Let me say before I begin that my move to Postgres has been nothing but great. Syntax wise Postgres is very similar SQL to that you are already familiar with. Feature wise you would probably compare it with an enterprise level database. To add to the good news you probably already know it is open source and very easy to get started.

Now, what I would really like to share are 6 handy things I learned during my move to Postgres.

Admin/Editors

When you start searching for editors you’re going to quickly find pgAdmin and phpPgAdmin. At first glance you will probably get the impression they are old dinosaurs.

Don’t let these two fool you, they are very powerful and feature rich. Give them a shot before shelling out a lot of cash for a high end GUI.

Serial

If you’re coming from Mysql or SQL Server you may be use to defining your auto incrementing fields. You still can if you like, but you will recognize a datatype called “serial” when you create your tables. The serial type will get the auto increment job done with no fuss. It will be an integer-like incrementing field starting at 1.

CREATE TABLE users
(
	id serial NOT NULL,
	name character varying(50),
	email character varying(150),
	is_active integer NOT NULL DEFAULT 0,
	CONSTRAINT id_pk PRIMARY KEY (id)
)

Limit and Offset

If you’re coming from SQL Server think of this as “TOP” on steroids. MySQL users are already familiar with limit. This will return X number of records starting at Y offset. There is no need to return more records than you intend to use.

-- return 5 records starting at 10 (records 10-15)
select * from my_dogs limit 5 offset 10

This is definitely an everyday use feature. You shouldn’t be returning 1,000 records if you only need 5. Also, you will find this makes pagination much easier than with “TOP” in SQL Server.

Regular Expressions

Regular Expressions with Postgres are very easy to use with the “~” comparison operator.

-- any dogs with a names starting with b or s and ending with y (Barkly and Scooby)
select * from my_dogs where name ~* '^[bs].*?y$'

This will use POSIX Regular Expressions to match. Of course you may still use LIKE to match if you prefer. Remember though, matching like this is very useful, but can be costly performance wise in any database.

Explain

A very easy to use EXPLAIN command will show you the execution plan of your query. This can be useful in finding bottlenecks and optimizing your queries easily.

-- The result set will be the explanation
explain select * from my_dogs where name ~* '^[bs].*?y$'

This will return the explanation of the execution plan instead of the query results. There are also a host of other tools if you need to dig deeper.

Arrays

Another gem you will find as you create your table structure are array datatypes. Postgres also comes with great set of array functions and operators as well. Retrieving them with languages such as php works as well, but the array is returned as a string: “{pos1,pos2,..}”.

CREATE TABLE my_dogs
(
	id serial NOT NULL,
	name character varying(50),
	birthdate date,
	color character varying(20),
	bite_reports timestamp without time zone[], -- array of timestamps
	CONSTRAINT id_primary_key PRIMARY KEY (id)
)

The feature set goes well beyond this just for a default install, so when you’re planning your next project keep Postgres in mind. Do you have any tips or experiences with Postgres?