Re: [BUGS] adding a primary key column to a temporary table fails

#1

On Thu, 22 Jul 2004, Frank van Vugt wrote:

Quote:

(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).

Quote:

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.

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

Quote:

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

I grok.

Quote:

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.

Now how did I miss *that* ;-\

Quote:

I think you probably want:
alter table f_test add primary key (id);

Yep, that does the trick.

Thank!

--
Best,

Frank.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] adding a primary key column to a temporary table fails

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] adding a primary key column to a temporary table fails - 07-22-2004 , 09:08 AM

Re: [BUGS] adding a primary key column to a temporary table fails - 07-22-2004 , 09:27 AM