PostGreSQL

I’ve spent some time lately exploring database systems other than SQL Server. One I’ve looked at is PostGreSQL. PostgreSQL is an object-relational database, and is open source. It can run in Windows, and comes in a one click installer. The install includes the server, the pgAdmin III graphical interface, and the stack builder, which is used to obtain add-ons to complement the DB.
In the admin GUI, at the server level are nodes for Databases, Tablespaces (a directory specified to hold files to store database objects and data), Group Roles and Login Roles.
At the Database level are nodes for Catalogs (ANSI and PostGreSQL, which contains Built-in views, functions, etc.), Extensions, Schemas and Slony Replication (Master to multiple slaves).
Under the Schema tab is where you’ll find Tables, Functions, views, etc.
In version 9.2.1 (the version I’m using), one of the new features is Multi-version concurrency control (MVCC), similar to the snapshot isolation used in SQL server.
One thing that took me a little while to figure out with querying was delimiting the object names using double quotes:
select * from “TestSchema”.”ParentTable”;

and using single quotes to delimit string data.

On first glance, there were two things that stood out to me. The first was the long list of data types supported, including:
JSON
ENUM – Enumerations
Serial – auto incrementing int
UUID – GUID
OID – Object ID
Geometric – Polygon, Circle, Line
Interval – time interval
Intet – IP address
Hstore – Key/Value pairs

The other thing was that PG supports table inheritance. So when a table is created, you can specify a table to inherit from, and your new child table will inherit all of those columns. You can then extend the child column to include its own columns in addition. Inserting a record into the child table will create a record in the parent table as well.

Advertisements

One Response to PostGreSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: