PostgreSQL has a richer type system than MySQL. The table below highlights the most common differences:

MySQL uses AUTO_INCREMENT to create auto-incrementing columns. PostgreSQL has two approaches:

SERIAL (traditional, still widely used):

CREATE TABLE person (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

GENERATED AS IDENTITY (SQL standard, introduced in PostgreSQL 10) comes in two variants:

-- Strict: manual inserts are rejected; the value is always generated
CREATE TABLE person (
  id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100)
);

-- Flexible: a generated value is used unless you supply one explicitly
CREATE TABLE person (
  id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100)
);

Use GENERATED ALWAYS when the identity column should never be set manually (safer for surrogate keys). Use GENERATED BY DEFAULT when you occasionally need to insert an explicit value, for example when migrating data from another database.

A UUID (Universally Unique Identifier) is a 128-bit value represented as a 32-character hex string, for example a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. PostgreSQL has a built-in UUID type that stores it efficiently in 16 bytes.

UUID is an alternative to integer primary keys. The main reasons to use it:

• UUIDs can be generated anywhere — in the application, on a client device, or in multiple databases — with a negligible risk of collision.
• Random UUIDs (UUIDv4) do not reveal how many rows exist or in what order they were created.
• Easier to merge data from multiple databases or distribute writes across servers

The downside is that UUID primary keys are larger (16 bytes vs 4 bytes for INT) and because they are random, inserting many rows can fragment indexes and slow down range scans compared to sequential integers.

In PostgreSQL 13+ you can generate a UUID with the built-in function gen_random_uuid() — no extension needed:

CREATE TABLE order_item (
  id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id   UUID        NOT NULL,
  product    VARCHAR(100),
  quantity   INT
);

-- UUID is generated automatically on insert
INSERT INTO order_item (order_id, product, quantity)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Laptop', 1);

-- You can also supply your own UUID explicitly
INSERT INTO order_item (id, order_id, product, quantity)
VALUES (gen_random_uuid(), gen_random_uuid(), 'Mouse', 2);

In earlier versions, enable the pgcrypto extension first:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- then use gen_random_uuid() in the same way

As a rule of thumb: use IDENTITY (integer) for most tables, and consider UUID when you need globally unique IDs across systems or want to hide row counts from users.

Under the hood, SERIAL and IDENTITY both use a sequence — a database object that generates a series of unique numbers. You can also create and use sequences directly:

CREATE SEQUENCE order_seq START 1000 INCREMENT 10;

SELECT nextval('order_seq');      -- returns the next value
SELECT currval('order_seq');      -- returns the current value
SELECT setval('order_seq', 500);  -- set the current value manually

Sequences are independent of tables, so the same sequence can serve multiple tables or be reset without affecting existing data.

In PostgreSQL, DDL statements (CREATE TABLE, DROP TABLE, ALTER TABLE, etc.) can be included inside a transaction and rolled back if something goes wrong.

BEGIN;

CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_table (name) VALUES ('Alice');

ROLLBACK;  -- both the table and the row are gone

In MySQL, DDL statements cause an implicit commit and cannot be rolled back. PostgreSQL DDL transactions make schema migrations significantly safer.

PostgreSQL supports a RETURNING clause on INSERT, UPDATE, and DELETE statements. It returns the affected rows immediately, removing the need for a separate SELECT.

-- Get the generated id after insert
INSERT INTO person (name) VALUES ('Alice') RETURNING id;

-- Get the updated row after update
UPDATE person SET name = 'Bob' WHERE id = 1 RETURNING id, name;

-- Get the deleted row
DELETE FROM person WHERE id = 1 RETURNING *;

MySQL has no equivalent; you must use LAST_INSERT_ID() or run a separate query.

In MySQL, LIKE is case-insensitive by default for most collations. In PostgreSQL, LIKE is case-sensitive. Use ILIKE for case-insensitive matching:

-- case-sensitive: matches 'alice', not 'Alice'
SELECT * FROM person WHERE name LIKE 'ali%';

-- case-insensitive: matches 'alice', 'Alice', 'ALICE'
SELECT * FROM person WHERE name ILIKE 'ali%';

In PostgreSQL, unquoted identifiers (table names, column names) are automatically folded to lowercase. Quoting an identifier preserves its case but makes it case-sensitive.

In the example below, ... stands for omitted column definitions.

CREATE TABLE Person (...);  -- table name stored as: person

SELECT * FROM person;    -- works
SELECT * FROM Person;    -- works (folded to lowercase)
SELECT * FROM "Person";  -- ERROR: relation "Person" does not exist

The safe rule: always use lowercase identifiers and avoid quoting them. If you migrate a MySQL schema with mixed-case names, rename them to lowercase in PostgreSQL.

The most common PostgreSQL client for Node.js is node-postgres (pg). Install it with:

npm install pg

Connect using a connection string and a Pool, which manages multiple simultaneous connections efficiently:

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: 'postgresql://libuser:libpass@localhost:5432/library',
});

async function getBooks() {
  const res = await pool.query('SELECT * FROM book WHERE author = $1', ['Tolkien']);
  return res.rows;
}

Note that PostgreSQL uses $1, $2, ... as parameter placeholders, unlike MySQL which uses ?.

When connecting to a remote server, enable SSL by adding an ssl option. In most cloud environments the server certificate is signed by a trusted CA, so you only need:

const pool = new Pool({
  connectionString: 'postgresql://libuser:libpass@db.example.com:5432/library',
  ssl: { rejectUnauthorized: true },
});

If you are connecting to a server with a self-signed certificate (common in development), set rejectUnauthorized: false — but never use this setting in production.

First, create a table for the examples:

CREATE TABLE person (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

This procedure takes a name as an input parameter and inserts a new row:

CREATE OR REPLACE PROCEDURE add_person(IN p_name VARCHAR(100))
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO person (name) VALUES (p_name);
END;
$$;

CALL add_person('Alice');
CALL add_person('Bob');

SELECT * FROM person ORDER BY id;
-- With a newly created table: 1 | Alice, 2 | Bob

• IN marks an input parameter; it is also the default parameter mode. The prefix p_ helps distinguish parameter names from column names.
• LANGUAGE plpgsql specifies the language used for the procedure body.
• $$ encloses the body, so single quotes inside it do not need to be doubled. No MySQL-style DELIMITER command is needed.
• BEGIN ... END; groups the statements in the procedure body; it does not start a transaction.

This procedure uses two input parameters to update a person's name. PL/pgSQL sets FOUND after the update; if no row matched, the procedure raises an error.

CREATE OR REPLACE PROCEDURE rename_person(
    IN p_id INTEGER,
    IN p_name VARCHAR(100)
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE person
    SET name = p_name
    WHERE id = p_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'No person with id %', p_id;
    END IF;
END;
$$;

CALL rename_person(1, 'Alice Smith');
SELECT * FROM person WHERE id = 1;
-- 1 | Alice Smith

-- Run separately to try the error case:
CALL rename_person(-1, 'Unknown');
-- ERROR: No person with id -1

A procedure is called with CALL, rather than used as an expression in a SELECT. Use a function when you need a routine that returns a value for use in a query.

Include the input parameter types when identifying the procedure to remove. You can clean up the examples with:

DROP PROCEDURE add_person(VARCHAR);
DROP PROCEDURE rename_person(INTEGER, VARCHAR);
DROP TABLE person;

Every PostgreSQL database contains a default schema called public. If no schema is explicitly specified when creating or querying objects, PostgreSQL uses this schema by default.

You can create your own schema with the following SQL command:

CREATE SCHEMA libschema;

When creating objects inside a specific schema, you can prefix the schema name:

CREATE TABLE libschema.book (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  author VARCHAR(255),
  isbn VARCHAR(255)
);

To make a schema the default for object lookup, you can set the search path in your session:

SET search_path TO libschema;

After setting this, you can reference tables and other objects without specifying the schema name.

• Organize database objects more clearly
• Support multi-tenancy within a single database
• Control access and permissions at schema level
• Avoid naming conflicts between applications

In psql, you can list all schemas using:

\dn

The default administrator is postgres. You have to first login with that user and then you can create new users and give privileges to them.

Example: Create a new user libuser and give privileges to all tables in database library to that user.

CREATE ROLE libuser WITH LOGIN PASSWORD 'libpass';

1. Set the default database with the command

   \c library
   

2. Add privileges to public schema with the command

   grant ALL ON SCHEMA public to libuser;
   

3. Add privileges to all tables with the command

   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO libuser;
   

4. Create the schema named libschema and assign libuser as its owner

   CREATE SCHEMA libschema AUTHORIZATION libuser;