Thread: RE: Removing all NOT NULL constraints from multiple tabl es easily

RE: Removing all NOT NULL constraints from multiple tabl es easily

From
patrick.wolf@Aerojet.com (WOLF, PATRICK)
Date:
Or, do

 pg_dump -s -t tablename

will output the schema for tablename

> -----Original Message-----
> From:    Sokel, John [SMTP:jsokel@mdsi-usa.com]
> Sent:    Monday, September 11, 2000 8:04 AM
> To:    pgsql-novice@postgresql.org
> Cc:    Dan Browning
> Subject:    RE: [NOVICE] Removing all NOT NULL constraints from multiple
> tabl es easily
>
> If you do a pg_dump on the db, the first part of the data will be the
> create script.  Just pull that part of the file off and alter it.
>
> John
>
> -----Original Message-----
> From: Dan Browning [mailto:danb@cyclonecomputers.com]
> Sent: Monday, September 11, 2000 8:58 AM
> To: 'Sokel, John'; pgsql-novice@postgresql.org
> Subject: RE: [NOVICE] Removing all NOT NULL constraints from multiple
> tables easily
>
>
> Does anyone have a program (or psql command?) that will analyze a table
> and generate a CREATE TABLE ... statement?  I ask because I don't have the
> create table statements handy, and it would take a while for me to
> recreate them (plus spelling errors, etc.)
>
>     -----Original Message-----
>     From: Sokel, John [mailto:jsokel@mdsi-usa.com]
>     Sent: Monday, September 11, 2000 6:57 AM
>     To: Dan Browning
>     Subject: RE: [NOVICE] Removing all NOT NULL constraints from
> multiple tables easily
>
>
>
>     I don't think you can alter that on the fly, but an easier way is
> too just use 'select into' a temporrary table, recreate, and insert the
> data back to the original file.  That's what I always do, usually pretty
> quick (unless you have gobs of data).
>
>     ** Create a temporrary table
>     Select * into <temp_table> from <orig_table>;
>
>     ** Recreate the file
>     Drop table <orig_table>;
>     Create table <orig_table> ...;
>
>     ** Put the data back in
>     Insert into <orig_table> select * from <temp_table>;
>
>     ** Clean up
>     Drop table <temp_table>;
>
>     -----Original Message-----
>     From: Dan Browning [ <mailto:danb@cyclonecomputers.com>]
>     Sent: Monday, September 11, 2000 8:32 AM
>     To: pgsql-general@postgresql.org; pgsql-novice@postgresql.org
>     Subject: [NOVICE] Removing all NOT NULL constraints from multiple
> tables
>     easily
>
>
>     I would like to remove all NOT NULL constraints from my tables.
>
>     I've tried ALTER TABLE, but I can't seem to get the syntax right,
> and the
>     docs aren't clear to me.
>     What is the correct syntax?  If there's another way, short of
> manually
>     recreating (dump, create, import) the tables, I'd love to hear it.
>
>     Thanks,
>
>     Dan Browning
>     Network & Database Administrator
>     Cyclone Computer Systems
>