Thread: 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

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
informationtechnology      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

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

"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