Thread: Is it possible (postgresql/mysql)

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

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






--
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>
>     "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.