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

RE: Removing all NOT NULL constraints from multiple tables easily

From: "Sokel, John" <jsokel(at)mdsi-usa(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Dan Browning <danb(at)cyclonecomputers(dot)com>
Subject: RE: Removing all NOT NULL constraints from multiple tables easily
Date: 2000-09-11 14:03:50
Message-ID: CAD59D8EA8B7124894637FC44951789F49703C@scratchy.int.mdsi-usa.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)cyclonecomputers(dot)com]
Sent: Monday, September 11, 2000 8:58 AM
To: 'Sokel, John'; pgsql-novice(at)postgresql(dot)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(at)mdsi-usa(dot)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(at)cyclonecomputers(dot)com
<mailto:danb(at)cyclonecomputers(dot)com> ] 
Sent: Monday, September 11, 2000 8:32 AM 
To: pgsql-general(at)postgresql(dot)org; pgsql-novice(at)postgresql(dot)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 

pgsql-novice by date

Next:From: Sokel, JohnDate: 2000-09-11 14:06:24
Subject: RE: Copy
Previous:From: Dan BrowningDate: 2000-09-11 13:57:49
Subject: RE: Removing all NOT NULL constraints from multiple tables easily

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