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
rolesfor logins, users and groups, and so on.
'String concatenation is done with ' || 'a double pipe '.
NVarcharis needed, just
varchar. The default installation uses UTF-8.
- Date & Time: use
- 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 ;
plpqsqlscript is separated from “pure”
SQLin a way that T-SQL just doesn’t bother with. If you are writing scripts,
plpqsqlmust either be inside a function or inside an ‘anonymous function block’, usually delimited by
Declarevariables in one block before
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:
@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
Parameters, still use the
@parameternamesyntax as you would for SQL Server
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
\cto change database,
\lto 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.
Serial. For more complex options, Postgres uses SQL sequences.
Select top 1 ...with
Select ... Fetch First 1 Only.
rolecovers all of logins (
Create Role ... Login) and database users (
Grant Connect on Database ... TO ...) and also covers
- The Nuget package for Ado.Net, including for .Net core, is
- Scan these
Converting T-SQL to Postgres
Here’s my initial search/replace list for converting existing code:
|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|