This is a followup to an earlier post I wrote on using Postgres and the differences between it and SQL Server.

— Data Types:
There isn’t a TinyInt in Postgres, SmallInt is the smallest integer type available.
Using varchar without specifying a length won’t enforce a max length, so similar to SQL Server’s varchar(max).

— Functions:
LENGTH still returns the length of a given string, but Postgres doesn’t allow the use of LEN, as I usually use with SQL Server.
Instead of using CHARINDEX to find the index of a character within a string, PG uses POSITION. For example, to find a dash within a Name column, I’d use: POSITION (‘-‘ IN name)

— Creating or Changing Objects:
Instead of using CREATE OR ALTER for a database object, Postgres uses CREATE OR REPLACE.
Also, I had issues with running CREATE OR REPLACE for a view when I added a column anywhere but at the end of a column list for a view. I ended up having to drop and recreate the view anytime I wanted to add a column in the middle of the column list.

–Declare and set variables
Variables can be declared and used within functions, but I didn’t see where they can be used outside of functions, like they can be used in SQL Server in an ad-hoc script.

— Raise errors
RAISE EXCEPTION ‘error message’;

— Identity Values
I mentioned in the part 1 article about IDENTITY values and how Postgres uses Sequences to generate these values. I saw an article that used SERIAL to generate these values in a CREATE TABLE statement. However, I saw a later article that said not to use SERIAL, but to use the GENERATED command.
For example:

CREATE TABLE test(
Id int GENERATED ALWAYS AS IDENTITY NOT NULL,
Name varchar(20) NOT NULL
);

The GENERATED command gives us a few more options than SERIAL. SERIAL doesn’t allow us to specify a data type, Integer is used, where with GENERATED we can specify the data type, so we could use a Big Int or Small Int instead. Also, SERIAL allows a user to insert their own value, which can causes a collision at some point, since the Sequence won’t know to skip the manually inserted value. The GENERATED ALWAYS command will raise an error if the user tries to insert their own value for ID (Although that behavior can be overridden, if needed).

— Schemas:
Postgres allows use of IF EXISTS in a CREATE SCHEMA statement:

CREATE SCHEMA IF NOT EXISTS test;

Also, we can drop a schema and all of the objects it contains with:

DROP SCHEMA test CASCADE;

— Miscellaneous:
Use INSERT INTO table_name instead of INSERT table_name.
Cluster is the PostGres term for an instance, a group of databases.

Also, I ran into an issue using Azure Data Studio with Postgres. It wouldn’t allow me to create a table by using the SELECT … INTO syntax, apparently the extension doesn’t support that.
GitHub Issue

Links:
PostGreSQL: Don’t Do This

Data Types: SQL Server to Postgres

Brent Ozar – Two Important Differences Between SQL Server and PostgreSQL