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"

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

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

"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

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

"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

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

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

"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

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

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is this resolved?

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

regards, tom lane

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