Useful PostgreSQL Queries Compared with MySQL

Recently while creating an SQL script for PostgreSQL, I found that some of the queries where not straightforward, specially if you are used to MySQL. I'm listing some of them here in case someone finds it useful.

Auto Increment

In MySQL you usually use AUTO_INCREMENT attribute as follows.

CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
Name varchar(255)
)

In PostgreSQL a similar result can be obtained by using SERIAL data type.

CREATE TABLE users (
id SERIAL
);

SERIAL is not an actual type, it is just a keyword used for convenience which is equivalent to specifying:

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
id integer DEFAULT nextval('users_id_seq') NOT NULL
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL

Update with Multi Table Join

In MySQL you could do:

UPDATE users, customers SET users.age = customers.age
WHERE customers.id = users.id

In PostgreSQL:

UPDATE users SET users.age = customers.age
FROM customers
WHERE customers.id = users.id

http://www.postgresql.org/docs/9.1/static/sql-update.html

Get Database Name

SELECT current_database();

http://www.postgresql.org/docs/9.1/static/functions-info.html

Remove or Modify Table Constraint

Modifying a constraint is done by first dropping the constraint and then adding the new constraint. So I'll show how to drop a constraint.

If you know the name of the constraint, it can be easily dropped using ALTER TABLE command. If you did not specify a constraint name when creating it, then first you need to find out the name of the constraint. When executing manually, this can be found out by using shell commands. But doing this in a script takes some work.


In MySQL, variables can be used to store the constraint name and then later drop it:

SELECT DATABASE() into @db FROM DUAL; 

SELECT CONSTRAINT_NAME
INTO @myvar
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'users' AND CONSTRAINT_TYPE ='UNIQUE' AND TABLE_SCHEMA = @db;

SET @stmt = CONCAT('ALTER TABLE users DROP INDEX ', @myvar);
PREPARE stmt2 FROM @stmt;
EXECUTE stmt2;

PostgreSQL does not have variables as above, so this has to be done using a function:

CREATE OR REPLACE FUNCTION dropconstraint(table_name text, con text) RETURNS void AS $$
BEGIN
EXECUTE 'ALTER TABLE ' || table_name ||' DROP CONSTRAINT ' || con;
END;
$$ LANGUAGE PLPGSQL;

SELECT dropconstraint('users',conname)
FROM pg_constraint
WHERE contype = 'u' AND conrelid =
(SELECT oid FROM pg_class WHERE relname = 'users');

http://www.postgresql.org/docs/9.1/static/catalog-pg-constraint.html
http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html