Skip site navigation (1) Skip section navigation (2)

Re: adding a primary key column to a temporary table fails

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: adding a primary key column to a temporary table fails
Date: 2004-07-22 14:01:03
Message-ID: 20040722065615.V98843@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Thu, 22 Jul 2004, Frank van Vugt wrote:

> (verify whether temporary tables do support primary keys)
> # create temp table f_test (id int primary key, value varchar(20));
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "f_test_pkey"
> for table "f_test"
> CREATE TABLE
>
> # drop table f_test;
> DROP TABLE
>
> (now create it without the primary key)
> # create temp table f_test (value varchar(20));
> CREATE TABLE
>
> (and add the column afterwards)
> # alter table f_test add column id int primary key;
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "f_test_pkey" for table "f_test"
> ERROR:  adding NOT NULL columns is not implemented
> HINT:  Add the column, then use ALTER TABLE SET NOT NULL.

The issue here is the not-nullness of a new column which isn't supported
in 7.4 (it appears to be in 7.5).

> By the way, I noticed that the todo-list does not mention 'alter table alter
> column set/drop primary key'. I'm wondering whether it should?

ALTER TABLE ADD CONSTRAINT can handle primary keys.

> I was trying to avoid repeating complex create temp table statements by doing
> stuff like 'create temp table <foo> as select * from <bar> where false'
> instead. But since the resulting table doesn't have any indexes on it and one
> of the libraries I use actually expects a 'real' primary key to be available,
> I was looking for ways to get to a temporary table with a copied structure
> AND a primary key.

I think you probably want:

create temp table f_test as select * from bar where false;
alter table f_test add primary key (id);

In response to

Responses

pgsql-bugs by date

Next:From: Frank van VugtDate: 2004-07-22 14:18:31
Subject: Re: adding a primary key column to a temporary table fails
Previous:From: Frank van VugtDate: 2004-07-22 13:50:46
Subject: adding a primary key column to a temporary table fails (v7.4.3)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group