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

Running Multiple Instances of ActiveMQ on the same server

To run multiple instances of Apache ActiveMQ on the same server, it is required to do some configuration changes.

First, go to ActiveMQ installation directory.

cd ~/apache-activemq-5.8.0

Then create instance1 and instance2 by running the following commands.

./bin/activemq create instance1
./bin/activemq setup ~/.activemqrc-instance-instance1
ln -s activemq bin/activemq-instance-instance1

./bin/activemq create instance2
./bin/activemq setup ~/.activemqrc-instance-instance2
ln -s activemq bin/activemq-instance-instance2