Postgres Pro
Downloads
Home   >   mailing lists

Thread: "oracle to postgresql" conversion

"oracle to postgresql" conversion

From
altudela@gmail.com
Date:
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 :

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



Thanks!

Re: "oracle to postgresql" conversion

From
Kevin Hunter
Date:
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 :
>
> 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')))

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

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

Re: "oracle to postgresql" conversion

From
Jan de Visser
Date:
On Thursday 08 March 2007 11:40:21 am Kevin Hunter wrote:
> I do not know what the setting 'sqlbl' does in Oracle.

SET SQLBLANKLINES ON makes sqlplus not choke on empty lines in your input.

Don't get me started.

jan



--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

Re: "oracle to postgresql" conversion

From
Shane Ambler
Date:
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

Re: "oracle to postgresql" conversion

From
Scott Marlowe
Date:
On Tue, 2007-03-06 at 11:01, altudela@gmail.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')))

Re: "oracle to postgresql" conversion

From
Devrim GÜNDÜZ
Date:
Hi,

On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> NUMBER is Oracle's version of NUMERIC - Oracle will use both but
> probably only Oracle will use NUMBER.

Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

/me looks at Oracle docs again.
--
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Attachment

Re: "oracle to postgresql" conversion

From
Scott Marlowe
Date:
On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote:
> Hi,
>
> On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> > NUMBER is Oracle's version of NUMERIC - Oracle will use both but
> > probably only Oracle will use NUMBER.
>
> Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

Not sure.  It let me assign a precision to it, so I figured it wasn't
int based.  In fact, it accepts precision up to 38, just like numeric,
and it accepts non-decimal portions, i.e.:

number(20,4);

Re: "oracle to postgresql" conversion

From
"Chris Fischer"
Date:
All of Oracle's (non-float) number types are variable size numbers with an ordinal and a mantissa.  This makes Oracle
numbervery efficient for smaller values as compared to fixed size integers, but less efficient with larger values.
NUMBERhas a maximum precision of 38 digits with a scale of -84 to +127.  NUMBER consumes between 1 and 22 bytes on
disk. It is typical to specify a NUMBER with (p, s).  In the absence of definition, precision of 38 and scale
indeterminatewill be assumed. 

The exception to this are IEEE floating point number types which are a fixed size regardless of value.

Summary: Oracle has no fixed length equivlents to tinyint, smallint, int or bigint from other databases and can either
storethese values more or less efficiently than those databases with fixed length integer types. 

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Friday, March 09, 2007 1:58 PM
To: Devrim GÜNDÜZ
Cc: Shane Ambler; Kevin Hunter; altudela@gmail.com; PostgreSQL General List
Subject: Re: [GENERAL] "oracle to postgresql" conversion

On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote:
> Hi,
>
> On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> > NUMBER is Oracle's version of NUMERIC - Oracle will use both but
> > probably only Oracle will use NUMBER.
>
> Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

Not sure.  It let me assign a precision to it, so I figured it wasn't int based.  In fact, it accepts precision up to
38,just like numeric, and it accepts non-decimal portions, i.e.: 

number(20,4);

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

Re: "oracle to postgresql" conversion

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/09/07 14:53, Chris Fischer wrote:
> All of Oracle's (non-float) number types are variable size
> numbers with an ordinal and a mantissa.  This makes Oracle number
> very efficient for smaller values as compared to fixed size
> integers, but less efficient with larger values.  NUMBER has a
> maximum precision of 38 digits with a scale of -84 to +127.
> NUMBER consumes between 1 and 22 bytes on disk.  It is typical to
> specify a NUMBER with (p, s).  In the absence of definition,
> precision of 38 and scale indeterminate will be assumed.
>
> The exception to this are IEEE floating point number types which
> are a fixed size regardless of value.
>
> Summary: Oracle has no fixed length equivlents to tinyint,
> smallint, int or bigint from other databases and can either store
> these values more or less efficiently than those databases with
> fixed length integer types.

Wow!!!!  Didn't believe you (Oracle couldn't be *that* lame, could
it?), so I Googled.

According to Table 12-1 of this web page, Oracle will silently
truncate your numbers.  There are no scalar data types!!!!
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8dDjS9HxQb37XmcRArCMAKDAFuUM2V804Zjdurr6eemqPyHHOwCg1oGk
8RxOTImJVBUqdBhHK6tezkA=
=ibbT
-----END PGP SIGNATURE-----