Thread: Query regarding
Le 4 nov. 2015 10:23 AM, "M Tarkeshwar Rao" <m.tarkeshwar.rao@ericsson.com> a écrit :
>
> Hi all,
>
> We have started to convert some oracle sql scripts and converting them to postgres, but facing some issues to create table.
>
> I have some common doubts in create table script ie.
>
> ALTER TABLE employee
> DROP PRIMARY KEY CASCADE;
>
> DROP employee CASCADE CONSTRAINTS;
>
> CREATE TABLE employee
> (
> LABEL_IMP VARCHAR2(50 BYTE)
> )
> TABLESPACE DATA_TB
> PCTUSED 0
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 5M
> NEXT 5M
> MINEXTENTS 1
> MAXEXTENTS UNLIMITED
> PCTINCREASE 0
> BUFFER_POOL DEFAULT
> )
> LOGGING
> NOCOMPRESS
> NOCACHE
> NOPARALLEL
> MONITORING;
>
>
> I am trying to find replacement for above keywords highlighted in BOLD in postgres, but unable to find it on google.
> Could you please let us know if these parameter are managed internally in postgres or what are the alternative.
>
Those are really specific to oracle. The only exception is pctfree. It's called fillfactor in PostgreSQL and is the opposite of pctfree (as in if pctfree is 10%, fillfactor is 90%).
M Tarkeshwar Rao schrieb am 04.11.2015 um 10:21: > Hi all, > > We have started to convert some oracle sql scripts and converting them to postgres, but facing some issues to create table. > > I have some common doubts in create table script ie. > > ALTER TABLE employee > DROP PRIMARY KEY CASCADE; > > DROP employee CASCADE CONSTRAINTS; > > /CREATE TABLE /employee > /(/ > / LABEL//_IMP// VARCHAR2(50 BYTE)/ > /)/ > /TABLESPACE DATA//_TB/ > */PCTUSED 0/* > */PCTFREE 10/* > */INITRANS 1/* > */MAXTRANS 255/* > */STORAGE (/* > */ INITIAL 5M/* > */ NEXT 5M/* > */ MINEXTENTS 1/* > */ MAXEXTENTS UNLIMITED/* > */ PCTINCREASE 0/* > */ BUFFER_POOL DEFAULT/* > */ )/* > */LOGGING /* > */NOCOMPRESS /* > */NOCACHE/* > */NOPARALLEL/* > */MONITORING;/* > > > I am trying to find replacement for above keywords highlighted in BOLD in postgres, but unable to find it on google. > Could you please let us know if these parameter are managed internally in postgres or what are the alternative. There are only two options that have a match in Postgres: PCTFREE and TABLESPACE As all of them are using the default values in Oracle anyway, I wouldn't bother to translate them. Just remove everything. If you really want to tune PCTFREE, you need to use fillfactor in Postgres - which is the same thing "the other way round". So it would be 90% in Postgres You also need to change "VARCHAR2(50 BYTE)". In Postgres varchar lengths are always specified in characters, never in bytes. But as "Byte Semantics" is also the default in Oracle I assume replacing that with VARCHAR(50) in Postgres will work justfine. The first statement: ALTER TABLE employee DROP PRIMARY KEY CASCADE; was useless in Oracle to begin with - if you drop the table afterwards (with CASCADE), there is no need to drop the PK first. > DROP employee CASCADE CONSTRAINTS; Assuming that the missing "TABLE" keyword is a copy & paste error, this translates to "DROP TABLE employee CASCADE" in Postgres.
Hi all,We have started to convert some oracle sql scripts and converting them to postgres, but facing some issues to create table.I have some common doubts in create table script ie.ALTER TABLE employeeDROP PRIMARY KEY CASCADE;DROP employee CASCADE CONSTRAINTS;CREATE TABLE employee(LABEL_IMP VARCHAR2(50 BYTE))TABLESPACE DATA_TBPCTUSED 0PCTFREE 10
INITRANS 1MAXTRANS 255STORAGE (INITIAL 5MNEXT 5MMINEXTENTS 1MAXEXTENTS UNLIMITEDPCTINCREASE 0BUFFER_POOL DEFAULT)LOGGING
NOCOMPRESS
SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
NOCACHE
NOPARALLELMONITORING;I am trying to find replacement for above keywords highlighted in BOLD in postgres, but unable to find it on google.Could you please let us know if these parameter are managed internally in postgres or what are the alternative.Regards,Tarkeshwar