Beginner PostgreSQL Interview Questions and Answers

Core PostgreSQL (often called Postgres) is a powerful open-source, object-relational database management system (ORDBMS) with over 35 years of active development. It is known for its standards compliance, reliability, and extensibility.

What sets PostgreSQL apart:

• ACID compliance — full support for Atomicity, Consistency, Isolation, and Durability even across complex multi-table operations
• Rich data types — arrays, JSON/JSONB, hstore, geometric types, network addresses, full-text search, UUID, ranges, and more
• Extensibility — create custom data types, operators, index methods, and procedural languages (PL/pgSQL, PL/Python, PL/v8)
• Advanced indexing — B-tree, Hash, GiST, GIN, BRIN, and partial indexes
• Standards compliant — closely follows SQL:2016 standard; supports CTEs, window functions, lateral joins
• MVCC — Multi-Version Concurrency Control enables high read/write concurrency without locking
• Free and open source — no licensing costs; backed by a vibrant community

Data Types

Numeric:

• SMALLINT (2 bytes), INTEGER / INT (4 bytes), BIGINT (8 bytes)
• DECIMAL / NUMERIC(p,s) — exact precision (financial data)
• REAL (4-byte float), DOUBLE PRECISION (8-byte float)
• SERIAL, BIGSERIAL — auto-incrementing integers (legacy; prefer GENERATED ALWAYS AS IDENTITY)

Character:

• CHAR(n) — fixed-length, padded with spaces
• VARCHAR(n) — variable-length with limit
• TEXT — unlimited variable-length (preferred in PostgreSQL)

Date/Time: DATE, TIME, TIMESTAMP, TIMESTAMPTZ (with time zone), INTERVAL

Boolean: BOOLEAN — TRUE / FALSE / NULL

Special PostgreSQL types:

• UUID, JSON, JSONB, ARRAY, BYTEA, INET, CIDR, TSVECTOR (full-text search)

CREATE TABLE products (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        TEXT NOT NULL,
  price       NUMERIC(10,2) NOT NULL,
  tags        TEXT[],             -- array
  metadata    JSONB,              -- binary JSON
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

Schema Constraints enforce data integrity rules at the database level — ensuring invalid data is never stored regardless of the application layer.

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- PRIMARY KEY
  order_no    TEXT UNIQUE NOT NULL,                              -- UNIQUE + NOT NULL
  customer_id BIGINT NOT NULL REFERENCES customers(id)          -- FOREIGN KEY
                ON DELETE RESTRICT ON UPDATE CASCADE,
  status      TEXT NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','processing','shipped','delivered','cancelled')), -- CHECK
  quantity    INT NOT NULL CHECK (quantity > 0),
  total       NUMERIC(10,2) NOT NULL CHECK (total >= 0),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

• PRIMARY KEY — uniquely identifies each row; implies NOT NULL + UNIQUE; table can have only one
• UNIQUE — no two rows can have the same value(s) in the constrained column(s); NULLs are considered distinct
• NOT NULL — column must always have a value
• CHECK — row must satisfy a boolean expression
• FOREIGN KEY — value must exist in the referenced table; enforces referential integrity
• DEFAULT — not strictly a constraint but provides a default value when none is specified
• EXCLUSION — advanced; ensures no two rows satisfy a specified operator combination (e.g., no overlapping date ranges)

SQL

• WHERE — filters rows before grouping and aggregation. Cannot reference aggregate functions (SUM, COUNT, etc.).
• HAVING — filters after grouping. Can reference aggregate functions. Used together with GROUP BY.

-- Get departments where the average salary exceeds £60,000,
-- considering only active employees

SELECT
    department,
    COUNT(*)         AS employee_count,
    AVG(salary)      AS avg_salary
FROM employees
WHERE  status = 'active'          -- filter rows BEFORE grouping
GROUP BY department
HAVING AVG(salary) > 60000        -- filter groups AFTER aggregation
ORDER BY avg_salary DESC;

Order of SQL clause execution: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This is why WHERE cannot use aliases defined in SELECT, but ORDER BY can.

SQL

-- Sample tables: orders (o) and customers (c)

-- INNER JOIN — only rows with a match in BOTH tables
SELECT o.id, c.name FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN (LEFT OUTER JOIN) — all rows from LEFT table + matching from right (NULL if no match)
SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Customers without orders still appear (o.id is NULL)

-- RIGHT JOIN — all rows from RIGHT table + matching from left
SELECT c.name, o.id FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;

-- FULL OUTER JOIN — all rows from BOTH tables (NULL where no match)
SELECT c.name, o.id FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

-- CROSS JOIN — every row from left × every row from right (Cartesian product)
SELECT c.name, p.name FROM customers c
CROSS JOIN products p;

-- SELF JOIN — join a table to itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Schema

• PRIMARY KEY — uniquely identifies each row. Automatically creates a unique B-tree index. The column(s) cannot be NULL. A table can have only one primary key (though it can be composite).
• UNIQUE — enforces uniqueness. Also creates a unique index. The column can be NULL (and multiple NULL values are allowed — NULLs are not considered equal). A table can have many unique constraints.

CREATE TABLE users (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- one PK only
  email    TEXT UNIQUE NOT NULL,   -- unique but not the PK
  username TEXT UNIQUE NOT NULL,   -- multiple UNIQUE constraints are fine
  phone    TEXT UNIQUE             -- nullable unique (multiple NULLs allowed)
);

-- Composite primary key (junction table)
CREATE TABLE order_items (
  order_id   BIGINT REFERENCES orders(id),
  product_id BIGINT REFERENCES products(id),
  quantity   INT NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id)  -- composite PK
);

SQL Aggregate functions compute a single result from a set of rows.

SELECT
    COUNT(*)                AS total_rows,       -- count all rows incl. NULLs
    COUNT(email)            AS rows_with_email,  -- count non-NULL values only
    COUNT(DISTINCT city)    AS unique_cities,    -- distinct count
    SUM(salary)             AS total_salary,
    AVG(salary)             AS mean_salary,
    MIN(salary)             AS lowest_salary,
    MAX(salary)             AS highest_salary,
    STDDEV(salary)          AS salary_stddev,
    VARIANCE(salary)        AS salary_variance,

    -- String aggregation
    STRING_AGG(name, ', ' ORDER BY name)  AS names_list,

    -- Array aggregation
    ARRAY_AGG(name ORDER BY name)         AS names_array,

    -- JSON aggregation
    JSON_AGG(row_to_json(employees.*))    AS employees_json,
    JSONB_OBJECT_AGG(name, salary)        AS salary_map,

    -- Boolean aggregation
    BOOL_AND(is_active)                   AS all_active,
    BOOL_OR(is_active)                    AS any_active

FROM employees
WHERE department = 'Engineering';

Transactions A transaction is a sequence of SQL statements that execute as a single logical unit. Either all statements succeed (COMMIT) or none of them take effect (ROLLBACK).

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- credit

-- If both succeed:
COMMIT;

-- If anything goes wrong (e.g., insufficient funds):
ROLLBACK;  -- both updates are undone

ACID properties:

• A — Atomicity — all operations in the transaction complete, or none do. No partial updates.
• C — Consistency — the database moves from one valid state to another; all constraints and rules are respected.
• I — Isolation — concurrent transactions do not interfere with each other. Each transaction sees a consistent snapshot.
• D — Durability — once committed, the changes survive even a system crash (written to the WAL and then to disk).

Indexes An index is a separate data structure that allows PostgreSQL to find rows matching a condition quickly — without scanning every row in the table (sequential scan). The trade-off: faster reads, slightly slower writes (index must be updated on every INSERT/UPDATE/DELETE).

-- Default index type: B-tree (balanced tree)
CREATE INDEX idx_users_email ON users (email);

-- Unique index (also enforces uniqueness like a UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_username ON users (username);

-- Composite index — covers queries filtering on both columns
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

-- Index with condition (partial index) — smaller, faster for selective queries
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- Drop an index
DROP INDEX idx_users_email;

-- Rebuild index without locking table (production-safe)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- Check if a query uses an index
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

PostgreSQL automatically creates a B-tree index for PRIMARY KEY and UNIQUE constraints. You do not need to create them manually.

SQL

• DELETE — removes specific rows matching a WHERE clause (or all rows if no WHERE). Fires row-level triggers. Can be rolled back in a transaction. Slow on large tables (row-by-row). Does NOT reset sequences.
• TRUNCATE — removes all rows from a table instantly. No row-level triggers. Can be rolled back (PostgreSQL’s TRUNCATE is transactional). Much faster than DELETE for full-table clears. Optionally resets sequences (RESTART IDENTITY).
• DROP TABLE — removes the entire table structure (columns, indexes, constraints, triggers) and all data permanently. Cannot be undone without a backup.

-- DELETE — specific rows, slow, triggers fire, sequence unchanged
DELETE FROM orders WHERE status = 'cancelled';

-- TRUNCATE — all rows, fast, no row triggers, optional sequence reset
TRUNCATE orders;
TRUNCATE orders RESTART IDENTITY CASCADE;  -- reset serial; cascade to FKs

-- DROP — entire table gone
DROP TABLE orders;
DROP TABLE IF EXISTS orders;               -- no error if table doesn't exist
DROP TABLE orders CASCADE;                 -- also drops dependent objects

SQL A subquery is a query nested inside another query. A correlated subquery references a column from the outer query — it re-evaluates for every row of the outer query.

-- Uncorrelated subquery — executes once
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM clause (derived table / inline view)
SELECT dept, avg_sal FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY department
) AS dept_stats
WHERE avg_sal > 70000;

-- Subquery in IN clause
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'pending');

-- Correlated subquery — references outer query's row (re-runs per row, slower)
SELECT e.name, e.salary FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary) FROM employees e2
    WHERE e2.department = e.department  -- references outer e.department
);

-- EXISTS — more efficient than IN for correlated checks
SELECT c.name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Objects A view is a named, saved SQL query stored in the database. It acts like a virtual table — you query it like a table, but the data comes from the underlying query executed at query time.

-- Create a view
CREATE VIEW active_orders AS
SELECT
    o.id,
    o.order_no,
    c.name  AS customer_name,
    o.total,
    o.created_at
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status NOT IN ('cancelled', 'delivered');

-- Query the view exactly like a table
SELECT * FROM active_orders WHERE total > 100;

-- Update / replace a view
CREATE OR REPLACE VIEW active_orders AS ...;

-- Drop a view
DROP VIEW active_orders;

-- Updatable views — simple single-table views can support INSERT/UPDATE/DELETE
-- For complex views, use INSTEAD OF triggers or rewrite rules

Use cases:

• Simplify complex queries — hide JOINs and business logic behind a clean interface
• Security — expose only specific columns to certain users (column-level access control)
• Abstraction — underlying table structure can change; view contract stays stable
• Reporting — named, reusable query logic for dashboards and reports

Objects A sequence is a database object that generates a series of unique integers — used for auto-incrementing primary keys.

-- Modern approach: GENERATED ALWAYS AS IDENTITY (SQL standard, preferred)
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- Legacy: SERIAL (shorthand that creates a sequence automatically)
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY  -- equivalent to BIGINT with auto-sequence
);

-- Manual sequence creation (full control)
CREATE SEQUENCE order_seq
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1000
  MAXVALUE 9999999
  CYCLE;                    -- wrap around at MAXVALUE

-- Use the sequence
INSERT INTO orders (order_no) VALUES ('ORD-' || nextval('order_seq'));
SELECT currval('order_seq'); -- current value in this session
SELECT lastval();            -- last value generated in this session

-- Reset a sequence
ALTER SEQUENCE order_seq RESTART WITH 1;

-- Key difference:
-- SERIAL: creates a sequence owned by the column; dropping column drops sequence
-- GENERATED IDENTITY: SQL standard, more explicit, harder to accidentally bypass
-- Manual SEQUENCE: full control, can be shared across tables

Tooling psql is the official interactive terminal for PostgreSQL. It supports running SQL statements, administrative commands, and scripting.

# Connect to a database
psql -U postgres -d mydb
psql "postgresql://user:password@host:5432/mydb"

# Inside psql — meta-commands (start with backslash)
\l              -- list all databases
\c mydb         -- connect to database
\dt             -- list tables in current schema
\dt *.*         -- list all tables in all schemas
\d users        -- describe table structure (columns, types, constraints, indexes)
\di             -- list indexes
\dv             -- list views
\df             -- list functions
\dn             -- list schemas
\du             -- list users/roles
\dp             -- list privileges
\x              -- toggle expanded display (vertical format, great for wide rows)
\timing         -- toggle query execution time display
\e              -- open SQL editor in $EDITOR
\i file.sql     -- execute SQL from a file
\o output.txt   -- send query results to file
\copy           -- client-side COPY (works without superuser privileges)
\q              -- quit

# Run a query without entering interactive mode
psql -U postgres -d mydb -c "SELECT COUNT(*) FROM users;"

# Execute a SQL file
psql -U postgres -d mydb -f migration.sql

Data Types

• CHAR(n) — fixed length. Always stores exactly n characters, padding with spaces if shorter. Trailing spaces are ignored in comparisons. Rarely useful; wastes space for shorter values.
• VARCHAR(n) — variable length with a maximum of n characters. Raises an error if the value exceeds n. Useful when you want to enforce a maximum length.
• TEXT — variable length with no limit. PostgreSQL-specific but widely used. Semantically equivalent to VARCHAR without a limit.

-- In PostgreSQL, TEXT and VARCHAR have the same underlying storage
-- There is NO performance difference between TEXT and VARCHAR(n)
-- The only difference is VARCHAR(n) enforces the length limit

-- PostgreSQL recommendation: prefer TEXT unless you need the length constraint
-- Use VARCHAR(n) only when the database should enforce a maximum (e.g., ISO codes)

CREATE TABLE example (
  country_code  CHAR(2),        -- always 2 chars: 'GB', 'US'
  postal_code   VARCHAR(10),    -- max 10 chars
  description   TEXT            -- unlimited, no constraint
);

Rule of thumb: Use TEXT for all general-purpose string columns in PostgreSQL. Use VARCHAR(n) only when you need the database to enforce a maximum length as a business rule. Avoid CHAR(n) — it almost never adds value.

SQL NULL represents an unknown or missing value — it is not zero, empty string, or false. NULL is contagious: any arithmetic or comparison involving NULL yields NULL (unknown).

-- NULL comparisons — ALWAYS use IS NULL / IS NOT NULL
SELECT * FROM users WHERE phone IS NULL;     -- ✅ correct
SELECT * FROM users WHERE phone = NULL;      -- ❌ ALWAYS returns 0 rows!

-- NULL arithmetic
SELECT NULL + 5;    -- NULL
SELECT NULL = NULL; -- NULL (not TRUE!)
SELECT NULL <> NULL; -- NULL

-- IS DISTINCT FROM — NULL-safe equality comparison
SELECT NULL IS DISTINCT FROM NULL;     -- FALSE (they are the same "nothing")
SELECT 1 IS DISTINCT FROM NULL;        -- TRUE
SELECT 1 IS NOT DISTINCT FROM 1;       -- TRUE

-- COALESCE — return first non-NULL value
SELECT COALESCE(phone, mobile, 'N/A') FROM users;

-- NULLIF — return NULL if two values are equal (avoid division by zero)
SELECT total / NULLIF(quantity, 0) FROM orders;

-- NULL in aggregates
SELECT COUNT(*)     FROM t; -- counts all rows including NULLs
SELECT COUNT(phone) FROM t; -- counts only non-NULL values
SELECT AVG(salary)  FROM t; -- ignores NULL rows

-- ORDER BY: NULLs sort LAST in ASC (default), FIRST in DESC
SELECT * FROM users ORDER BY phone ASC NULLS FIRST; -- control placement

Architecture

• Database — the top-level container. Each database has its own set of schemas, users, and data. Connections are made to a specific database. Cross-database queries are not possible in standard PostgreSQL.
• Schema — a namespace within a database. Organises objects (tables, views, functions, sequences) into logical groups. Multiple schemas can exist in one database. The default schema is public.

-- Create a schema
CREATE SCHEMA billing;
CREATE SCHEMA IF NOT EXISTS analytics;

-- Objects in a schema (namespace)
CREATE TABLE billing.invoices (id BIGINT, amount NUMERIC);
CREATE TABLE billing.payments (id BIGINT, invoice_id BIGINT);

-- Query with schema prefix
SELECT * FROM billing.invoices;

-- search_path — controls which schemas are searched when no prefix given
SET search_path TO billing, public;
SELECT * FROM invoices;  -- finds billing.invoices first

-- Common patterns:
-- public schema: shared objects
-- app schema: application tables
-- analytics schema: reporting views and aggregates
-- staging schema: ETL temporary tables

-- Grant schema access to a role
GRANT USAGE ON SCHEMA billing TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA billing TO reporting_user;

Performance EXPLAIN shows the query execution plan — how PostgreSQL intends to retrieve the data. EXPLAIN ANALYZE actually executes the query and shows real timings.

-- Show plan without running the query
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Run query and show actual timings (most useful)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC LIMIT 10;

Key nodes to recognise:

• Seq Scan — reads entire table (full scan). Fine for small tables; bad for large ones without a WHERE clause.
• Index Scan — uses an index to find rows, then fetches them. Good for selective queries.
• Index Only Scan — all data is in the index; no heap fetch needed. Fastest.
• Bitmap Heap Scan — combines index + heap fetches in batches. Good for moderately selective queries.
• Hash Join / Merge Join / Nested Loop — join strategies chosen by the planner.

Key metrics: cost=start..total (estimated), actual time=start..total, rows, loops, Buffers hit/read. Large differences between estimated and actual rows indicate stale statistics — run ANALYZE table_name.

SQL

-- Tables: customers (3 rows) and orders (2 rows)
-- Alice has 2 orders, Bob has 1, Carol has 0

-- INNER JOIN — only rows with a match in BOTH tables
SELECT c.name, o.id AS order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- Result: Alice|1, Alice|2, Bob|3
-- Carol is EXCLUDED because she has no orders

-- LEFT JOIN — ALL rows from left table, NULL for unmatched right
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Result: Alice|1, Alice|2, Bob|3, Carol|NULL
-- Carol IS included with NULL for order_id

-- Common pattern: find customers WITHOUT any orders
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;  -- only rows where no match was found
-- Returns: Carol

Key rule: If you need to keep all rows from the left table regardless of whether they have a match, use LEFT JOIN. If you only want rows that match in both tables, use INNER JOIN.

Schema A foreign key ensures that a value in one table (child) references an existing value in another table (parent). This is called referential integrity.

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

-- ON DELETE options:
-- RESTRICT (default) — prevents deletion of parent if child rows exist (raises error)
-- NO ACTION — like RESTRICT but checked at end of transaction (allows deferrable FK)
-- CASCADE — automatically delete child rows when parent is deleted
-- SET NULL — set the FK column to NULL when parent is deleted
-- SET DEFAULT — set the FK column to its DEFAULT value when parent is deleted

-- ON UPDATE works the same way (usually CASCADE to propagate PK changes)

-- Deferrable foreign key — check at COMMIT time, not per-statement
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  DEFERRABLE INITIALLY DEFERRED;

-- Disable FK checks temporarily (loading bulk data)
SET session_replication_role = 'replica';  -- disables FK checks for session
-- Load data...
SET session_replication_role = 'origin';   -- re-enable

SQL

• UNION — combines results from two queries, removing duplicate rows. Performs a sort/hash operation to find duplicates — slower.
• UNION ALL — combines results and keeps ALL rows including duplicates. No deduplication — faster. Use when duplicates are impossible or acceptable.

-- UNION — removes duplicates (slower)
SELECT email FROM customers
UNION
SELECT email FROM leads;
-- If "alice@example.com" is in both tables, it appears ONCE

-- UNION ALL — keeps duplicates (faster)
SELECT email FROM customers
UNION ALL
SELECT email FROM leads;
-- If "alice@example.com" is in both, it appears TWICE

-- Rules for UNION:
-- Both queries must have the same number of columns
-- Column data types must be compatible
-- Column names in the result come from the FIRST query
-- ORDER BY applies to the entire combined result (put it at the very end)

SELECT id, name, 'customer' AS type FROM customers
UNION ALL
SELECT id, name, 'lead'     AS type FROM leads
ORDER BY name;

SQL The RETURNING clause returns values from rows affected by INSERT, UPDATE, or DELETE — eliminating the need for a separate SELECT to retrieve auto-generated values or confirm changes.

-- INSERT ... RETURNING — get the generated ID back immediately
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, created_at;

-- UPDATE ... RETURNING — see the new values after update
UPDATE products
SET price = price * 1.1, updated_at = NOW()
WHERE category = 'electronics'
RETURNING id, name, price AS new_price;

-- DELETE ... RETURNING — capture deleted rows before they're gone
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id, user_id, expires_at;

-- Use with a CTE to process returned rows
WITH deleted AS (
  DELETE FROM notifications
  WHERE read = TRUE AND created_at < NOW() - INTERVAL '30 days'
  RETURNING user_id
)
SELECT user_id, COUNT(*) AS deleted_count
FROM deleted
GROUP BY user_id;

This is a PostgreSQL-specific extension to standard SQL. It is extremely useful in ORM code, eliminating the common pattern of inserting a row and then querying it again to get the generated values.