SQL: PostgreSQL Commands
SQL: PostgreSQL Commands
While the PostgreSQL documentation is infinitely better than that for MySQL, there are still some commands that are difficult to remember.
ALTER TABLE
Adding a column to a table:
ALTER TABLE table ADD COLUMN column type;
Dropping a column from a table:
ALTER TABLE table DROP COLUMN column;
Adding a default value for a column and making it NOT NULL:
ALTER TABLE table ALTER COLUMN column SET DEFAULT 'default';
UPDATE table SET column='default' WHERE column IS NULL;
ALTER TABLE table ALTER COLUMN column SET NOT NULL;
The default value can be either a constant (e.g. 0, false, true, 'foo') or a function (e.g. NOW()).
Adding and removing a NOT NULL constraint on a column:
ALTER TABLE table ALTER COLUMN column SET NOT NULL;
ALTER TABLE table ALTER COLUMN column DROP NOT NULL;
Adding a primary key to a table:
ALTER TABLE table ADD PRIMARY KEY (column);
Shortcuts in PostgreSQL 9+
In PostgreSQL 9.1 you can add the new column, set it's attributes and default value all in a single statement. For example, to add a timestamp field that records the INSERT date, and populate it automatically for existing records with the current timesetamp:
ALTER TABLE table ADD COLUMN timestamp timestamp NOT NULL DEFAULT NOW();
And field data types can be changed without creating and deleting new columns:
ALTER TABLE table ALTER COLUMN column TYPE type;
Advanced Operations
Changing a TABLE to UNLOGGED to speed up INSERT operations:
BEGIN;
CREATE UNLOGGED TABLE table_alt AS SELECT * FROM table;
ALTER TABLE table RENAME TO old_table;
ALTER TABLE table_alt RENAME TO table;
COMMIT;
You can now drop the table old_table once you've confirmed the new one is working.
Using this method you also need to recreate any defaults, indexes or constraints that are missing from the new table. A more proper approach would be to create the 'alt' table explicitly and then run a transaction to copy the data.
References
- PostgreSQL: PostgreSQL Documentation