Re: "oracle to postgresql" conversion

On Tue, 2007-03-06 at 11:01, altudela(at)gmail(dot)com wrote:
> I'm a newbie in Oracle and postgreSQL,
> i'm need to translate the following script (in Oracle) to postgreSQL :
>
> rem Autorisation des lignes vides :
> set sqlbl on

You don't need this, postgresql doesn't choke on extra lines.

> rem Initialisation du timer :
> set timing on

-- Change this to \timing if you're gonna use psql to run the script:
\timing

> rem Creation de la table :

becomes

-- Creation de la table :

Now, we need to use real SQL 99 types here, or specific postgresql
types. And don't quote unless you need to. PostgreSQL folds to lower
case, not upper case, so if you quote upper case here, you'll always
have to quote in the future. Better to just not quote, in my humble
opinion.

So, "LEPAPE" will become lepape
VARCHAR2(16) will become varchar(16)
NUMBER will become either
decimal or numeric
NOT NULL is still NOT NULL
and the check constraint will look the same too. again unless you
require upper case, leave the SYS_C009967 lower case, and better yet,
give it a useful name, like lepape_measure_check

CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,

CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))