Horton

Thread

  1. An easy question about creating a primary key

    Ligia Pimentel <lmpimentel@yahoo.com> — 2001-11-29T23:22:22Z

    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

  2. Re: An easy question about creating a primary key

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-12-03T19:16:14Z

    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"

  3. Re: An easy question about creating a primary key

    Josh Berkus <josh@agliodbs.com> — 2001-12-03T19:20:07Z

    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

  4. Re: An easy question about creating a primary key

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-12-04T01:36:18Z

    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
>

  5. Re: An easy question about creating a primary key

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-04T02:12:21Z

    "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

  6. Re: [SQL] An easy question about creating a primary key

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-12-04T02:23:19Z

    > 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

  7. Re: [SQL] An easy question about creating a primary key

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-04T02:29:49Z

    "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

  8. Re: [SQL] An easy question about creating a primary key

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-04T02:40:20Z

    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

  9. Re: [SQL] An easy question about creating a primary key

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-12-04T02:50:06Z

    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
>

  10. Re: [SQL] An easy question about creating a primary key

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-04T02:54:06Z

    "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

  11. Re: creating a primary key

    Keith Gray <keith@heart.com.au> — 2001-12-04T05:16:36Z

    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

  12. Re: [SQL] An easy question about creating a primary key

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-12-28T05:11:00Z

    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

  13. Re: [SQL] An easy question about creating a primary key

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-28T05:20:44Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this resolved?

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

			regards, tom lane

  14. Re: [SQL] An easy question about creating a primary key

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-01-02T03:36:53Z

    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
>