SQL Server vs PostgreSQL DDL and Schema Design
On this page
DDL differences between SQL Server and PostgreSQL
Tables, keys, constraints and defaults mean the same thing in both databases. The differences are in the details, and they show up the first time you run a SQL Server DDL script against PostgreSQL. This lesson covers those details, then builds the small schema you'll use in tomorrow's lab.
CREATE TABLE, side by side
| Feature | T-SQL | PostgreSQL |
|---|---|---|
| Default schema | dbo.products | public.products, or just products |
| Create only if missing | IF OBJECT_ID('dbo.products') IS NULL ... | CREATE TABLE IF NOT EXISTS products (...) |
| Computed column | total AS (qty * price) | total numeric GENERATED ALWAYS AS (qty * price) |
| Persisted computed column | ... AS (qty * price) PERSISTED | ... GENERATED ALWAYS AS (qty * price) STORED |
| Table description | sp_addextendedproperty | COMMENT ON TABLE products IS '...' |
| Add a constraint without checking existing rows | WITH NOCHECK | NOT VALID |
Computed columns map more directly than they used to. Until PostgreSQL 17 every generated column had to be STORED, saved on disk like SQL Server's PERSISTED. PostgreSQL 18 added virtual generated columns, computed when read, and made them the default. So GENERATED ALWAYS AS (qty * price) with no keyword now behaves like a plain SQL Server computed column. Add STORED when you'd have used PERSISTED, for example to index the column.
Constraints, and NOT VALID instead of WITH NOCHECK
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and DEFAULT work just as in SQL Server. Name them, so error messages and later migrations can refer to them:
CREATE TABLE products (
product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_name varchar(150) NOT NULL,
sku varchar(30) NOT NULL CONSTRAINT products_sku_key UNIQUE,
unit_price numeric(10,2) NOT NULL CONSTRAINT products_price_check CHECK (unit_price >= 0)
);
COMMENT ON TABLE products IS 'Catalog of sellable products';On big tables, adding a constraint means checking every existing row while holding a lock. SQL Server DBAs handle this with WITH NOCHECK, which leaves the constraint untrusted until it's checked later. PostgreSQL's version is NOT VALID followed by VALIDATE CONSTRAINT:
ALTER TABLE products ADD CONSTRAINT products_name_check CHECK (product_name <> '') NOT VALID;
ALTER TABLE products VALIDATE CONSTRAINT products_name_check;The first statement enforces the rule on new writes straight away, without scanning. The second checks existing rows later, with a lighter lock that doesn't block normal reads and writes. Unlike an untrusted SQL Server constraint, a validated PostgreSQL constraint is fully trusted afterwards.
Sequences under identity columns
An identity column (Day 4) is backed by a sequence, a separate object you can inspect and adjust. SQL Server has had standalone sequences since 2012, but most SQL Server code only uses IDENTITY, so this is worth seeing:
SELECT last_value FROM products_product_id_seq; -- the identity's sequence
CREATE SEQUENCE invoice_number_seq START WITH 1000; -- a standalone one
SELECT nextval('invoice_number_seq'); -- 1000You'll use this mostly after loading data with explicit ids. The sequence doesn't move when you insert ids yourself, so you have to move it past the highest loaded value, or the next insert collides. Day 13 covers that step in the migration process.
Schemas and search_path
In SQL Server most objects live in dbo, and an unqualified name looks in your default schema and then in dbo. PostgreSQL uses a configurable, ordered list called search_path:
SHOW search_path; -- "$user", public
CREATE SCHEMA reporting;
SET search_path TO reporting, public; -- this session onlyWhen you write orders, PostgreSQL checks each schema in the list, in order, and uses the first orders it finds. That means the same query can resolve to different tables for different roles. Two habits avoid surprises:
- Set the path per application role, with
ALTER ROLE app_user SET search_path TO sales, public;. - Always schema-qualify names in DDL and migration scripts, as in
CREATE TABLE sales.orders.
If you're merging several SQL Server databases into one PostgreSQL database (Day 1), each becomes a schema, and search_path decides which one an application sees by default.
The sample schema for Day 7
Here's a small order-processing schema in PostgreSQL. Run it in training_db. Tomorrow's lab loads and queries it.
CREATE TABLE customers (
customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_name varchar(150) NOT NULL,
email varchar(150) NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers,
order_date timestamptz NOT NULL DEFAULT now(),
status varchar(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'shipped', 'cancelled'))
);
CREATE TABLE order_items (
order_item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES products,
quantity integer NOT NULL CHECK (quantity > 0),
line_price numeric(12,2) NOT NULL
);
CREATE INDEX ON orders (customer_id);
CREATE INDEX ON order_items (order_id);
CREATE INDEX ON order_items (product_id);
Notice the last three lines. SQL Server doesn't create indexes on foreign key columns automatically, and neither does PostgreSQL. Joins and cascading deletes need them, so add them yourself, exactly as you would in SQL Server.
Check yourself
full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name). On PostgreSQL 18 that's virtual by default, like the original. Add STORED if you need to index it.
Add it with NOT VALID, which enforces it on new rows immediately, then run ALTER TABLE ... VALIDATE CONSTRAINT later to check existing rows under a lighter lock.
The search_path of the role or session running it. An unqualified orders resolves to the first schema in the path that contains it. Qualify the name, or fix the role's search_path.
Both add a constraint without checking existing rows. In PostgreSQL you then run ALTER TABLE ... VALIDATE CONSTRAINT later, which checks old rows under a lighter lock and marks the constraint as trusted. Unlike an untrusted SQL Server constraint, a validated one can be relied on by the planner.
The sequence belongs to the identity column, so it's renamed and dropped along with it. That's simpler than serial, where a separately named sequence could be left behind or shared by mistake. Use GENERATED ... AS IDENTITY for migrated IDENTITY columns.