Skip to content

SQLite vs. PostgreSQL

SQLite and PostgreSQL both speak SQL, and most queries you wrote in HW1 run unchanged on either one. The difference is in how seriously each one takes a column's type. In SQLite, a type is a suggestion (called type affinity), and almost any value can go in almost any column. In PostgreSQL, a type is a rule, and a value that does not fit is an error.

For this activity, open two windows side by side:

  • DB Browser for SQLite with a new, empty database (File > New Database).
  • pgAdmin with the Query Tool open on the sec1 database. Anything you create there goes in your own schema.

One person runs each statement in SQLite while another runs it in PostgreSQL, and everyone predicts the result before anyone presses Run. In both tools, highlight one statement and press F5 to run only that statement.

Part 1: Predict, then run

Create the same test table in both databases:

CREATE TABLE t (n integer, x real, s varchar(5), d date, b boolean);

Round 1: Which inserts succeed?

Run each statement separately. For each one, write down whether it succeeds in SQLite, in PostgreSQL, or both. Then run SELECT * FROM t; in each database and compare what was actually stored.

INSERT INTO t VALUES ('abc', 3.5, 'hi', '2026-09-29', true);
INSERT INTO t VALUES (1, 3.5, 'too long for five', '2026-09-29', true);
INSERT INTO t VALUES (1, 3.5, 'hi', '2026-02-30', true);
INSERT INTO t VALUES (1, 3.5, 'hi', '2026-09-29', 'yes');
INSERT INTO t VALUES (1, 3.5, 'hi', '2026-09-29', 1);
INSERT INTO t VALUES ('7', '3.5', 42, '2026-09-29', true);

In SQLite, typeof(n) tells you how each value was actually stored:

SELECT n, typeof(n), d, typeof(d), b, typeof(b) FROM t;

Round 2: What does each expression return?

These need no table. Run each one in both databases, and put a check next to any result that surprises you.

SELECT 7 / 2;
SELECT 0.1 + 0.2 = 0.3;
SELECT 1234567.89 * 1.0;         -- SQLite
SELECT 1234567.89::real;         -- PostgreSQL
SELECT '2026-09-29' + 1;
SELECT date('2026-09-29', '+1 day');   -- SQLite
SELECT date '2026-09-29' + 1;          -- PostgreSQL
SELECT 'Duke' LIKE 'duke';

Round 3: Keys and references

Run these in both databases, one statement at a time.

CREATE TABLE p (id integer PRIMARY KEY, name text);
INSERT INTO p (name) VALUES ('Duke Dog');
CREATE TABLE c (id integer PRIMARY KEY, p_id integer REFERENCES p);
INSERT INTO c (id, p_id) VALUES (1, 99);
  1. Why does the first INSERT fail in PostgreSQL but not in SQLite?
  2. Why does the second INSERT fail in PostgreSQL? Did it fail in DB Browser? (Look at the Edit Pragmas tab, then look at the connect() function in your hw2.py.)

Now drop c and p in PostgreSQL, and recreate p with this key instead:

id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
  1. Run the first INSERT again with RETURNING id added to the end. What do you get back?
  2. What happens if you try to insert your own value into id?
Summary: what you saw
Question SQLite PostgreSQL
'abc' in an integer column stored as text error: invalid input syntax
varchar(5) limit ignored error: value too long
date column stored as text, so '2026-02-30' is fine real date type; February 30 is an error
boolean column no such type; true is the integer 1 real type; accepts true, 'yes', 't', but not 1
real 8 bytes (same as Python float) 4 bytes, only about 6 digits; use double precision or numeric
0.1 + 0.2 = 0.3 false (floating point) true (a literal like 0.1 is numeric)
'2026-09-29' + 1 2027 (the text is read as the number 2026) error; dates need the date type to do arithmetic
LIKE case insensitive (for ASCII) case sensitive; use ILIKE
integer PRIMARY KEY generates the next id automatically just a key; use GENERATED ALWAYS AS IDENTITY
Foreign keys off unless PRAGMA foreign_keys = ON (DB Browser turns it on; Python's sqlite3 does not) always enforced

Both databases agree that 7 / 2 is 3, since integer divided by integer is integer division.

Part 2: Port your HW2 tables

Open your hw2.py and copy the two CREATE TABLE statements and the INSERT statement from insert_sample(). Paste them into the pgAdmin Query Tool, and run them in sec1.

  1. Fix whatever PostgreSQL rejects. The id column is the likely first error; change it to GENERATED ALWAYS AS IDENTITY.
  2. Then make the tables better, now that the types mean something. For every column, ask whether the type is the right one, not just one that works:
    • Is your real column money, a grade, or a measurement? Money and grades want numeric(p, s); measurements can be double precision.
    • Is any text column really a date, a time, or a yes/no value?
    • Is any integer column really a boolean?
  3. Add at least one CHECK constraint that SQLite would have let you skip, such as a price that cannot be negative. Insert a row that violates it and read the error message.

When everything runs cleanly, save the statements in a file named hw2_pg.sql. Put DROP TABLE IF EXISTS statements at the top, in the right order, so the whole file can run again from scratch.

Keep this file

On Thursday you will write a Python program that runs these CREATE TABLE statements and fills your tables with fake data. There is nothing to submit today.