building menu...

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:

ALTER TABLE table ALTER COLUMN column SET DEFAULT default;
UPDATE table SET column=default;

The default value can be either a constant (eg. 0, false, true, 'foo') or a function (eg. NOW()).

Note: It is possible in Postgres 8+ to use ALTER TABLE table ADD COLUMN column type DEFAULT default but this results in the table being completely rebuilt and any exising oids will be reset to new values. The Postgres team have said that this will be fixed in future releases.

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);

References

PostgreSQL Documentation

[Back to SQL]

SQL: PostgreSQL Commands

ALTER TABLE

References

Send Feedback