Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 1 · Lesson 6 · 5 min read

SQL Server vs PostgreSQL DDL and Schema Design

On this page
  1. DDL differences between SQL Server and PostgreSQL
  2. CREATE TABLE, side by side
  3. Constraints, and NOT VALID instead of WITH NOCHECK
  4. Sequences under identity columns
  5. Schemas and search_path
  6. The sample schema for Day 7
  7. Check yourself

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

FeatureT-SQLPostgreSQL
Default schemadbo.productspublic.products, or just products
Create only if missingIF OBJECT_ID('dbo.products') IS NULL ...CREATE TABLE IF NOT EXISTS products (...)
Computed columntotal AS (qty * price)total numeric GENERATED ALWAYS AS (qty * price)
Persisted computed column... AS (qty * price) PERSISTED... GENERATED ALWAYS AS (qty * price) STORED
Table descriptionsp_addextendedpropertyCOMMENT ON TABLE products IS '...'
Add a constraint without checking existing rowsWITH NOCHECKNOT 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:

SQL
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:

SQL
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:

SQL
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');                    -- 1000

You'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:

SQL
SHOW search_path;                          -- "$user", public
CREATE SCHEMA reporting;
SET search_path TO reporting, public;      -- this session only

When 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.

SQL
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);
The sample schema as DataGrip's diagram tool draws it. Arrows point from the referenced table to the table holding the foreign key.
The sample schema as DataGrip's diagram tool draws it. Arrows point from the referenced table to the table holding the foreign key.

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.