After 17 years on
T-SQL, I at last started working on projects using 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:
- Permit a
ROLEto login with the With Login clause when you
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.
- Permit a
NVarcharis needed, just
text. The default installation uses UTF-8.
Textis the usual idiom over
VarChar(n), allegedly it performs marginally better.
'String concatenation is done with ' || ' a double pipe '.
- Date & Time: use
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.
- Before version 11, use
returns voidif there is no return value). From version 11, you can
Create Procedure()but note brackets are needed even for no parameters. A Procedure is called with CALL.
- A typical definition:
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)
- Function and Procedural code is strictly separated from “pure”
SQLin a way that T-SQL just doesn't bother with, and is typically written in
Declarevariables in one block before
Begin. For interactive work you can use an Anonymous Function block starting with
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 $$ delimiter is not a special syntax for functions, it is 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 multiline strings and quotes.
- If you don't need variables other than parameters, or control statements you can write a routine in pure sql by specifying:
Create or Replace Procedure ProcName(Date onDate) Language Sql As $$ Select * from LatestNews where PublishTimeStamp::Date = onDate $$;
- The double colon :: is the cast/conversion operator.
- 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 databases,
\dto list relations (ie tables), and
\?to list other meta commands.
- Variables: are only available inside plpgsql (or other language) code, not in plain SQL. Just use a plain identifier
@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
Parameters, still use the
@parameternamesyntax as you would for SQL Server
- Postgres has probably stayed ahead of of T-SQL in keeping close to the SQL standard syntax, though they both become more, not less, standards-compliant with each version. 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.
Identitywith the SQL Standard
GENERATED BY DEFAULT AS IDENTITYfor Postgres 10 onwards. (You will see
Serialin older versions). For more complex options, Postgres uses SQL Sequences.
- 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:
but stay with @variable for .Net clients using
Before postgres 10 use
||Before version 13, first websearch for
Usually you will connect to postgres specifying a username. The
psql.exe commandline tool will default it to your OS login username. You can avoid passwords in scripts that use
psql by putting them in the
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.
Your replacement for SQL Server Manager is the pgAdmin GUI which gets you nicely off the ground with a live monitoring dashboard.
Command Line and Scripting
psql -h localhost -d myDB -c 'Select Current_User'
runs the quoted command. Note
-h for “host”, not
-s for “server”. Use
psql --help to see more options. You can also use the pipe:
echo 'Select Version(), Current_Database(); Select Current_TimeStamp' | psql -h localhost
For large data dumps,
pg_restore are designed to generate consistent backups of entire databases or selected tables —data or DDL or both—without blocking other users.
There are more command line utilities