Is there a way to add a default value definition to an existing column? Something like an "alter table... alter column... default 'foo'".

I thought of a clumsy way to do this... create a temp column, set it's value to that of the column to be altered, drop the column to be altered, redefine it with the default, shunt all the values in the temp column over to the new column and then drop the temp column. But I have before and after triggers on the table that react to changes in this column (not to mention the need for it's existence).

I could add something to the before trigger to do this too. But it would be cleaner to do this as a column property.

Thanks for any help.

Search Discussions

  • Mike Fowler at Apr 13, 2011 at 4:26 pm
    Hi Dave,
    On 13/04/11 17:21, Gauthier, Dave wrote:

    Is there a way to add a default value definition to an existing
    column? Something like an "alter table... alter column... default 'foo'".
    Sure is something like that:

    ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT expression;

    For full documentation see:
    http://www.postgresql.org/docs/9.0/static/sql-altertable.html
    Thanks for any help.
    Regards,

    --
    Mike Fowler
    Registered Linux user: 379787
  • Tom Lane at Apr 13, 2011 at 4:30 pm

    "Gauthier, Dave" <dave.gauthier@intel.com> writes:
    Is there a way to add a default value definition to an existing column? Something like an "alter table... alter column... default 'foo'".
    ALTER TABLE ... ALTER COLUMN ... SET DEFAULT ...

    regards, tom lane
  • Andrew Sullivan at Apr 13, 2011 at 4:32 pm

    On Wed, Apr 13, 2011 at 09:21:20AM -0700, Gauthier, Dave wrote:
    Is there a way to add a default value definition to an existing column? Something like an "alter table... alter column... default 'foo'".
    ALTER TABLE table ALTER [ COLUMN ] column SET DEFAULT expression

    (see http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html)

    Note that this doesn't actually update the fields that are NULL in the
    column already. For that, once you had the default in place, you
    could do

    UPDATE table SET column = DEFAULT WHERE column IS NULL

    IIRC.

    A

    --
    Andrew Sullivan
    ajs@crankycanuck.ca
  • Harald Fuchs at Apr 14, 2011 at 2:54 pm
    In article <20110413163120.GU24471@shinkuro.com>,
    Andrew Sullivan <ajs@crankycanuck.ca> writes:
    On Wed, Apr 13, 2011 at 09:21:20AM -0700, Gauthier, Dave wrote:
    Is there a way to add a default value definition to an existing column? Something like an "alter table... alter column... default 'foo'".
    ALTER TABLE table ALTER [ COLUMN ] column SET DEFAULT expression
    (see http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html)
    Note that this doesn't actually update the fields that are NULL in the
    column already. For that, once you had the default in place, you
    could do
    UPDATE table SET column = DEFAULT WHERE column IS NULL
    And you probably want to do
    ALTER TABLE table ALTER [ COLUMN ] column SET NOT NULL
    after that.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedApr 13, '11 at 4:22p
activeApr 14, '11 at 2:54p
posts5
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase