Skip site navigation (1) Skip section navigation (2)

RE: Removing NOT NULL Contraint

From: Matthew <matt(at)ctlno(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: RE: Removing NOT NULL Contraint
Date: 2000-05-04 13:43:40
Message-ID: 183FA749499ED311B6550000F87E206C0C9169@SRV (view raw or flat)
Thread:
Lists: 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(at)eurocom(dot)od(dot)ua]
> Sent:	Thursday, May 04, 2000 1:44 AM
> To:	Matthew; pgsql-admin(at)postgresql(dot)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

Next:From: MatthewDate: 2000-05-04 13:46:50
Subject: RE: backup of my database...
Previous:From: Greg BrzezinskiDate: 2000-05-04 09:20:13
Subject: connect via libpq.dll using Ip adress...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group