Postgres quick start for SQL Server / T-SQL Developers

After 17 years on T-SQL I'm at last on my first projects 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 first questions about connections, databases and login.
  • Postgres replaces logins and database users and groups with a single concept: ROLE.
    • Permit a ROLE to login with the With Login clause when you Create or Alter it:
      Create Role MyNameHere With Login.
    • Grant it access to a database with Grant Connect: Grant Connect on Database dbname TO roleName.
    • Make it a group with – well, it already is a group. Add other roles to it with Grant Role1 to Role2. I think this approach works out well both for evolving and managing users & groups.
  • No NVarchar is needed, just text. The default installation uses UTF-8. Text is the usual idiom over VarChar(n), allegedly it performs marginally better.
  • 'String concatenation is done with ' || ' a double pipe '.
  • Date & Time: use timestamp for datetime/datetime2. Read Date/Time Types 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.
  • Use Function not Procedure. (Use returns void if there is no return value) :
    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 the 'Create or Replace' syntax, and how Postgres has a really good Upsert syntax – Insert On Conflict Update ?
  • Postgres does function overloads, so to drop a function you must give the signature: Drop function functionname(int)
  • Procedural code in plpgsql script is strictly separated from “pure” standard SQL in a way that T-SQL just doesn't bother with. plpgsql code must always be inside a function or inside an 'anonymous function block' and always inside delimiters. The usual delimiter is $$ or $somestring$. 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, and the $$ delimiters are not special syntax for functions, they are the postgres syntax for a literal string constant in which you don't have to escape any special characters at all. So that's handy for any multiline strings.

  • Functions can be defined in other languages than plpgsql. For javascript, for example, see https://github.com/plv8/plv8.
  • 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 databases, \d to list relations (ie tables), and \? to list other meta commands.
  • 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
  • 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 from Postgres 10 with the SQL Standard GENERATED BY DEFAULT AS IDENTITY. (You will see Serial in older versions). For more complex options, Postgres uses SQL Sequences.
  • 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 Text
datetime
datetime2
timestamp
Identity Generated By Default As Identity
Before postgres 10 use Serial
Raise Raise Exception
Print Raise Notice
Select Top 10 ... Select ... Fetch First 10 Rows Only ;
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

Authorization

Usually you will connect to postgres specifying a username. The psql.exe commandline tool will default it to your windows username. You can avoid passwords in scripts that use psql by putting them in the pgpass.conf file). If you want to set up integrated security, the limitation is that for computers not in a Domain it only works on localhost. The instructions at Postgres using Integrated Security on Windows take about 5 minutes for the localhost case, and include a link to the extra steps for the Domain case.

3 thoughts on “Postgres quick start for SQL Server / T-SQL Developers”

  1. 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’.

  2. Thanks for taking the time to write this article! It is very useful – I am doing a hobby project in postgresql while in my day to day job I use SQL server. Your article helped me to mentally map the transition from sql server to postgresql. Of course, there will be more but it is good start.

Leave a Reply

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