Updating columns in PostgreSQL tables effectively:


2. Changing a column from NULL to NOT NULL (or vice versa)

This is much easier -- there's a field in the system catalog we can
switch.

To change the email field to NOT NULL :

UPDATE pg_attribute SET attnotnull = TRUE
WHERE attname = 'email'
AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'pers') ;

(set the same field to FALSE to clear the NOT NULL setting.)

This change does *not* require any fixing of indexes, triggers, etc. The
only problem is when you change a column to NOT NULL, and there are null values in it. In this case, PostgreSQL will *allow* the initial change, but refuses any UPDATEs to records with NULL values in the field. If you do set it to NOT NULL when there are NULL values present, the only sane choice would be to immediate populate those NULL values.


3. Adding a Primary Key

A primary key in PostgreSQL is a unique index on a column which is NOT NULL. Unless you name the primary key differently, PostgreSQL assumes the name of the key is '_pkey'.

Creating a primary key is easy:

CREATE UNIQUE INDEX pers_pkey ON pers ( id );