RE: Removing NOT NULL Contraint - Mailing list pgsql-admin

From Matthew
Subject RE: Removing NOT NULL Contraint
Date
Msg-id 183FA749499ED311B6550000F87E206C0C9169@SRV
Whole thread Raw
In response to Removing NOT NULL Contraint  (Matthew <matt@ctlno.com>)
List pgsql-admin
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

pgsql-admin by date:

Previous
From: Nicolas Huillard
Date:
Subject: RE: backup of my database...
Next
From: Matthew
Date:
Subject: RE: backup of my database...