From: | "tamsin" <tg_mail(at)bryncadfan(dot)co(dot)uk> |
---|---|
To: | "PostgreSQL-General" <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: Re: Adding a NOT NULL column? |
Date: | 2001-06-28 09:23:10 |
Message-ID: | NEBBKHBOBMJCHDMGKCNJKEEADAAA.tg_mail@bryncadfan.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
i've always had to add the column first and then do:
update pg_attribute set attnotnull = 't' where attname = 'fieldname' and
attrelid = (select oid from pg_class where relname = 'tablename');
but i'm using version 7.0.2, maybe the alter column to set not null works in
later versions?
tamsin
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Gregory Wood
Sent: 26 June 2001 20:11
To: Philip Molter
Cc: PostgreSQL-General
Subject: [GENERAL] Re: Adding a NOT NULL column?
> but how do set that new column to be NOT NULL? The postgres docs say
> do something like:
>
> ALTER TABLE t ADD (table constraint definition)
>
> so I would do that like:
>
> ALTER TABLE ADD c NOT NULL
> ALTER TABLE ADD CONSTRAINT c NOT NULL
>
> each time it fails on the 'NOT'. How do I add that constraint?
I believe you can either ALTER the column (much like you did when you added
the default value: ALTER TABLE t ALTER c SET DEFAULT 0), or ADD a CHECK
constraint which checks that 'c IS NOT NULL'. I don't think you can simply
*add* a 'NOT NULL' constraint to a column after the fact.
Both of these should work fine, although I'm more comfortable with the
syntax of the first:
ALTER TABLE ALTER COLUMN c NOT NULL;
-OR-
ALTER TABLE ADD CONSTRAINT CHECK (c IS NOT NULL);
Greg
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Inoue | 2001-06-28 10:08:59 | Re: Weird error |
Previous Message | Andreas Tille | 2001-06-28 09:16:01 | Re: Red Hat to support PostgreSQL |