Thread: Changing column modifiers?

Can you change a column modifier after you've created the table?  For
example, I have a table called "authors" with the following columns:
authorid, authorfirstname, authorlastname, authormi, statecode, country,
born, deceased.

When I created the table, I forgot to make the "authorfirstname" and
"authorlastname" columns NOT NULL.  I've looked at ALTER TABLE but I
don't see a way to use it to add NOT NULL...maybe I'm just missing the
obvious?

Any help/suggestions are appreciated.

Thanks,
Tim

It seems that it is not possible to alter column constraints and
table constraints do not include NOT NUL.
But you can use CHECK as workaround:

    ALTER TABLE authors ADD CHECK (NOT authorfirstname = NULL);

Arne.

Tim Boring wrote:
>
> Can you change a column modifier after you've created the table?  For
> example, I have a table called "authors" with the following columns:
> authorid, authorfirstname, authorlastname, authormi, statecode, country,
> born, deceased.
>
> When I created the table, I forgot to make the "authorfirstname" and
> "authorlastname" columns NOT NULL.  I've looked at ALTER TABLE but I
> don't see a way to use it to add NOT NULL...maybe I'm just missing the
> obvious?
>
> Any help/suggestions are appreciated.
>
> Thanks,
> Tim
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

ALTER TABLE authors ADD CHECK (NOT authorfirstname = NULL);
Should that be:
ALTER TABLE authors ADD CHECK (NOT authorfirstname IS NULL);
?
Anyway, how about something like the following:
UPDATE pg_attribute SET attnotnull='t' WHERE attname='field_name' AND
attrelid in (SELECT pg_class.oid FROM pg_class WHERE relkind='r' AND
relname='table_name');
Where field_name is the field name and table_name is the table name.
Hope this helps,
- Stuart

> -----Original Message-----
> From:    Arne Weiner [SMTP:aswr@gmx.de]
> Sent:    Monday, September 10, 2001 10:06 AM
> To:    pgsql-admin@postgresql.org
> Subject:    Re: Changing column modifiers?
>
>
> It seems that it is not possible to alter column constraints and
> table constraints do not include NOT NUL.
> But you can use CHECK as workaround:
>
>     ALTER TABLE authors ADD CHECK (NOT authorfirstname = NULL);
>
> Arne.
>
> Tim Boring wrote:
> >
> > Can you change a column modifier after you've created the table?  For
> > example, I have a table called "authors" with the following columns:
> > authorid, authorfirstname, authorlastname, authormi, statecode, country,
> > born, deceased.
> >
> > When I created the table, I forgot to make the "authorfirstname" and
> > "authorlastname" columns NOT NULL.  I've looked at ALTER TABLE but I
> > don't see a way to use it to add NOT NULL...maybe I'm just missing the
> > obvious?
> >
> > Any help/suggestions are appreciated.
> >
> > Thanks,
> > Tim
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html

On Mon, 10 Sep 2001 01:25:59 +0000 (UTC), Tim Boring <tboring@insight.rr.com>:
> Can you change a column modifier after you've created the table?  For
> example, I have a table called "authors" with the following columns:
> authorid, authorfirstname, authorlastname, authormi, statecode, country,
> born, deceased.
>
> When I created the table, I forgot to make the "authorfirstname" and
> "authorlastname" columns NOT NULL.  I've looked at ALTER TABLE but I
> don't see a way to use it to add NOT NULL...maybe I'm just missing the
> obvious?
>

How about creating a new table as you like it, then
selecting the data from the old table into the new one
dropping the old table and renaming the new one?

Roundabout, but might be useful.