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

SQL Server to PostgreSQL: Week 1 Hands-On Lab

On this page
  1. The library database lab
  2. Solution: the PostgreSQL schema
  3. Solution: the queries
  4. What a strong answer includes
  5. Check yourself

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.

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

  1. Every book with its author, newest first.
  2. Books currently on loan, with the member's name.
  3. How many books each author has, including authors with none.
  4. The average loan length in days, for returned loans only.

Solution: the PostgreSQL schema

Each change below applies a rule from this week:

SQL
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 boolean with true, not 1 (Day 5).
  • JSON in NVARCHAR(MAX) becomes jsonb (Day 5).
  • SYSDATETIME() and GETDATE() become now() and current_date (Day 4). joined_at uses timestamptz because 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:

SQL
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

SQL
-- 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, jsonb and 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.