SQL Server to PostgreSQL: Week 1 Hands-On Lab
On this page
The library database lab
Try this on your own first. It should take about an hour, and it uses every topic from Days 1 to 6.
A small library runs this schema on SQL Server. Recreate it in training_db as idiomatic PostgreSQL, load the sample data, then answer four questions with queries.
-- T-SQL (the source)
CREATE TABLE dbo.Authors (
AuthorId INT IDENTITY(1,1) PRIMARY KEY,
FullName NVARCHAR(150) NOT NULL,
Country NVARCHAR(80) NULL
);
CREATE TABLE dbo.Books (
BookId INT IDENTITY(1,1) PRIMARY KEY,
Title NVARCHAR(250) NOT NULL,
AuthorId INT NOT NULL REFERENCES dbo.Authors(AuthorId),
Isbn VARCHAR(20) NOT NULL UNIQUE,
PublishedOn DATE NULL,
IsAvailable BIT NOT NULL DEFAULT 1,
Metadata NVARCHAR(MAX) NULL -- JSON as text
);
CREATE TABLE dbo.Members (
MemberId INT IDENTITY(1,1) PRIMARY KEY,
MemberName NVARCHAR(150) NOT NULL,
Email NVARCHAR(150) NOT NULL UNIQUE,
JoinedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);
CREATE TABLE dbo.Loans (
LoanId INT IDENTITY(1,1) PRIMARY KEY,
BookId INT NOT NULL REFERENCES dbo.Books(BookId),
MemberId INT NOT NULL REFERENCES dbo.Members(MemberId),
LoanedOn DATE NOT NULL DEFAULT CAST(GETDATE() AS date),
ReturnedOn DATE NULL
);The questions:
- Every book with its author, newest first.
- Books currently on loan, with the member's name.
- How many books each author has, including authors with none.
- The average loan length in days, for returned loans only.
Solution: the PostgreSQL schema
Each change below applies a rule from this week:
CREATE TABLE authors (
author_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name varchar(150) NOT NULL,
country varchar(80)
);
CREATE TABLE books (
book_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title varchar(250) NOT NULL,
author_id integer NOT NULL REFERENCES authors,
isbn varchar(20) NOT NULL UNIQUE,
published_on date,
is_available boolean NOT NULL DEFAULT true,
metadata jsonb
);
CREATE TABLE members (
member_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
member_name varchar(150) NOT NULL,
email varchar(150) NOT NULL UNIQUE,
joined_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE loans (
loan_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
book_id integer NOT NULL REFERENCES books,
member_id integer NOT NULL REFERENCES members,
loaned_on date NOT NULL DEFAULT current_date,
returned_on date,
CHECK (returned_on IS NULL OR returned_on >= loaned_on)
);
CREATE INDEX ON books (author_id);
CREATE INDEX ON loans (book_id);
CREATE INDEX ON loans (member_id);- Names are lowercase snake_case, so nothing needs quotes (Day 4).
- IDENTITY(1,1) becomes
GENERATED ALWAYS AS IDENTITY(Day 4). - NVARCHAR becomes
varchar, since all PostgreSQL text is Unicode (Day 5). - BIT becomes
booleanwithtrue, not 1 (Day 5). - JSON in NVARCHAR(MAX) becomes
jsonb(Day 5). - SYSDATETIME() and GETDATE() become
now()andcurrent_date(Day 4).joined_atusestimestamptzbecause it records a real moment. - Foreign key indexes are added by hand, since neither database creates them (Day 6). The CHECK is an extra rule the source didn't have, and catching bad data like this is a good habit during a migration.
Now load sample data:
INSERT INTO authors (full_name, country) VALUES
('Gabriel Garcia Marquez', 'Colombia'), ('Isabel Allende', 'Chile'),
('Haruki Murakami', 'Japan'), ('Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO books (title, author_id, isbn, published_on, metadata) VALUES
('One Hundred Years of Solitude', 1, '978-0060883287', '1967-05-30', '{"genre": "novel"}'),
('Love in the Time of Cholera', 1, '978-0307389732', '1985-01-01', '{"genre": "novel"}'),
('The House of the Spirits', 2, '978-1501117015', '1982-01-01', NULL),
('Norwegian Wood', 3, '978-0375704024', '1987-08-04', '{"genre": "novel"}');
INSERT INTO members (member_name, email) VALUES
('Ana Torres', 'ana.torres@example.com'), ('Marco Bianchi', 'marco.bianchi@example.com');
INSERT INTO loans (book_id, member_id, loaned_on, returned_on) VALUES
(1, 1, current_date - 10, current_date - 3),
(2, 1, current_date - 2, NULL),
(4, 2, current_date - 20, current_date - 18);Notice current_date - 10. In PostgreSQL, a date minus a whole number is a date that many days earlier, with no DATEADD needed.
Solution: the queries
-- 1. Every book with its author, newest first
SELECT b.title, a.full_name AS author, b.published_on
FROM books b
JOIN authors a ON a.author_id = b.author_id
ORDER BY b.published_on DESC NULLS LAST;
-- 2. Books on loan right now
SELECT b.title, m.member_name, l.loaned_on
FROM loans l
JOIN books b ON b.book_id = l.book_id
JOIN members m ON m.member_id = l.member_id
WHERE l.returned_on IS NULL;
-- 3. Books per author, including authors with none
SELECT a.full_name, count(b.book_id) AS book_count
FROM authors a
LEFT JOIN books b ON b.author_id = a.author_id
GROUP BY a.author_id, a.full_name
ORDER BY book_count DESC;
-- 4. Average loan length in days
SELECT round(avg(returned_on - loaned_on), 1) AS avg_days
FROM loans
WHERE returned_on IS NOT NULL;Expected results: query 2 returns Love in the Time of Cholera for Ana Torres. Query 3 lists Chimamanda Ngozi Adichie with 0. Query 4 returns 4.5.
Three details are worth noticing. In query 1, NULLS LAST makes the NULL-handling explicit. PostgreSQL sorts NULLs first in descending order, while SQL Server sorts them last. In query 3, count(b.book_id) counts only matched books, where count(*) would give the author with no books a 1. In query 4, subtracting one date from another gives a number of days directly, replacing DATEDIFF(day, ...).
Finally, open the four tables in DataGrip's diagram view (Day 3) and check that the relationships match the DDL.
What a strong answer includes
- No quoted identifiers anywhere, and lowercase names throughout.
boolean,jsonband identity columns in place of BIT, NVARCHAR(MAX) JSON and IDENTITY.- Indexes on every foreign key column.
count(column)with the LEFT JOIN, so authors without books show 0.- An explicit NULLS FIRST or NULLS LAST where NULLs can appear in a sort.
Check yourself
You probably wrote count(*), which counts rows, and a LEFT JOIN produces one row for an author with no books, filled with NULLs. Use count(b.book_id), which counts only non-NULL values, so authors with no books correctly show 0.
PostgreSQL treats NULL as larger than any value, so NULLs come first in descending order. SQL Server treats NULL as the smallest value. Add NULLS LAST to keep the old order.
Validation, since invalid JSON is accepted, plus the jsonb operators like ->> and @> and the ability to index inside the documents with GIN.
SQL Server databases usually use a case-insensitive collation, while PostgreSQL's default comparisons are case-sensitive. Options include comparing lower(name) = lower($1) with a matching expression index, the citext type, or a nondeterministic case-insensitive ICU collation.
Compare row counts per table, then aggregate checksums such as sums of numeric columns and counts per status or date. For key tables, compare a sample of rows field by field. Automating these checks makes every rerun of the migration verifiable.