Обсуждение: 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.