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

Using PostgreSQL with WSO2 BAM

WSO2 Business Activity Monitor (BAM) can be used to address monitoring requirements in business activities and processes. It supports aggregating, analyzing and presenting information about business activities.

WSO2 BAM architecture is designed to handle these steps through its different modules.  Data Receivers in WSO2 BAM receive data and store them in a Cassandra data store. The Analyzer Engine will run analytics according to defined Hive queries and stores the result in a RDBMS data store. Finally the analyzed data will be fetched from the data store and shown in dashboards or reports.

This post will show how to integrate PostgreSQL into WSO2 BAM, which could be used to store analyzed data.