skip to content

SQL: PostgreSQL Commands

 Tweet0 Shares0 Tweets

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

< SQL

Send a message to The Art of Web:


used only for us to reply, and to display your gravatar.

CAPTCHA

<- copy the digits from the image into this box

press <Esc> or click outside this box to close

Post your comment or question
top