Thread: Changing a table column datatype
Hello, This has been asked before, but the responses weren't clear enough for me to understand. I have a table `mytable' with an attribute `mycol' of datatype char(4) and I'd like to change it to varchar(20). Mycol is populated by two values - NULLs and four element chars. What I've tried thusfar: db=> ALTER TABLE mytable ADD COLUMN mycol_new VARCHAR(20); ALTER TABLE db=> INSERT INTO mytable (mycol_new) SELECT mycol FROM mytable; ERROR: ExecInsert: Fail to add null value in not null attribute fqdn The `fqdn' attribute is another column in mytable. So is it the case that INSERT doesn't like inserting NULL values? Is there another way to do this? I'm using PostgreSQL 7.3.2. TIA! -mt
Hi Marshall, based on the error msg your getting back from postgres, your column fqdn is set to 'not null'. therefore you must enter something into that field when trying to insert a new record. going off your example, you could do it like this: insert into mytable (fqdn, mycol_new) select fqdn, mycol from mytable; ----- Original Message ----- From: <marshall@perilith.com> Date: Mon, 12 May 2003 20:18:57 -0400 (EDT) To: <pgsql-novice@postgresql.org> Subject: [NOVICE] Changing a table column datatype > Hello, > > This has been asked before, but the responses weren't clear enough > for me to understand. > > I have a table `mytable' with an attribute `mycol' of datatype char(4) and > I'd like to change it to varchar(20). Mycol is populated by two values - > NULLs and four element chars. What I've tried thusfar: > > db=> ALTER TABLE mytable ADD COLUMN mycol_new VARCHAR(20); > ALTER TABLE > db=> INSERT INTO mytable (mycol_new) SELECT mycol FROM mytable; > ERROR: ExecInsert: Fail to add null value in not null attribute fqdn > > The `fqdn' attribute is another column in mytable. > > So is it the case that INSERT doesn't like inserting NULL values? Is > there another way to do this? > > I'm using PostgreSQL 7.3.2. > > TIA! > > -mt > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- ____________________________________________ http://www.operamail.com Get OperaMail Premium today - USD 29.99/year Powered by Outblaze
Hmmm... Maybe I haven't explained what I'm trying to do clearly. I don't wish to insert new fqdn values into this table, only copy the contents of one column (`mycol') into another column (`mycol_new'). Am I missing something here? On Tue, 13 May 2003, psql novice wrote: > Hi Marshall, > > based on the error msg your getting back from postgres, your column > fqdn is set to 'not null'. therefore you must enter something into > that field when trying to insert a new record. > > going off your example, you could do it like this: > > insert into mytable (fqdn, mycol_new) select fqdn, mycol from mytable; > > > ----- Original Message ----- > From: <marshall@perilith.com> > Date: Mon, 12 May 2003 20:18:57 -0400 (EDT) > To: <pgsql-novice@postgresql.org> > Subject: [NOVICE] Changing a table column datatype > > > Hello, > > > > This has been asked before, but the responses weren't clear enough > > for me to understand. > > > > I have a table `mytable' with an attribute `mycol' of datatype char(4) and > > I'd like to change it to varchar(20). Mycol is populated by two values - > > NULLs and four element chars. What I've tried thusfar: > > > > db=> ALTER TABLE mytable ADD COLUMN mycol_new VARCHAR(20); > > ALTER TABLE > > db=> INSERT INTO mytable (mycol_new) SELECT mycol FROM mytable; > > ERROR: ExecInsert: Fail to add null value in not null attribute fqdn > > > > The `fqdn' attribute is another column in mytable. > > > > So is it the case that INSERT doesn't like inserting NULL values? Is > > there another way to do this? > > > > I'm using PostgreSQL 7.3.2. > > > > TIA! > > > > -mt > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > -- > ____________________________________________ > http://www.operamail.com > Get OperaMail Premium today - USD 29.99/year > > > Powered by Outblaze > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Tue, 2003-05-13 at 01:18, marshall@perilith.com wrote: > Hello, > > This has been asked before, but the responses weren't clear enough > for me to understand. > > I have a table `mytable' with an attribute `mycol' of datatype char(4) and > I'd like to change it to varchar(20). Mycol is populated by two values - > NULLs and four element chars. What I've tried thusfar: > > db=> ALTER TABLE mytable ADD COLUMN mycol_new VARCHAR(20); > ALTER TABLE > db=> INSERT INTO mytable (mycol_new) SELECT mycol FROM mytable; > ERROR: ExecInsert: Fail to add null value in not null attribute fqdn > > The `fqdn' attribute is another column in mytable. > > So is it the case that INSERT doesn't like inserting NULL values? Is > there another way to do this? INSERT inserts new rows and all constraints for the row must be satisfied; use UPDATE to alter existing rows. UPDATE mytable SET mycol_new = mycol; (no WHERE clause, because you want to change every row). -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Watch ye and pray, lest ye enter into temptation. The spirit truly is ready, but the flesh is weak." Mark 14:38
<marshall@perilith.com> writes:
> Hmmm... Maybe I haven't explained what I'm trying to do clearly.
> I don't wish to insert new fqdn values into this table, only copy
> the contents of one column (`mycol') into another column (`mycol_new').
> Am I missing something here?
Yes. You should be using UPDATE not INSERT, viz
UPDATE mytable SET mycol_new = mycol;
INSERT is for adding new rows.
regards, tom lane
Hi Marshall, i assumed you wanted a new record as you used the "INSERT" command and not the "UPDATE" command. try this: update mytable set mycol_new = mycol; heres the output from my terminal j=> select * from mytable; fqdn | mycol | mycol_new ------+-------+----------- 1 | 1 | 2 | 2 | (2 rows) j=> update mytable set mycol_new = mycol; UPDATE 2 j=> select * from mytable; fqdn | mycol | mycol_new ------+-------+----------- 1 | 1 | 1 2 | 2 | 2 (2 rows) ----- Original Message ----- From: <marshall@perilith.com> Date: Mon, 12 May 2003 21:48:24 -0400 (EDT) To: psql novice <psql_novice@operamail.com> Subject: Re: [NOVICE] Changing a table column datatype > Hmmm... Maybe I haven't explained what I'm trying to do clearly. > > I don't wish to insert new fqdn values into this table, only copy > the contents of one column (`mycol') into another column (`mycol_new'). > Am I missing something here? > > On Tue, 13 May 2003, psql novice wrote: > > > Hi Marshall, > > > > based on the error msg your getting back from postgres, your column > > fqdn is set to 'not null'. therefore you must enter something into > > that field when trying to insert a new record. > > > > going off your example, you could do it like this: > > > > insert into mytable (fqdn, mycol_new) select fqdn, mycol from mytable; > > > > > > ----- Original Message ----- > > From: <marshall@perilith.com> > > Date: Mon, 12 May 2003 20:18:57 -0400 (EDT) > > To: <pgsql-novice@postgresql.org> > > Subject: [NOVICE] Changing a table column datatype > > > > > Hello, > > > > > > This has been asked before, but the responses weren't clear enough > > > for me to understand. > > > > > > I have a table `mytable' with an attribute `mycol' of datatype char(4) and > > > I'd like to change it to varchar(20). Mycol is populated by two values - > > > NULLs and four element chars. What I've tried thusfar: > > > > > > db=> ALTER TABLE mytable ADD COLUMN mycol_new VARCHAR(20); > > > ALTER TABLE > > > db=> INSERT INTO mytable (mycol_new) SELECT mycol FROM mytable; > > > ERROR: ExecInsert: Fail to add null value in not null attribute fqdn > > > > > > The `fqdn' attribute is another column in mytable. > > > > > > So is it the case that INSERT doesn't like inserting NULL values? Is > > > there another way to do this? > > > > > > I'm using PostgreSQL 7.3.2. > > > > > > TIA! > > > > > > -mt > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > -- > > ____________________________________________ > > http://www.operamail.com > > Get OperaMail Premium today - USD 29.99/year > > > > > > Powered by Outblaze > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > -- ____________________________________________ http://www.operamail.com Get OperaMail Premium today - USD 29.99/year Powered by Outblaze