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, justvarchar
. The default installation uses UTF-8. In fact the usual idiom is to use thetext
type, because omitting the length-specifier performs marginally better on postgres. - Date & Time: use
timestamp
fordatetime/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. plpgsql
script is strictly separated from “pure” standardSQL
in a way that T-SQL just doesn't bother with. If you are writing scripts,plpgsql
must either be inside afunction
or inside an 'anonymous function block'. It is always delimited by$$
or$somestring$
; andDeclare
variables in one block beforeBegin
: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.NetCommands
withParameters
, still use the@parametername
syntax as you would for SQL Server - Use
Function
notprocedure
: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)
- 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, 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
withSerial
or from Postgres 10 you can use the standard-but-wordyGENERATED BY DEFAULT AS IDENTITY
. For more complex options, Postgres uses SQL sequences. - Postgres replaces logins and database users and groups with a single concept:
role
. Permit a role to login with the With Login clause when youCreate
orAlter
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 withGrant Role1 to Role2
. I think this approach turns out to be much easier both to evolve and to understand and manage. - 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 or VarChar |
datetime |
timestamp |
Identity |
Serial The SQL-standard Generated by default as Identity works from Postgres 10. |
Raise |
Raise Exception |
Print |
Raise Notice |
Select Top 10 ... |
Select ... Fetch First 1 Rows Only ; |
Insert Into table Select ... |
Insert into Table (COLNAMES) Select ... |
If Then Begin … End |
If Then … ; |
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.
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’.
Why thankyou! Noted.
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.