Postgres quick start for MS SQL / T-SQL developers

After 17 years on T-SQL I’m at last on my first project with Postgres. Here are the first things I needed for the transition:

  • Do this first: as it answers most of your first questions about connections, databases, using roles for logins, users and groups, and so on.
  • 'String concatenation is done with ' || 'a double pipe '.
  • No NVarchar is needed, just varchar. The default installation uses UTF-8.
  • Date & Time: use timestamp for datetime/datetime2
  • Read for more on dates, times and intervals.
  • Use more semicolon terminators If ... Then ... ; End If ;
  • plpqsql script is separated from “pure” SQL in a way that T-SQL just doesn’t bother with. If you are writing scripts, plpqsql must either be inside a function or inside an ‘anonymous function block’, usually delimited by $$ or $somestring$; and Declare variables in one block before Begin :
    Select 'This line is plain SQL' ;
    Do $$
      anumber integer ;
      astring varchar(100);
    If 1=1 Then
     Raise Notice 'This block is plpgsql' ;
    End If;
    End $$ ;
    Select 'This line is SQL again' ;

    In fact, functions are defined in a string, and the $$…$$ delimiters is a postgres syntax for specifying a string constant without having to escape any special characters at all. They can be used anywhere in code. Functions can be defined in other languages than plpgsql. See e.g.

  • Variables: just use a plain identifier: myvariablename with no @ symbol or other decoration. BUT as a consequnce you must avoid variable names in a query that are the same as a column name in the same query. BUT BUT in Ado.Net Commands with Parameters, still use the @parametername syntax as you would for SQL Server
  • Use Function not procedure:
    Create or Replace Function procedureName( forId int, newName varchar(20) )
    Returns int -- use returns void for no return
    As $$
      localvariable int;
      othervariable varchar(10);
     Insert into mytable (id,name) values (forId, newName) On Conflict (id) Do Update Set Name=NewName ;
    End $$;
  • Ooh, did you notice how postgres has a really simple upsert syntax?
  • Postgres does function overloads, so to drop a function you must give the signature: Drop function functionname(int)
  • Whereas the T-SQLer does everything in T-SQL, other database systems use non-sql commands for common tasks. For working at a command-line, learn about psql meta-commands.. Start with \c to change database, \l to list object, and \? to list other meta commands.
  • Postgres have (I think) made more effort than MS to stay close to the SQL standards and as with T-SQL, the docs go into detail on deviations. But this means that much of your code for databases, schemas, users, tables, views, etc can be translated fairly quickly.
  • Replace Identity with Serial. For more complex options, Postgres uses SQL sequences.
  • Replace Select top 1 ... with Select ... Fetch First 1 Only.
  • role covers all of logins (Create Role ... Login) and database users (Grant Connect on Database ... TO ...) and also covers groups.
  • The Nuget package for Ado.Net, including for .Net core, is Npgsql
  • Scan these

Converting T-SQL to Postgres

Here’s my initial search/replace list for converting existing code:

Search Replace
@variableName _VariableName
NVarChar VarChar
Identity Serial
UniqueIdentifier uuid
Raise Raise Exception
Print Raise Notice
Insert Into table Select … Insert into Table (COLNAMES) Select …
If Then Begin … End Else Begin … End If Then … ; Else … ; End If ;
Create Table #Name Create Temporary Table _Name
NewId() websearch Create Extension “uuid-ossp” and then use uuid_generate_v1() or uuid_generate_v4()
Alter Function|Procedure Create or Replace Function

Leave a Reply

Your email address will not be published. Required fields are marked *