Re: "oracle to postgresql" conversion - Mailing list pgsql-general

Kevin Hunter wrote:
> On 06 Mar 2007 at 9:01a -0800, g.c[ altudela ] 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
>>
>> rem Initialisation du timer :
>> set timing on
>>
>> rem Creation de la table :

I would say you can remove these. rem is just a comment Which can be
changed to -- . The two set lines are session settings that you won't miss.

You may want to consider starting with your CREATE DATABASE command and
make sure you have a suitable ENCODING setting.

Start by adding -
CREATE SCHEMA "LEPAPE";

You may want to add "LEPAPE" to your search path so you don't need to
have it before everything.
http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH
can explain that further.

>> 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')))

Change VARCHAR2 to VARCHAR
Change NUMBER to NUMERIC
Change CHECK(measure to CHECK("MEASURE"

VARCHAR2 is an Oracle type that replaces VARCHAR. Not sure why but my
first guess would be it works with multibyte character sets which is why
I suggest checking your ENCODING before you start.

NUMBER is Oracle's version of NUMERIC - Oracle will use both but
probably only Oracle will use NUMBER.

The CHECK change is to do with identifiers which I point you to later.

You may want to bookmark http://www.ss64.com/orasyntax/datatypes.html
and http://www.postgresql.org/docs/8.2/interactive/datatype.html
as references for future scripts that you may stumble on.

> I believe rem translates to '-- ' (the extra space is important)
>
> set translates to '\set'
>
> I do not know what the setting 'sqlbl' does in Oracle.
>
> I'm not entirely sure about the owner bit, specified by "LEPAPE"."..."
>
>
>
> The various data types translate to (likely) more standards compliant
> names, which you can peruse at
>
> http://www.postgresql.org/docs/8.2/static/datatype.html
>
> (Replace 8.2 with your major version of PostgreSQL.)
>
>
>
> Someone may correct me, but I believe that Postgres is not case
> sensitive in (terms of column and constraint names) unless you create
> them with quotes.  Thus, you could just as easily write
>
> "EXP_ID" VARCHAR2(16) NOT NULL,
>
> as
>
> exp_id VARCHAR(16) NOT NULL

I would say remove all the double quotes, but this will depend on the
other scripts that have selects and such - it may be easier to leave
them as is than to update all the scripts you have. If you are typing
from a book then that won't matter.

I am guessing that this is an Oracle tutorial that you have and you want
to use postgresql to work through it. This would be a good time to get
familiar with naming conventions, start by reading -
http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
This should help you to understand why part of the above table
definition should be changed to CHECK("MEASURE" and you may have some
similar fixes throughout your scripts.

> which would be my personal preference as I like to capitalize SQL
> keywords and leave everything as lower case.  (Makes for easier reading
> later.)
>
> Kevin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>


--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz