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: https://wiki.postgresql.org/wiki/First_steps 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 https://www.postgresql.org/docs/9.1/static/datatype-datetime.html 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 $$
    Declare
      anumber integer ;
      astring varchar(100);
    Begin
    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. https://pgxn.org/dist/plv8/doc/plv8.html.

  • 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 $$
    Declare 
      localvariable int;
      othervariable varchar(10);
    Begin
     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

2 thoughts on “Postgres quick start for MS SQL / T-SQL developers

  1. Rus

    This is good! One note — nobody uses varchar in Postgres (it is simply a text type with a length constraint). Unlike MS SQL, text columns are stored directly inside the table and thus there is no real reason to use varchar for strings; just make everything ‘text’.

    Reply

Leave a Reply

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