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 or indeed text. The default installation uses UTF-8.
  • Date & Time: use timestamp for datetime/datetime2
  • Read for more on dates, times and intervals.
  • Use semicolon terminators nearly everywhere. If ... Then ... ; End If ; T-SQL doesn’t need them but Postgres demands them.
  • plpqsql script is strictly separated from “pure” standard 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’. It is always 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, Postgres functions are defined in a string. The $$…$$ delimiters is actually a postgres syntax for a literal string constant anywhere in code, in which you don’t have to escape any special characters at all. 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 consequence 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 Rows Only ;.
  • role is a single concept that replaces logins (Create Role ... Login) and database users (Grant Connect on Database ... TO ...) and groups (Grant Role1 to Role2)
  • 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
IsNull Coalesce
NewId() First websearch for Create Extension "uuid-ossp" to install the guid extension to a database. Then you can use uuid_generate_v4() and other uuid functions
Alter Function|Procedure Create or Replace Function

Deep Learning & Unintended Algorithm Bias

This was a 5 minute talk on deep learning for the very excellent @chesterdevs. Like others talking about deep learning, I took visuals and the face-learning example from the landmark 2012 paper, Quoc Le/Google/Andrew Ng paper, “Building High-level Features Using Large Scale Unsupervised Learning.”

Only afterwards did I notice that the subset of images which their system show as “most like a face” from their test set were 90% male and 90% white, as is the prototypical face that the machine outputs.

And so we have a neat demonstration of unintended algorithm bias: their input was 10 million randomly-chosen youtube videos; the output was white and male. I bet they didn’t expect that.

A salutary reminder that—as the hard-working statistician will tell you—“random selection” does not mean “unbiased”.

AutoHotKey script for they who, being Mac Users and also equipped with an Apple keyboard, yet still they work at a Windows desktop

Surprising how much time you can spend on these little niggles…

  • Irritated that Windows doesn’t have an ellipsis key?
  • Wondering how to do printscreen from your apple keyboard?
  • Really really fed up with swapping between Cut‘n’Paste is “⌘-C,⌘-V” and Cut‘n’Paste is “Ctrl-c-Ctrl-v”?

Help is at hand.


When I first came across I was a bit unsure about using AutoHotKey. But I have seen the light. It is the bee’s knees. It is open source, widely used for years, free, small footprint and is the ultimate customise-all-the-things tool for Windows. It is a combined scripting tool & Keyboard/Mouse hotkey manager.

My AutoHotKey for a Mac User with an Apple keyboard on Windows Script

There you are. It’s all you need. That, and a few hours to customise it yourself. Then a few more hours to… oh, never mind.


When I said that AHK is the bee’s knees, I didn’t say that the language isn’t arcane, unintuitive and bearing signs of organic growth over a decade or more…