[BUGS] adding a primary key column to a temporary table fails (v7.4.3)
Hi, I guess this might not be the appropriate thing to be happening: # select version(); version - PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) (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. 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? Ratio: I was trying to avoid repeating complex create temp table statements by doing stuff like 'create temp table as select * from 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. -- Best, Frank. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] adding a primary key column to a temporary table fails
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 as select * from 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
Re: [BUGS] adding a primary key column to a temporary table fails
> 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. > > 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* ;-\ > 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
Re: [BUGS] ecpg -D SYMBOL
Defining things twice - once in 'SQL' space and once in 'C' space is something I struggled with when using Informix ESQL/C. I like the idea of having it apply to both. Although if you are to support INFORMIX or INFORMIX_SE mode in ecpg you will need limit it to 'SQL' space (for these modes anyway). Michael Meskes wrote: On Tue, Jul 06, 2004 at 03:49:14PM +1000, Luke McFarlane wrote: When defining a symbol on the command line with ecpg -D SYMBOL the ecpg preprocessor will replace that symbol with empty space in 'C' program space rather than limiting it to 'SQL' program space. This indeed is a bug, but ecpg has never been designed to limit symbols to SQL space. You're absolutely right that it should not use empty space. I just fixed that in CVS. Now it correctly uses "1". It shouldn't touch anything outside EXEC SQL. Why? That means you have to define most things twice. Also, if you try to fool ecpg by compiling with ecpg -D SYMBOL=SYMBOL it will sit in an infinite loop gobbling as much virtual memory as it sees fit. Ah yes, another bug. I just committed a fix for this as well. All fixes went into HEAD and 7.4. Michael ---(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
