Thread: BUG #5719: Constraint Problem

The following bug has been logged online:

Bug reference:      5719
Logged by:          KOTAPATI.KRISHNAIAH
Email address:      kotapati.krishna@hotmail.com
PostgreSQL version: PostgreSQL8.3.1
Operating system:   Microsoft Windows [Version 6.1.7600]
Description:        Constraint Problem
Details:

create table kittu1 as select * from kota1;  when i use this command ,i got
the following details:


CREATE TABLE kota1
(
  did integer NOT NULL,
  "name" character varying(40),
  deptno integer NOT NULL DEFAULT 12,
  CONSTRAINT kota1_pkey PRIMARY KEY (did)
)
WITH (OIDS=FALSE);
ALTER TABLE kota1 OWNER TO postgres;


CREATE TABLE kittu1
(
  did integer,
  "name" character varying(40),
  deptno integer
)
WITH (OIDS=FALSE);
ALTER TABLE kittu OWNER TO postgres;

On Thu, Oct 21, 2010 at 03:05, KOTAPATI.KRISHNAIAH
<kotapati.krishna@hotmail.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05719
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0KOTAPATI.KRISHNAIAH
> Email address: =C2=A0 =C2=A0 =C2=A0kotapati.krishna@hotmail.com
> PostgreSQL version: PostgreSQL8.3.1
> Operating system: =C2=A0 Microsoft Windows [Version 6.1.7600]
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Constraint Problem
> Details:
>
> create table kittu1 as select * from kota1; =C2=A0when i use this command=
 ,i got
> the following details:

I may have misunderstood the problem, I dont see a bug here.  Im
assuming you want kittu1 to have the constraints that kota1 has.
create table ... as select ... won't work simply because the the
select does not have the information needed.

Try:
create table kittu1 (like kota1 including constraints including
defaults including indexes);
insert into kittu1 select * from kota1;

Or if you just want the not null constraints:
create table kittu1 (like kota1 including constraints);

See http://www.postgresql.org/docs/8.3/static/sql-createtable.html for more.
[ note it looks like with postgresql 9.0 and up you can just say like
kota1 including all ]

On Thu, Oct 21, 2010 at 12:03, Alex Hunsaker <badalex@gmail.com> wrote:
> Or if you just want the not null constraints:
> create table kittu1 (like kota1 including constraints);

Correction, the above should read: "Or, if you want check
constraints".  Not null constraints are always copied, and including
constraints only deals with check constraints.

On Thu, Oct 21, 2010 at 22:01, KOTAPATI KRISHNAIAH
<kotapati.krishna@hotmail.com> wrote:

[ Example shrunk ]
> CREATE TABLE cons
> (
>   id integer NOT NULL primary key,
> );
>
> The above cons table already existed.
> when i'm creating  the above same structure of table by using below this
> Query i'm not getting constraints ...
>
> create table vijju1(like cons including constraints);
>
> CREATE TABLE vijju1
> (
>   id integer NOT NULL,
> );

Or in other words create table like is not copying the primary key
constraint.  The simple answer is to use "including indexes".

There is quite a bit of discussion on this, some interesting points:
http://archives.postgresql.org/pgsql-hackers/2009-12/msg02018.php

and a long thread with a patch...:
http://postgresql.1045698.n5.nabble.com/CREATE-TABLE-LIKE-INCLUDING-INDEXES-support-td2220980.html

The sad news is I dont think anything appened for 8.4 or 9.0.