Thread: Removing NOT NULL Contraint

I may be missing something simple here, but I have an attribute in a
table that was created as NOT NULL.  I now need to remove this
constraint.  I don't see a proper way to do this.  I think I can update
the pg_attribute table and change attnotnull from true to false.  Is
this an acceptable solution or is there a good reason that I should not
directly edit the system tables?

Maybe I'm missing something but I didn't see another way to do it other
then creating a new table, selecting into it and dropping the old table,
which seems like a lot of work for something so simple.

Thanks,

Matt

When I need to change table (e.g. adding new column or changing col attr)

I used the following:
1) dump the table: pg_dump -t my_table > my_table.out
2) edit my_table slightly (e.g. remain only COPY statement)
3) delete table my_table (also my_table's sequences if any)
4) create table as it should be
5) populate table, e.g. pgsql -e my_db < my_table.out
6) setting sequences, etc. (e.g. select setval('my_table_sequence', ...))

If you will know more convinient way, please let me now.

Matthew wrote:

> I may be missing something simple here, but I have an attribute in a
> table that was created as NOT NULL.  I now need to remove this
> constraint.  I don't see a proper way to do this.  I think I can update
> the pg_attribute table and change attnotnull from true to false.  Is
> this an acceptable solution or is there a good reason that I should not
> directly edit the system tables?
>
> Maybe I'm missing something but I didn't see another way to do it other
> then creating a new table, selecting into it and dropping the old table,
> which seems like a lot of work for something so simple.
>
> Thanks,
>
> Matt

I think in a lot of situations that process is necessary, but all I
wanted to do was change the not null flag from true to false.

I went ahead and test this out and it worked.  All I did was:
update pg_attributes set attnotnull = 'f' where oid =
oidofnotnullcolumn;
vacuum analyze;

Everything seemed to work and I have not had any problems stem from
this.


> -----Original Message-----
> From:    Vladimir V. Zolotych [SMTP:gsmith@eurocom.od.ua]
> Sent:    Thursday, May 04, 2000 1:44 AM
> To:    Matthew; pgsql-admin@postgresql.org
> Subject:    Re: [ADMIN] Removing NOT NULL Contraint
>
> When I need to change table (e.g. adding new column or changing col
> attr)
>
> I used the following:
> 1) dump the table: pg_dump -t my_table > my_table.out
> 2) edit my_table slightly (e.g. remain only COPY statement)
> 3) delete table my_table (also my_table's sequences if any)
> 4) create table as it should be
> 5) populate table, e.g. pgsql -e my_db < my_table.out
> 6) setting sequences, etc. (e.g. select setval('my_table_sequence',
> ...))
>
> If you will know more convinient way, please let me now.
>
> Matthew wrote:
>
> > I may be missing something simple here, but I have an attribute in a
> > table that was created as NOT NULL.  I now need to remove this
> > constraint.  I don't see a proper way to do this.  I think I can
> update
> > the pg_attribute table and change attnotnull from true to false.  Is
> > this an acceptable solution or is there a good reason that I should
> not
> > directly edit the system tables?
> >
> > Maybe I'm missing something but I didn't see another way to do it
> other
> > then creating a new table, selecting into it and dropping the old
> table,
> > which seems like a lot of work for something so simple.
> >
> > Thanks,
> >
> > Matt