Обсуждение: "oracle to postgresql" conversion
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!
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
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! --------------------------------------------------------------
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
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')))
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/
Вложения
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);
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
-----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-----