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 or indeed text is the usual idiom. 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 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 $$
    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, 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. https://pgxn.org/dist/plv8/doc/plv8.html.

  • 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 $$
    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 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
but stay with @variable for .Net clients using Npgsql
NVarChar VarChar or text
datetime
datetime2
timestamp
Identity Serial
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
UniqueIdentifier uuid
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

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 *