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.