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 just fine.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

On Wed, Nov 4, 2015 at 1:21 AM, M Tarkeshwar Rao <
m.tarkeshwar.rao@ericsson.com> wrote:

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*

We need to use FILLFACTOR, which is an opposite setting of PCTFREE.

*INITRANS 1*

*MAXTRANS 255*
*STORAGE (*
* INITIAL 5M*
* NEXT 5M*
* MINEXTENTS 1*
* MAXEXTENTS UNLIMITED*
* PCTINCREASE 0*
* BUFFER_POOL DEFAULT*
* )*
*LOGGING*

Default is LOGGED. We can specify UNLOGGED if we do not want to log trx
into WAL.

*NOCOMPRESS*

Postgres supports column level storage support.

SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

*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.

Regards,
Tarkeshwar

--

Regards,
Dinesh
manojadinesh.blogspot.com