pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
An easy question about creating a primary key
13+ messages / 6 participants
[nested] [flat]

* An easy question about creating a primary key
@ 2001-11-29 23:22  Ligia Pimentel <lmpimentel@yahoo.com>
  0 siblings, 3 replies; 13+ messages in thread

From: Ligia Pimentel @ 2001-11-29 23:22 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: An easy question about creating a primary key
@ 2001-12-03 19:16  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Ligia Pimentel <lmpimentel@yahoo.com>
  2 siblings, 0 replies; 13+ messages in thread

From: Stephan Szabo @ 2001-12-03 19:16 UTC (permalink / raw)
  To: Ligia Pimentel <lmpimentel@yahoo.com>; +Cc: pgsql-sql

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"




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: An easy question about creating a primary key
@ 2001-12-03 19:20  Josh Berkus <josh@agliodbs.com>
  parent: Ligia Pimentel <lmpimentel@yahoo.com>
  2 siblings, 0 replies; 13+ messages in thread

From: Josh Berkus @ 2001-12-03 19:20 UTC (permalink / raw)
  To: Ligia Pimentel <lmpimentel@yahoo.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: An easy question about creating a primary key
@ 2001-12-04 01:36  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Ligia Pimentel <lmpimentel@yahoo.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Christopher Kings-Lynne @ 2001-12-04 01:36 UTC (permalink / raw)
  To: Ligia Pimentel <lmpimentel@yahoo.com>; pgsql-sql

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
>




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: An easy question about creating a primary key
@ 2001-12-04 02:12  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2001-12-04 02:12 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Ligia Pimentel <lmpimentel@yahoo.com>; pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2001-12-04 02:23  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 13+ messages in thread

From: Christopher Kings-Lynne @ 2001-12-04 02:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ligia Pimentel <lmpimentel@yahoo.com>; Hackers <pgsql-hackers@postgresql.org>

> 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




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2001-12-04 02:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2001-12-04 02:29 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Ligia Pimentel <lmpimentel@yahoo.com>; Hackers <pgsql-hackers@postgresql.org>

"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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2001-12-04 02:40  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2001-12-04 02:40 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Ligia Pimentel <lmpimentel@yahoo.com>; Hackers <pgsql-hackers@postgresql.org>

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2001-12-04 02:50  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 13+ messages in thread

From: Christopher Kings-Lynne @ 2001-12-04 02:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ligia Pimentel <lmpimentel@yahoo.com>; Hackers <pgsql-hackers@postgresql.org>

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
>




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2001-12-04 02:54  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2001-12-04 02:54 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Ligia Pimentel <lmpimentel@yahoo.com>; Hackers <pgsql-hackers@postgresql.org>

"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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2001-12-28 05:11  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 13+ messages in thread

From: Bruce Momjian @ 2001-12-28 05:11 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Ligia Pimentel <lmpimentel@yahoo.com>; Hackers <pgsql-hackers@postgresql.org>


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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2001-12-28 05:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 13+ messages in thread

From: Tom Lane @ 2001-12-28 05:20 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Ligia Pimentel <lmpimentel@yahoo.com>; Hackers <pgsql-hackers@postgresql.org>

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

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

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] An easy question about creating a primary key
@ 2002-01-02 03:36  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 13+ messages in thread

From: Christopher Kings-Lynne @ 2002-01-02 03:36 UTC (permalink / raw)
  To: Hackers <pgsql-hackers@postgresql.org>

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
>




^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2002-01-02 03:36 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-29 23:22 An easy question about creating a primary key Ligia Pimentel <lmpimentel@yahoo.com>
2001-12-03 19:16 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-03 19:20 ` Josh Berkus <josh@agliodbs.com>
2001-12-04 01:36 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-04 02:12   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-04 02:23     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-04 02:29       ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-04 02:40         ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-04 02:50           ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-04 02:54             ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-28 05:11               ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-12-28 05:20                 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-02 03:36                 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox