SQL Server to PostgreSQL: Week 2 Migration Lab
On this page
The order system migration lab
This lab uses everything from Week 2. Try it yourself first, in training_db, then compare with the walkthrough.
Here's the SQL Server source:
-- T-SQL
CREATE TABLE dbo.Customers (
CustomerId INT IDENTITY PRIMARY KEY,
CustomerName NVARCHAR(100) NOT NULL,
Email NVARCHAR(150) NOT NULL,
IsActive BIT NOT NULL DEFAULT 1
);
CREATE TABLE dbo.Orders (
OrderId INT IDENTITY PRIMARY KEY,
CustomerId INT NOT NULL REFERENCES dbo.Customers(CustomerId),
OrderDate DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
OrderTotal DECIMAL(12,2) NOT NULL DEFAULT 0,
Status VARCHAR(20) NOT NULL DEFAULT 'OPEN'
);
CREATE TABLE dbo.OrderStatusLog (
LogId INT IDENTITY PRIMARY KEY,
OrderId INT NOT NULL,
OldStatus VARCHAR(20),
NewStatus VARCHAR(20),
ChangedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
CREATE PROCEDURE dbo.usp_PlaceOrder
@CustomerId INT, @OrderTotal DECIMAL(12,2), @NewOrderId INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerId = @CustomerId AND IsActive = 1)
BEGIN
RAISERROR('Customer not found or inactive.', 16, 1);
RETURN;
END
INSERT INTO dbo.Orders (CustomerId, OrderTotal) VALUES (@CustomerId, @OrderTotal);
SET @NewOrderId = SCOPE_IDENTITY();
END;
GO
CREATE TRIGGER trg_Orders_StatusChange ON dbo.Orders AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.OrderStatusLog (OrderId, OldStatus, NewStatus)
SELECT i.OrderId, d.Status, i.Status
FROM inserted i JOIN deleted d ON i.OrderId = d.OrderId
WHERE i.Status <> d.Status;
END;Your tasks:
- Create the three tables in PostgreSQL.
- Load three customers (one inactive) and two orders, and make sure new rows get correct ids afterwards.
- Convert the procedure. Decide between a function and a procedure, and say why.
- Convert the trigger, and test that changing an order's status writes a log row.
Step 1: the schema
CREATE TABLE customers (
customer_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_name varchar(100) NOT NULL,
email varchar(150) NOT NULL,
is_active boolean NOT NULL DEFAULT true
);
CREATE TABLE orders (
order_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers,
order_date timestamptz NOT NULL DEFAULT now(),
order_total numeric(12,2) NOT NULL DEFAULT 0,
status varchar(20) NOT NULL DEFAULT 'OPEN'
);
CREATE TABLE order_status_log (
log_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id integer NOT NULL,
old_status varchar(20),
new_status varchar(20),
changed_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (customer_id);Why BY DEFAULT on customers and orders? The migrated data arrives with its original ids, and BY DEFAULT accepts them without OVERRIDING SYSTEM VALUE. Once migration is finished you could switch to ALWAYS. SYSUTCDATETIME() becomes now() on a timestamptz, which stores the exact moment whatever the server's time zone.
Step 2: the data
INSERT INTO customers (customer_id, customer_name, email, is_active) VALUES
(1, 'Alice Nguyen', 'alice@example.com', true),
(2, 'Ben Carter', 'ben@example.com', true),
(3, 'Chidi Okafor', 'chidi@example.com', false);
INSERT INTO orders (order_id, customer_id, order_total) VALUES (1, 1, 150.00), (2, 2, 89.50);
SELECT setval(pg_get_serial_sequence('customers', 'customer_id'), (SELECT max(customer_id) FROM customers));
SELECT setval(pg_get_serial_sequence('orders', 'order_id'), (SELECT max(order_id) FROM orders));The setval lines are the step people forget (Day 13). Without them, the next order would try to use id 1 and fail with a duplicate key error.
Step 3: the procedure becomes a function
The routine returns a value and doesn't need to commit partway, so it becomes a function with an OUT parameter (Day 9):
CREATE FUNCTION place_order(p_customer_id integer, p_order_total numeric, OUT new_order_id integer)
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id AND is_active) THEN
RAISE EXCEPTION 'Customer % not found or inactive', p_customer_id;
END IF;
INSERT INTO orders (customer_id, order_total)
VALUES (p_customer_id, p_order_total)
RETURNING order_id INTO new_order_id;
END;
$$;
SELECT * FROM place_order(1, 199.99); -- new_order_id = 3
SELECT * FROM place_order(3, 50.00); -- ERROR: Customer 3 not found or inactiveWhat changed: RAISERROR ...; RETURN; became one RAISE EXCEPTION, which also stops execution. SCOPE_IDENTITY() became RETURNING ... INTO. SET NOCOUNT ON disappeared. The caller no longer declares an OUTPUT variable, because the function returns new_order_id as a column.
Step 4: the trigger
The SQL Server trigger is set-based. A row-level PostgreSQL trigger is the simplest translation here, since status changes affect a few rows at a time (Day 10):
CREATE FUNCTION log_order_status_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO order_status_log (order_id, old_status, new_status)
VALUES (NEW.order_id, OLD.status, NEW.status);
RETURN NULL;
END;
$$;
CREATE TRIGGER orders_status_change
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_order_status_change();
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1;
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1; -- no change, no log row
SELECT order_id, old_status, new_status FROM order_status_log;
-- 1 | OPEN | SHIPPEDThe WHEN clause does the job of the T-SQL WHERE i.Status <> d.Status, but before the function is even called. Using IS DISTINCT FROM also handles NULLs correctly, which <> doesn't. UPDATE OF status skips the trigger entirely for updates that don't touch the column.
In DataGrip, check the result: the trigger under the orders table's Triggers node, the function under Routines, and the log rows in a console.
What a strong answer includes
- Lowercase snake_case names,
boolean,numericandtimestamptzin place of BIT, DECIMAL and DATETIME2. - Sequences moved past the loaded ids before any new insert.
- A function, not a procedure, for
usp_PlaceOrder, with a clear reason: no transaction control is needed, and it returns a value. RETURNING ... INTOinstead of a second query for the new id.- A trigger that only logs real changes, with
WHENandIS DISTINCT FROM. - Each step verified before moving on: structure, row counts, the function's success and error paths, and the trigger.
Check yourself
You skipped moving the orders id sequence past the loaded data. The load inserted explicit ids, but the identity sequence still starts at 1, so the first new order collides. Run SELECT setval(pg_get_serial_sequence('orders', 'order_id'), max(order_id)) FROM orders; for every table loaded with explicit ids.
It doesn't commit partway, and it returns a value. A function returns new_order_id directly and can be called from SELECT, while a procedure would add nothing.
A change from NULL to a value, or back, gives NULL instead of true, so it wouldn't be logged. IS DISTINCT FROM treats NULL as a comparable value.
Run it against copies of real data for the important cases: enough stock, not enough stock, an unknown product and two concurrent orders for the last item. Compare results and side effects, such as stock levels and audit rows, with what the SQL Server procedure produced.
Use IS DISTINCT FROM: NEW.status IS DISTINCT FROM OLD.status is true when the values differ, including when one side is NULL. Plain <> returns NULL when either side is NULL, so a change to or from NULL would be missed.