Re: Changing a table column datatype - Mailing list pgsql-novice

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