Thread: alter table

alter table

From
"Yuri A. Kabaenkov"
Date:
Hello,

      I've read documentation on alter table syntax and doesn't find
      anything about change column type or drop column.

      Also when i add column by command
      alter table test add column a integer not null default '10'

      It adds column but doesn't set default value.

      How can i fix it?



------------
With respect,
Yuri A. Kabaenkov
hellman@artofit.com


Re: alter table

From
Andrew Gould
Date:
Yuri,

You are correct that the alter table syntax does not
support changing the data type of a column or dropping
a column.

I've never added a column with default values, so I
could be wrong here; but if your new column contains
integers, shouldn't the default value be mentioned
without the single quotes?

Best of luck,

Andrew Gould

--- "Yuri A. Kabaenkov" <sec@artofit.com> wrote:
> Hello,
>
>       I've read documentation on alter table syntax
> and doesn't find
>       anything about change column type or drop
> column.
>
>       Also when i add column by command
>       alter table test add column a integer not null
> default '10'
>
>       It adds column but doesn't set default value.
>
>       How can i fix it?
>
>
>
> ------------
> With respect,
> Yuri A. Kabaenkov
> hellman@artofit.com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)


__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1

Re: alter table

From
wsheldah@lexmark.com
Date:

Hi,

alter table syntax currently (as of 7.1.3 at least) does not support changing a
column type or dropping a column. I believe these are scheduled to be supported
in a future release. There are two possible workarounds for these operations.

One is to create a temporary table with the same schema as your existing table,
select your data into the temporary table, drop the original table, create the
table with the new column definitions (or without the columns you're wanting to
drop), then select the data you want from the temporary table into the new
table, and drop the temporary table.

That will work fine, UNLESS you have some foreign keys, triggers, or other sorts
of dependencies on other tables. If you do, then the above will still appear to
work, but will also silently break those links, as triggers will continue to
refer to the oid of the old table. In this case, the most conservative advice
I've seen is to back up the entire database, both schema and data, with pg_dump,
then edit the SQL in the resulting file to change any table definitions that
need changing. If you're dropping a column, you'll probably also need to delete
the data for that column from the table's COPY statement. Then drop the
database, and restore it from your modified backup. That way any linkages to the
altered table will be recreated with the correct oid and therefore remain in
place. Obviously, this approach will require your database to be offline for as
long as it takes to drop and restore it.

Obligatory disclaimer: I have used the first method successfully, but I haven't
tried the second method (yet), it's based on earlier postings to this list. If I
have misstated it in any way, I trust that I'll be corrected in short order.
And of course you can always check the list archives.

Thanks,

Wes Sheldahl




"Yuri A. Kabaenkov" <sec%artofit.com@interlock.lexmark.com> on 11/30/2001
10:22:56 AM

Please respond to sec <sec%artofit.com@interlock.lexmark.com>

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] alter table


Hello,

      I've read documentation on alter table syntax and doesn't find
      anything about change column type or drop column.

      Also when i add column by command
      alter table test add column a integer not null default '10'

      It adds column but doesn't set default value.

      How can i fix it?



------------
With respect,
Yuri A. Kabaenkov
hellman@artofit.com


---------------------------(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: alter table

From
"Gregory Wood"
Date:
According to the documentation:

ALTER TABLE table [ * ] ADD [ COLUMN ] column type

Which means you can only set the column type of the new column. You'll have
to go back and alter that column to add a default:

ALTER TABLE table [ * ] ALTER [ COLUMN ] column { SET DEFAULT value | DROP
DEFAULT }

You'll no doubt also want to add that NOT NULL constraint:

ALTER TABLE table ADD table constraint definition

I know this only because it bit me in the ass the other day too :)

Greg

----- Original Message -----
From: "Yuri A. Kabaenkov" <sec@artofit.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, November 30, 2001 10:22 AM
Subject: [GENERAL] alter table


> Hello,
>
>       I've read documentation on alter table syntax and doesn't find
>       anything about change column type or drop column.
>
>       Also when i add column by command
>       alter table test add column a integer not null default '10'
>
>       It adds column but doesn't set default value.
>
>       How can i fix it?
>
>
>
> ------------
> With respect,
> Yuri A. Kabaenkov
> hellman@artofit.com
>
>
> ---------------------------(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: alter table

From
Andrew Sullivan
Date:
On Fri, Nov 30, 2001 at 11:44:12AM -0500, Gregory Wood wrote:
> According to the documentation:
>
> ALTER TABLE table [ * ] ADD [ COLUMN ] column type

[. . .]

> You'll no doubt also want to add that NOT NULL constraint:
>
> ALTER TABLE table ADD table constraint definition
>
> I know this only because it bit me in the ass the other day too :)

Also according to the documentation:

----

 In the current implementation, default and constraint clauses for
the new column will be ignored. You can use the SET DEFAULT form of
ALTER TABLE to set the default later. (You will also have to update
the already existing rows to the new default value, using UPDATE.)

 In the current implementation, only FOREIGN KEY constraints can be
added to a table. To create or remove a unique constraint, create a
unique index (see CREATE INDEX). To add check constraints you need to
recreate and reload the table, using other parameters to the CREATE
TABLE command.
----

So you can't add a NOT NULL constraint.  There's a handy ip on
techdocs, though (written by someone else) as to how to change things
to enfoce NOT NULL.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110