PostgreSQL for the SQL Server Guy

PostgreSQL is an open source RDBMS that is very similar to SQL Server.  In this post I'll compare the features and syntax of the two.  Learning a new DBMS tool can be a lot of fun and will help you understand SQL Server even better.  

I've been using PostgreSQL a lot lately and I'm really loving it.  Whenever I learn a new database manager I like to compare its general features to what I already know best, namely SQL Server and Oracle.  I did an entire blog series on Vertica where I compared its features to SQL Server's and dove deeper where Vertica had compelling features.  I'm not going to go into that much detail with Postgres.  

Who cares?

If you are a SQL Server Guy, you may be wondering why you should bother learning Postgres?  Very simple.  When you learn a new database manager you begin to think critically, or at least differently, about SQL Server.  For instance, Oracle has no concept of "clustered indexes", yet their queries don't need them.  Why is that?  Well, when you learn the Oracle architecture you discover that something like "a sorted table" really isn't all that important.  Another example...Oracle DBAs never bother "reindexing" their tables.  For years that was a standard practice in the SQL Server world.  Only recently have DBAs stopped reindexing their tables.  Why?  They realized, like Oracle people have known for years, that in most cases if you have fragmentation you really don't need to worry about unless you have a scan-heavy workload.  Fragmentation doesn't matter for seeks.  And if you have heavy fragmentation with a scan-heavy workload, the better fix involves changing your FILLFACTORs (known as PCTFREE in Oracle) as well as your general design.  

Do you see how we can all learn from each other?  

It's also very common to meet experts ofother DBMS vendor technologies who will spout lies and misconceptions about SQL Server.  You don't want to be one of those people.  When someone spouts a stupid comment you want to be knowledgeable enough about the competition to be able to refute those lies.  Don't just be the SQL Server Guy who hates the SQL Server Guy who hates Hadoop for valid, innumerable reasons.  Don't be ignorant of a technology which then causes you to make stupid statements.  

The following tables list the key features of any RDBMS and then compares SQL Server to PostgreSQL.  This is not meant to be an exhaustive list.  Just something to whet your appetite.  

General Features

FeatureSQL ServerPostgreSQL
Pronunciation (you don't want to sound "uninformed" when you discuss a technology)"sequel server""POST grez" or "Post grez Queue El"
LicensingAnywhere from "free" (SQL Express) to super-expensivesimilar to MIT or BSD.  Very liberal (no attribution is even required).  Postgres' biggest open source competitor is probably MySQL, which is GPL-licensed.  IMO, GPL licensing can be dangerous for ISVs.  See [[Open Source Licensing]].  
default port number (defined with IANA)14335432
determine the versionselect @@versionSELECT version();
Primary GUI administration/query toolSSMSpgAdmin
command line query execution toolsqlcmd/osqlpsql
redo transaction logstransaction logs (.ldf files)write-ahead logs (WAL)...very similar to Oracle's redo logs
recovery modeFULL or SIMPLEarchive_mode = on is the same as FULL.  
transaction isolation and concurrency semanticsvarious, but mostly READ COMMITTED AND SNAPSHOT ISOLATION are the most commonMVCC (multi-version concurrency control) which is identical to RCSI (including the concept of the row version store)


(pgAdmin screenshot)


Basic DBA Tasks

FeatureSQL ServerPostgreSQL
Primary Backup MethodBACKUP DATABASE which creates a self-contained backuphot physical backups of files with continuous archiving.  This closely resembles Oracle's hot backups.  Postgres, nor Oracle, has a concept similar to SQL Server's backup method.  You essentially take each "file" offline and use scp, rsync or any other copy tool to copy it elsewhere, as well as the write-ahead logs (WAL).  
Logical BackupsSQL Server has no concept of this natlvely.  Maybe something like "copy database wizard"Logical recovery executes SQL to re-create the database objects.  pg_dump > outfile.sql generates the dump...psql dbname < infile.sql restores the .sql file.  Oracle works identically.  pg_dump simply executes SQL statements against the database to unload the data.  The data is consistent, thanks to MVCC, as of the point-in-time when pg_dump started.  You can restore this data and then apply the WAL.  But you can restore selective lists of objects, schemas, or just structure and no data, etc etc.  
ReplicationYesHas similar feature set to SQL Server.  
"Log Shipping"YesVery similar.  The "hot physical backup files" are copied to the destination and then the WAL files are copied automatically when they "roll over".  Also called "log shipping"
Can queries run on Log Shipped database?Yes, if db is in STANDBY mode.  Queries must be quiesced whenever log backups are restoredYes.  This is called "hot standby" or "continuous replication".  In fact, unlike SQL Server, Postgres allows the queries to continue running while the logs are being applied.  


Programming Features

FeatureSQL ServerPostgreSQL
main procedure languageTransact-SQLPL/pgSQL.  This is so similar to TSQL that you can pick it up in just a few minutes.  
Alternate LanguagesAnything that can produce CLR codePL/Perl, PL/Python and tons more.  
CREATE OR REPLACE syntaxNo.  Have to query sys.objects and DROP then CREATE the objectsupports this feature for most db objects.  This is soooo much easier.  Microsoft, when will you fix this?  
metadata schemasys. or information_schema.information_schema. or pg_* tables
hierarchy of objectshost-->server instance-->database-->schema-->objecthost-->server instance-->database-->schema-->object
list all databases on the instanceselect name from sys.databases;select datname from pg_database;
object identifiers[table] (preferred) or "table" depending on quoted_identifiers settingobject names can contain spaces and mixed case characters if enclosed in double quotes. Otherwise object names are assumed to be lower-cased.  If you create your objects using quoted names, then you need to write your SQL using quoted names.  Converts all non-quoted names to lowercase when used within an SQL statement.  This can be tricky.  It's best to keep all object names in lower case only.  
data loading toolbcp\COPY something FROM something.txt CSV HEADER or use pgloader which is a bit more friendly.  
windowing functionsrow_number() over()same.  all windowing functions from SQL Server are present in Postgres.  In fact, Postgres is even richer. 
cte's, recursive cte's, updateable cte'sPostgres works identically to SQL Server


Some Example PL/pgSQL Code

CREATE OR REPLACE FUNCTION fn_constraint_exists (text) RETURNS integer
AS $$
IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = $1) THEN


$$ LANGUAGE plpgsql;

That's very close to TSQL.  Interesting stuff:

  • $$ as a delimiter where you declare the actual language you are using for the code.  
  • CREATE OR REPLACE...I love that feature.  
  • arguments can be named or position (where you only declare the datatype, like above).  You reference positional parameters using $n syntax.  
  • IF...THEN...ELSE...END IF I think is cleaner than TSQL's method


(a graphical EXPLAIN PLAN in pgadmin...looks pretty much like a SQL Server graphical plan)

You have just read "[[PostgreSQL for the SQL Server Guy]]" on If you found this useful please feel free to subscribe to the RSS feed.