Thread: Changing a table column datatype

Changing a table column datatype

From
Date:
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


Re: Changing a table column datatype

From
"psql novice"
Date:
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


Re: Changing a table column datatype

From
Date:
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)
>


Re: Changing a table column datatype

From
Oliver Elphick
Date:
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


Re: Changing a table column datatype

From
Tom Lane
Date:
<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


Re: Changing a table column datatype

From
"psql novice"
Date:
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