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:
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.