Hello.

I'm using postgres 7.1. I have an easy question...

I want to create a primary key constraint on an existing table. The
documentation says I can't . Please confirm. If this is true... How can I
rename the existing table so I can create the new one and copy the data?

Thank you

Ligia



Thu, 29 Nov 2001 17:22:22 -0600
"Ligia Pimentel" <lmpimentel@yahoo.com>
An easy question about creating a primary key
On Thu, 29 Nov 2001, Ligia Pimentel wrote:

> Hello.
>
> I'm using postgres 7.1. I have an easy question...
>
> I want to create a primary key constraint on an existing table. The
> documentation says I can't . Please confirm. If this is true... How can I
> rename the existing table so I can create the new one and copy the data?

I believe that's correct for 7.1 at least.  You can rename tables using
ALTER TABLE (alter table <table> rename to <newtable>).  If the column(s)
are marked not null already, you may be able to just get away with
creating a unique index on the column(s) named "<table>_pkey"


Mon, 3 Dec 2001 11:16:14 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: An easy question about creating a primary key
Ligia,

> I want to create a primary key constraint on an existing table. The
> documentation says I can't . Please confirm. If this is true... How
> can I
> rename the existing table so I can create the new one and copy the
> data?

FYI, this question is more appropriate for the NOVICE list.

You would use the same method that you use to drop and recreate the
table for other reasons:

CREATE TABLE tablea_temp AS
SELECT * FROM tablea;

DROP tablea;

CREATE tablea (
   primary_key SERIAL ...
   <snip>
);

INSERT INTO tablea (column list)
SELECT (column list) FROM tablea_temp;

And don't forget to re-build your indexes!

-Josh Berkus




______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Mon, 03 Dec 2001 11:20:07 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: An easy question about creating a primary key
Hi Ligia,

I have submitted code for 7.2b3 that allows ADD UNIQUE after table creation,
but you'll have to wait until 7.3 for ADD PRIMARY KEY after table createion.
What you can do however is something like this:

1. Make sure the column you want to make a primary key is NOT NULL and there
are no other PRIMARY KEYs on the table.

2.
BEGIN;
CREATE UNIQUE INDEX blah ON table(field);
UPDATE pg_index SET indisprimary=true WHERE indexrelid=(SELECT oid FROM
pg_class WHERE relname='blah'))
COMMIT;

Not that as far as postgres is concerned a UNIQUE, NOT NULL index is exactly
the same as a PRIMARY KEY index.  All that the above catalog tweak does is
actually mark the index as being primary in pg_dump, etc.

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Ligia Pimentel
> Sent: Friday, 30 November 2001 7:22 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] An easy question about creating a primary key
>
>
> Hello.
>
> I'm using postgres 7.1. I have an easy question...
>
> I want to create a primary key constraint on an existing table. The
> documentation says I can't . Please confirm. If this is true... How can I
> rename the existing table so I can create the new one and copy the data?
>
> Thank you
>
> Ligia
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Tue, 4 Dec 2001 09:36:18 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: An easy question about creating a primary key
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I have submitted code for 7.2b3 that allows ADD UNIQUE after table creation,
> but you'll have to wait until 7.3 for ADD PRIMARY KEY after table createion.

I think you've forgotten your own work, Chris.

regression=# create table foo (bar int not null);
CREATE
regression=# alter table foo add primary key (bar);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=#

Having to have marked the columns as "not null" from the beginning is a
painful limitation, but it's not like the feature doesn't exist at all.

			regards, tom lane

Mon, 03 Dec 2001 21:12:21 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: An easy question about creating a primary key 
> I think you've forgotten your own work, Chris.
>
> regression=# create table foo (bar int not null);
> CREATE
> regression=# alter table foo add primary key (bar);
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> 'foo_pkey' for table 'foo'
> CREATE
> regression=#

Bizarre.  That patch was never committed.  If you check
src/backend/commands/command.c and search for 'CONSTR_' you'll notice that
the CONSTR_UNIQUE function I implemented is there, but CONSTR_PRIMARY is
definitely not being handled.  (I'm looking at the 7.2b2 source code)

Chris


Tue, 4 Dec 2001 10:23:19 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] An easy question about creating a primary key 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> I think you've forgotten your own work, Chris.
>> 
>> regression=# create table foo (bar int not null);
>> CREATE
>> regression=# alter table foo add primary key (bar);
>> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
>> 'foo_pkey' for table 'foo'
>> CREATE
>> regression=#

> Bizarre.  That patch was never committed.  If you check
> src/backend/commands/command.c and search for 'CONSTR_' you'll notice that
> the CONSTR_UNIQUE function I implemented is there, but CONSTR_PRIMARY is
> definitely not being handled.  (I'm looking at the 7.2b2 source code)

Hmm ... actually, I wonder whether that code in command.c isn't entirely
dead code.  I believe that as things stand, parser/analyze.c converts
UNIQUE and PRIMARY constraints into CREATE INDEX statements; the
constraint nodes themselves never make it past the parser.  It looks to
me like command.c only needs to handle CHECK constraints and foreign-key
constraints, cf transformAlterTableStmt().

			regards, tom lane

Mon, 03 Dec 2001 21:29:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] An easy question about creating a primary key 
Now that I look at it, I think I made the relevant changes in the
parser:

2001-10-11 20:07  tgl

	* doc/src/sgml/ref/alter_table.sgml, src/backend/catalog/pg_type.c,
	src/backend/commands/command.c, src/backend/parser/analyze.c,
	src/backend/tcop/utility.c, src/include/commands/command.h,
	src/include/nodes/parsenodes.h,
	src/test/regress/expected/alter_table.out,
	src/test/regress/expected/foreign_key.out: Break
	transformCreateStmt() into multiple routines and make
	transformAlterStmt() use these routines, instead of having lots of
	duplicate (not to mention should-have-been-duplicate) code.  Adding
	a column with a CHECK constraint actually works now, and the tests
	to reject unsupported DEFAULT and NOT NULL clauses actually fire
	now.  ALTER TABLE ADD PRIMARY KEY works, modulo having to have
	created the column(s) NOT NULL already.

I was mainly interested in eliminating the inconsistencies in parse-time
handling of CREATE TABLE and ALTER TABLE, and the ensuing bugs mentioned
in the commit log.  I didn't think much about the possibility that I was
obsoleting stuff in command.c, but maybe I did.

			regards, tom lane

Mon, 03 Dec 2001 21:40:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] An easy question about creating a primary key 
You know, that would explain a lot.  Since this was only done in October, I
wouldn't have noticed it.  And it explains why I couldn't get various
changes in my code to actually have any effect...

Oh well, it was a fun coding exercise ;)  Feel free to remove the ADD UNIQUE
stuff from command.c to see if the parser will handle it.  However, your
commit message also just implies that all you fixed was ADD PRIMARY KEY???

Chris

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, 4 December 2001 10:40 AM
> To: Christopher Kings-Lynne
> Cc: Ligia Pimentel; Hackers
> Subject: Re: [SQL] An easy question about creating a primary key
>
>
> Now that I look at it, I think I made the relevant changes in the
> parser:
>
> 2001-10-11 20:07  tgl
>
> 	* doc/src/sgml/ref/alter_table.sgml, src/backend/catalog/pg_type.c,
> 	src/backend/commands/command.c, src/backend/parser/analyze.c,
> 	src/backend/tcop/utility.c, src/include/commands/command.h,
> 	src/include/nodes/parsenodes.h,
> 	src/test/regress/expected/alter_table.out,
> 	src/test/regress/expected/foreign_key.out: Break
> 	transformCreateStmt() into multiple routines and make
> 	transformAlterStmt() use these routines, instead of having lots of
> 	duplicate (not to mention should-have-been-duplicate) code.  Adding
> 	a column with a CHECK constraint actually works now, and the tests
> 	to reject unsupported DEFAULT and NOT NULL clauses actually fire
> 	now.  ALTER TABLE ADD PRIMARY KEY works, modulo having to have
> 	created the column(s) NOT NULL already.
>
> I was mainly interested in eliminating the inconsistencies in parse-time
> handling of CREATE TABLE and ALTER TABLE, and the ensuing bugs mentioned
> in the commit log.  I didn't think much about the possibility that I was
> obsoleting stuff in command.c, but maybe I did.
>
> 			regards, tom lane
>


Tue, 4 Dec 2001 10:50:06 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] An easy question about creating a primary key 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Oh well, it was a fun coding exercise ;)  Feel free to remove the ADD UNIQUE
> stuff from command.c to see if the parser will handle it.  However, your
> commit message also just implies that all you fixed was ADD PRIMARY KEY???

It says that because I thought that was all I was changing; I hadn't
realized the side-effects on ADD UNIQUE.

			regards, tom lane

Mon, 03 Dec 2001 21:54:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] An easy question about creating a primary key 
Josh Berkus wrote:
> 
> 
> And don't forget to re-build your indexes!
> 

Is this just VACUUM ANALYZE
or is there another index
rebuild command?

-- 
Keith Gray

Technical Development Manager
Heart Consulting Services P/L
mailto:keith@heart.com.au

Tue, 04 Dec 2001 16:16:36 +1100
Keith Gray <keith@heart.com.au>
Re: creating a primary key

Is this resolved?

---------------------------------------------------------------------------

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > Oh well, it was a fun coding exercise ;)  Feel free to remove the ADD UNIQUE
> > stuff from command.c to see if the parser will handle it.  However, your
> > commit message also just implies that all you fixed was ADD PRIMARY KEY???
> 
> It says that because I thought that was all I was changing; I hadn't
> realized the side-effects on ADD UNIQUE.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 28 Dec 2001 00:11:00 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] An easy question about creating a primary key
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this resolved?

Yup, the no-longer-needed code is gone.

			regards, tom lane

Fri, 28 Dec 2001 00:20:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] An easy question about creating a primary key 
Note that although I added a regression test for ADD UNIQUE (and had some
confusion as it was Tom's error messages generated, not mine!), I didn't add
an ADD PRIMARY KEY one.  It should be done in 7.3 I guess.

Chris

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Friday, 28 December 2001 1:11 PM
> To: Tom Lane
> Cc: Christopher Kings-Lynne; Ligia Pimentel; Hackers
> Subject: Re: [HACKERS] [SQL] An easy question about creating a primary
> key
>
>
>
> Is this resolved?
>
> ------------------------------------------------------------------
> ---------
>
> > "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > > Oh well, it was a fun coding exercise ;)  Feel free to remove
> the ADD UNIQUE
> > > stuff from command.c to see if the parser will handle it.
> However, your
> > > commit message also just implies that all you fixed was ADD
> PRIMARY KEY???
> >
> > It says that because I thought that was all I was changing; I hadn't
> > realized the side-effects on ADD UNIQUE.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>


Wed, 2 Jan 2002 11:36:53 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] An easy question about creating a primary key