Re: Changing a table column datatype

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(at)perilith(dot)com>
Date: Mon, 12 May 2003 21:48:24 -0400 (EDT)
To: psql novice <psql_novice(at)operamail(dot)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(at)perilith(dot)com>
> > Date: Mon, 12 May 2003 20:18:57 -0400 (EDT)
> > To: <pgsql-novice(at)postgresql(dot)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(at)postgresql(dot)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(at)postgresql(dot)org)
> >
>

--
____________________________________________
http://www.operamail.com
Get OperaMail Premium today - USD 29.99/year

Powered by Outblaze