Is it possible (postgresql/mysql)

Louis,

indizes are simply created outside the create table

CREATE INDEX someTable_Date_create
ON someTable
USING btree
(date_create);

As you are working on transferring, maybe you like to drop those varchar(xx)
and replace them with text. Saves a lot of hassle lateron.

Harald

On 8/16/06, louis gonzales <gonzales@linuxlouis.net> wrote:

Hello List,
PostgreSQL 8.0.1 (on Solaris 9)
There is a PERL program that a friend purchased which is used to create
tables on a MySQL database, and of course ;) I want to run this on a
PostgreSQL database server instead. The below is the code:
$sth=runSQL("CREATE TABLE someTable (
date_create bigint NOT NULL,
date_end bigint NOT NULL,
username VARCHAR(20) NOT NULL,
$cat_definition
id serial PRIMARY KEY,
status VARCHAR(20) NOT NULL,
$adfields
visibility TEXT NOT NULL,
priority TEXT NOT NULL,
template TEXT NOT NULL,
view bigint DEFAULT 0 NOT NULL,
reply bigint DEFAULT 0 NOT NULL,
save bigint DEFAULT 0 NOT NULL,
updated bigint,
photo VARCHAR(1) NOT NULL DEFAULT '0',
INDEX(username),
$cat_index
INDEX(date_create) );");

What my question is, the "INDEX(...)" function calls, which work this
way on MySQL, don't work in PostgreSQL. Does anybody know what a
synonymous way to modify the above code, for compatibility with
PostgreSQL?

FYI: yourVariable INT UNSIGNED AUTO_INCREMENT(MySQL)
can be replaced by
yourVariable serial

Thanks group!

--
Email: louis.gonzales@linuxlouis.net
WebSite: http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.

Harald,
I had thought of that, but I wasn't sure if there was/is a way to create
the index's upon table creation, as it appears is possible with MySQL.

As for the replacing of varchar(xx) with a text data type, why do you
recommend this? I want to stay as close as I can to the original
code...but if you think there is a good reason and that it won't
conflict with something else, then I'd like to know. Granted,
varchar(xx) is nothing but a string of characters, potentially xx in
length, and a text datatype is also just a string of characters, I would
wonder if possibly there would be a string comparison that would treat
these different on the single fact of different datatype?

Thanks for your help Harald!

Harald Armin Massa wrote:

Louis,

indizes are simply created outside the create table

CREATE INDEX someTable_Date_create
ON someTable
USING btree
(date_create);

As you are working on transferring, maybe you like to drop those
varchar(xx) and replace them with text. Saves a lot of hassle lateron.

Harald

On 8/16/06, *louis gonzales* <gonzales@linuxlouis.net
<mailto:gonzales@linuxlouis.net>> wrote:

Hello List,
PostgreSQL 8.0.1 (on Solaris 9)
There is a PERL program that a friend purchased which is used to
create
tables on a MySQL database, and of course ;) I want to run this on a
PostgreSQL database server instead. The below is the code:
$sth=runSQL("CREATE TABLE someTable (
date_create bigint NOT NULL,
date_end bigint NOT NULL,
username VARCHAR(20) NOT NULL,
$cat_definition
id serial PRIMARY KEY,
status VARCHAR(20) NOT NULL,
$adfields
visibility TEXT NOT NULL,
priority TEXT NOT NULL,
template TEXT NOT NULL,
view bigint DEFAULT 0 NOT NULL,
reply bigint DEFAULT 0 NOT NULL,
save bigint DEFAULT 0 NOT NULL,
updated bigint,
photo VARCHAR(1) NOT NULL DEFAULT '0',
INDEX(username),
$cat_index
INDEX(date_create) );");

What my question is, the "INDEX(...)" function calls, which work this
way on MySQL, don't work in PostgreSQL. Does anybody know what a
synonymous way to modify the above code, for compatibility with
PostgreSQL?

FYI: yourVariable INT UNSIGNED AUTO_INCREMENT(MySQL)
can be replaced by
yourVariable serial

Thanks group!

--
Email: louis.gonzales@linuxlouis.net
<mailto:louis.gonzales@linuxlouis.net>
WebSite: http://www.linuxlouis.net <http://www.linuxlouis.net&gt;
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstra�e 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.

--
Email: louis.gonzales@linuxlouis.net
WebSite: http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka

Louis,

there may be an a unimportant performance penality for varchar, because of
filling up and whatever.

But the real reason: as much as I researched, NOBODY stated that varchar2
would be faster or better; but it limits the length. And I spend to much
time in working around fixed field lengths in my life; and I saw enough
people being frustrated and wasting time, especially for fields like "name".
i.E.: in germany the ZIP-code was extended from 4 to 5 digits. Can you
imagine HOW MANY applications had to be fixed?

On the other hand I use the "TEXT" type to argue when somebody wants me to
port my application to Oracle :) ... "yes, of course. You need Oracle
Enterprise Edition because of Table Partitioning; and we need to refactor
all text fields so they can deal with Oracles limited VARCHAR2 type, or you
simply accept that we will only use the first xxx chars."

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.