Thread: 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
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
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)
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)
>
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