skip to content

SQL Dialect

PostgreSQL extends standard SQL with DISTINCT ON, ON CONFLICT upserts, RETURNING, COPY, identity and uuidv7 keys, exclusion constraints and partitioning. Interviewers probe the production edges.

part ofPostgreSQLread it →
on this pageshow

explore

questions

page 1 of 2

In PostgreSQL, how does COPY ... FROM '/path/file.csv' differ from COPY ... FROM STDIN, and what privilege does each one need?

level: juniorimportance: must knowfreq 70%

answer

  1. whose filesystem, whose OS account
  2. the server reads the file itself
  3. predefined roles for server files
  4. STDIN rides the client connection

basics

~20 s

COPY FROM a file name makes the PostgreSQL server open the file on the database host as its own OS user, needing superuser or pg_read_server_files; COPY FROM STDIN streams rows over the client connection and needs only INSERT.

solid answer

~50 s

`COPY` is PostgreSQL's bulk-transfer command: it moves rows between a table and a file, a program or the client connection in one statement. With a file name, the **server process** opens the file, so the path is resolved on the database host and read with the OS account the server runs as. Because that lets a role read any file that account can read, it is limited to superusers and members of `pg_read_server_files` (`pg_write_server_files` for `COPY TO` a file, `pg_execute_server_program` for `PROGRAM`); anyone else gets SQLSTATE `42501`. With `STDIN`, the client sends the data over its existing connection, so the file can live anywhere the client can reach and the role needs only `INSERT` (or column privileges) on the target table. For a census CSV that is the default choice unless the file already sits on the database host.

code

console · 4 lines
console
census=> COPY census_staging FROM '/srv/import/census_2026.csv' WITH (FORMAT csv, HEADER);
ERROR:  permission denied to COPY from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

go deeper

for a junior

Recall that COPY with a file name is read by the server on its own host, while COPY FROM STDIN takes the data from your client connection.

for a middle

Explain the privilege split: pg_read_server_files, pg_write_server_files and pg_execute_server_program for file and program forms, plain table privileges for STDIN and STDOUT, and SQLSTATE 42501 otherwise.

for a senior

Show judgement: keep server-file roles away from application logins, stream loads from the client, and remember that triggers and CHECK constraints still run and identity values are copied as given.

for a principal

Frame it as access control versus a network hop: who may touch the database host's filesystem is a security boundary, and bulk loading is not a reason to widen it.

## What COPY is `COPY` is a PostgreSQL command, not part of standard SQL, that moves rows between a table and an external stream in a single statement. `COPY ... FROM` **appends** rows to a table; `COPY ... TO` writes a table, or the result of a parenthesised query, out. It is fast because one command is parsed and planned once and then runs a tight loop that converts each field with its column type's input function and inserts the row. The first thing interviewers probe, though, is not speed but **where the data comes from**, because the two forms behave very differently. ## COPY with a file name: the server does the reading ```sql COPY census_staging FROM '/srv/import/census_2026.csv' WITH (FORMAT csv, HEADER); ``` Here the **PostgreSQL server process** serving your session opens `/srv/import/census_2026.csv` itself. That has three consequences: - The path is resolved **on the database host**, not on the machine where you typed the command. If the CSV is on your laptop and the server is elsewhere, the server simply cannot find it. - The file is opened with the **operating-system account the server runs as**, so that account needs read permission on it. - A relative path is resolved against the server process's working directory, normally the cluster's data directory; `COPY TO` a file insists on an absolute path. Because the server reads the file, a role allowed to do this can read **any file the server's account can read**: configuration files, key files, anything on the host. PostgreSQL therefore limits the file and program forms to superusers and to members of three predefined roles. ## The predefined roles | Form | Needs superuser or | Error otherwise | |---|---|---| | `COPY ... FROM 'file'` | `pg_read_server_files` | `permission denied to COPY from a file` | | `COPY ... TO 'file'` | `pg_write_server_files` | `permission denied to COPY to a file` | | `COPY ... FROM/TO PROGRAM 'cmd'` | `pg_execute_server_program` | `permission denied to COPY to or from an external program` | | `COPY ... FROM STDIN` / `TO STDOUT` | nothing beyond table privileges | none | All three errors carry SQLSTATE `42501` (`insufficient_privilege`) and a hint that anyone can copy from `STDIN` or to `STDOUT`. The manual warns that these roles **bypass database-level permission checks** on file access and could be used to gain superuser-level access, so they belong to trusted administrative roles, never to an application's login role. Watch the near-miss name: `pg_read_all_data` grants `SELECT` on every table, and nothing on the server's filesystem. ## COPY FROM STDIN: the client sends the data ```sql COPY census_staging FROM STDIN WITH (FORMAT csv, HEADER); ``` With `STDIN`, the server switches the connection into the COPY sub-protocol and waits for the client to send rows; with `STDOUT` it streams rows back. The file lives wherever the **client** can read it, and the only privilege checked is the ordinary one: `INSERT` on the target table (column privileges on the listed columns are enough) for `COPY FROM`, `SELECT` for `COPY TO`. Client libraries expose this directly, for example libpq's `PQputCopyData` and `PQputCopyEnd`, and most language drivers offer an equivalent copy API. psql's `\copy` meta-command wraps the same mechanism; its details belong to the psql topic. ## What COPY FROM does to the table either way Both forms run the same server-side insert path, so these hold wherever the bytes come from: 1. **Triggers fire and `CHECK` constraints are checked**; rewrite **rules are not invoked**. 2. Values supplied for an **identity column** are written as given, like `INSERT ... OVERRIDING SYSTEM VALUE`, so the column's sequence does not advance and needs resetting after the load. 3. `COPY FROM` is **not supported on a table with row-level security** enabled; the manual says to use `INSERT` there. 4. The target table is locked in `ROW EXCLUSIVE` mode, the same as `INSERT`, so readers are not blocked. 5. Progress is visible in `pg_stat_progress_copy`, whose `type` column reads `FILE` for a server file, `PROGRAM` for a program and `PIPE` for `STDIN` and `STDOUT`. ## Choosing between them For a census CSV produced by an export job, the practical rule is short: - If the file is **already on the database host** and an administrator runs the load, a server-side `COPY FROM 'file'` saves one network transfer. - In every other case (an application, a CI job, a managed server whose filesystem you cannot reach) use **`COPY FROM STDIN`** from the client, which needs no special role. Granting `pg_read_server_files` to an application role to save that transfer trades a performance detail for a privilege-escalation path, and is the answer interviewers hope you will not give.

  • Where does a relative file path in a PostgreSQL COPY FROM resolve, and can COPY TO use one?
    A relative path in `COPY FROM` is resolved against the server process's working directory, normally the cluster's data directory, never the client's current directory. `COPY TO` a file refuses relative paths and requires an absolute one. The manual recommends always writing absolute paths, because a relative one silently depends on where the server was started.
  • Why not grant pg_read_server_files to the application role that runs the nightly census load?
    Because that role could then read any file the server's OS account can read, including configuration and key files. The manual warns these roles bypass database permission checks and can lead to superuser-level access. An application should stream with `COPY FROM STDIN`, which needs only `INSERT` on the target table.
  • What does PostgreSQL's COPY ... FROM PROGRAM do, and what is the risk?
    The server runs the given shell command as its own OS account and reads the command's standard output as COPY input; `COPY TO PROGRAM` writes to the command's standard input. It requires `pg_execute_server_program` or superuser. The shell interprets the string, so splicing user-supplied text into it is a command-injection hole; use a fixed command.

  • COPY FROM '/home/me/census.csv' reads the file from the machine where I typed it.
  • Any role with INSERT on the table may COPY from a server-side file.
  • pg_read_all_data is the role that allows COPY from a file on the server.
  • COPY FROM STDIN needs superuser because it streams data past permission checks.
  • COPY FROM skips triggers and CHECK constraints to load faster.
open as a page

In PostgreSQL, why is GENERATED AS IDENTITY preferred over serial or bigserial for a new table's primary key?

level: juniorimportance: must knowfreq 72%

basics

~20 s

serial is shorthand for a separate sequence plus a nextval() default; an identity column owns an internal sequence. Identity needs no sequence grant, copies cleanly with LIKE, can reject hand-written ids, and is the SQL-standard form.

open as a page

In PostgreSQL, which rows does FOR VALUES FROM ('2026-03-01') TO ('2026-04-01') accept, and what happens to a row no partition accepts?

level: juniorimportance: must knowfreq 58%

basics

~20 s

FROM is inclusive and TO is exclusive, so the partition holds 2026-03-01 up to but not including 2026-04-01. A row that fits no partition fails with 'no partition of relation found for row' unless a DEFAULT partition exists.

open as a page

In PostgreSQL, how do ILIKE, ~ and ~* differ from LIKE when you search stock symbols or company names?

level: juniorimportance: must knowfreq 58%

basics

~20 s

ILIKE is a case-insensitive LIKE that must match the whole string with % and _ wildcards; ~ and ~* are POSIX regular-expression matches, case-sensitive and case-insensitive, that match anywhere unless anchored with ^ and $.

open as a page

In PostgreSQL, how does INSERT ... ON CONFLICT DO NOTHING differ from ON CONFLICT DO UPDATE, and what does EXCLUDED refer to?

level: juniorimportance: must knowfreq 80%

basics

~10 s

ON CONFLICT DO NOTHING skips any proposed row that collides with an arbiter unique index or constraint; DO UPDATE instead updates the existing row, and EXCLUDED names the row that was proposed for insertion.

open as a page

In PostgreSQL, how do you make the database itself reject overlapping meeting-room bookings, and why does the usual EXCLUDE constraint need btree_gist?

level: middleimportance: must knowfreq 58%

basics

~20 s

Store each booking's time as a tstzrange and declare EXCLUDE USING gist (room_id WITH =, during WITH &&). Core GiST has no equality operator class for scalar types such as bigint, so the btree_gist extension supplies one for room_id.

open as a page

In PostgreSQL 18, how does uuidv7() differ from gen_random_uuid(), and what ordering does uuidv7() actually guarantee?

level: middleimportance: must knowfreq 55%

basics

~20 s

gen_random_uuid(), also spelled uuidv4() in 18, returns a fully random version 4 UUID. uuidv7() puts a millisecond Unix timestamp first, so values sort by creation time: strictly increasing within one session, only roughly time-ordered across sessions.

open as a page

In PostgreSQL, why does ALTER TABLE clicks ADD PRIMARY KEY (click_id) fail on a table partitioned by RANGE (clicked_at), and what are your legal options?

level: middleimportance: must knowfreq 64%

basics

~20 s

PostgreSQL has no global indexes: a unique key on a partitioned table is one unique index per partition, so it must include every partition key column. Use PRIMARY KEY (click_id, clicked_at), or partition by the column that must be unique.

open as a page

In PostgreSQL, how does SELECT DISTINCT ON pick the latest price per stock symbol, and what must its ORDER BY look like?

level: middleimportance: must knowfreq 68%

basics

~20 s

DISTINCT ON (symbol) keeps the first row of each symbol group in sort order, so ORDER BY must begin with symbol and then traded_at DESC to choose that row; add a unique tiebreaker so ties are deterministic.

open as a page

In PostgreSQL's INSERT ... ON CONFLICT, how is the arbiter index chosen, and when would you use ON CONSTRAINT instead of a column list?

level: middleimportance: must knowfreq 58%

basics

~20 s

PostgreSQL infers arbiters: every unique index whose key is exactly the listed columns or expressions, with a matching WHERE for partial ones. ON CONSTRAINT names a primary-key, unique or exclusion constraint directly and cannot name a bare unique index.

open as a page

In PostgreSQL, how would you load a 50-million-row census CSV quickly and safely, from staging table through indexes to ANALYZE?

level: seniorimportance: must knowfreq 55%

basics

~20 s

COPY the file into an UNLOGGED staging table created in the same transaction, validate and move rows with set-based SQL into a target without secondary indexes or foreign keys, build those afterwards, then ANALYZE before queries run.

open as a page

During a PostgreSQL release, ALTER TABLE accounts ADD COLUMN waits for a lock and every query on accounts stalls behind it; why, and how do lock_timeout and retries prevent it?

level: seniorimportance: must knowfreq 66%

basics

~20 s

The ALTER waits for ACCESS EXCLUSIVE behind a long transaction, and PostgreSQL queues every later conflicting request, even plain SELECTs, behind that waiter. A lock_timeout of a few seconds makes the DDL give up with 55P03 and retry later.

open as a page

In PostgreSQL, which ALTER TABLE forms rewrite the whole table and which only change the catalog, and how do you check before production?

level: seniorimportance: must knowfreq 58%

basics

~20 s

In PostgreSQL, ADD COLUMN with no or a non-volatile default, DROP COLUMN, SET DEFAULT, renames and varchar widening only change catalogs; volatile defaults, identity or stored generated columns and most type changes rewrite the table under a new relfilenode.

open as a page

A PostgreSQL insert into a meeting-room bookings table fails with SQLSTATE 23P01: which kind of constraint rejected it, and how should the application respond?

level: juniorimportance: should knowfreq 34%

basics

~20 s

SQLSTATE 23P01 is exclusion_violation: an EXCLUDE constraint, or a PostgreSQL 18 WITHOUT OVERLAPS key, found an existing row that conflicts, such as the same room at an overlapping time. Match the code, report the slot as taken, roll back.

open as a page

In PostgreSQL, what does transactional DDL let a schema migration do, and which commands refuse to run inside a transaction block?

level: juniorimportance: should knowfreq 52%

basics

~20 s

PostgreSQL runs almost all DDL inside a transaction, so a migration that fails halfway rolls back completely. A few commands refuse a transaction block: CREATE INDEX CONCURRENTLY, VACUUM, CREATE or DROP DATABASE, CREATE TABLESPACE and ALTER SYSTEM.

open as a page

In PostgreSQL 18, which constraints can be added NOT VALID, and how does a NOT VALID constraint differ from a NOT ENFORCED one?

level: middleimportance: should knowfreq 42%

basics

~20 s

In PostgreSQL 18, FOREIGN KEY, CHECK and now NOT NULL constraints can be added NOT VALID: new writes are checked, old rows wait for VALIDATE CONSTRAINT. NOT ENFORCED, new in 18 for CHECK and FOREIGN KEY, checks nothing at all.

open as a page

In PostgreSQL's COPY, how do the text, csv and binary formats differ, and how does each one represent a NULL value?

level: middleimportance: should knowfreq 40%

basics

~20 s

text, the default, is tab-delimited with backslash escapes and \N for NULL; csv uses commas and quoting, so an unquoted empty field is NULL and "" an empty string; binary is type-specific and marks NULL with length -1.

open as a page

In PostgreSQL 18, what do COPY's ON_ERROR ignore and REJECT_LIMIT options skip in a dirty CSV load, and what still aborts it?

level: middleimportance: should knowfreq 45%

basics

~20 s

ON_ERROR ignore (PostgreSQL 17) discards rows whose values fail their column type's input conversion; REJECT_LIMIT (18) fails the COPY once more than N rows are skipped. Wrong field counts and constraint violations still abort the whole load.

open as a page

After COPY loads legacy order ids into a PostgreSQL identity column, why does the next INSERT fail, and how does setval fix it?

level: middleimportance: should knowfreq 40%

basics

~20 s

COPY FROM writes the supplied ids even into a GENERATED ALWAYS column without calling nextval(), so the sequence still offers numbers the loaded rows hold. Resync it with setval(pg_get_serial_sequence('orders', 'order_id'), max(order_id)) while writers are paused.

open as a page

In PostgreSQL 18, why does CREATE INDEX fail on full_name GENERATED ALWAYS AS (first_name || ' ' || last_name), and what fixes it?

level: middleimportance: should knowfreq 45%

basics

~10 s

In PostgreSQL 18 a generated column without STORED is VIRTUAL: computed on read and never stored, so it cannot be indexed. Declare it STORED, or keep it virtual and index the same expression directly.

open as a page

In PostgreSQL, why does regexp_replace(phone, '[^0-9]', '') leave most punctuation in '+1 (555) 010-2030', and how do you normalise it?

level: middleimportance: should knowfreq 42%

basics

~20 s

regexp_replace replaces only the first match unless given the g flag, so it removed just the leading plus sign; regexp_replace(phone, '\D', '', 'g') strips every non-digit, and an anchored ~ check then validates the result.

open as a page

In PostgreSQL, which table lock do ADD COLUMN, ADD FOREIGN KEY, VALIDATE CONSTRAINT and RENAME COLUMN take, and what does each block?

level: middleimportance: should knowfreq 44%

basics

~20 s

In PostgreSQL, ADD COLUMN and RENAME COLUMN take ACCESS EXCLUSIVE, blocking everything; ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables, blocking writes; VALIDATE CONSTRAINT takes SHARE UPDATE EXCLUSIVE, leaving reads and writes running.

open as a page

In PostgreSQL 18, how can a single INSERT ... ON CONFLICT DO UPDATE report which rows were inserted versus updated, and each row's previous price?

level: middleimportance: should knowfreq 38%

basics

~20 s

PostgreSQL 18 lets RETURNING read old and new: old values are NULL for inserted rows and hold the prior values for conflicting rows that were updated, so old.product_id IS NULL flags inserts and old.price gives the previous price.

open as a page

In PostgreSQL 18, what do PRIMARY KEY ... WITHOUT OVERLAPS and FOREIGN KEY ... PERIOD enforce, and what do they still not support?

level: seniorimportance: should knowfreq 26%

basics

~20 s

WITHOUT OVERLAPS makes a key's last, range-typed column unique by non-overlap, enforced like an EXCLUDE constraint. PERIOD makes a foreign key require parent rows whose periods together cover the child's period. Temporal foreign keys accept only NO ACTION.

open as a page

A PostgreSQL checkout service set CACHE 100 on its order_id identity; why do ids now arrive out of order and jump by hundreds?

level: seniorimportance: should knowfreq 30%

basics

~20 s

PostgreSQL's sequence CACHE is per session: each backend reserves 100 values and serves them from private memory. Sessions hand out interleaved blocks, and unused values vanish when a session ends, so ids stay distinct but are neither ordered nor dense.

open as a page

In PostgreSQL, why does creating the March partition of clicks fail when its DEFAULT partition already holds late March rows, and how do you recover and prevent it?

level: seniorimportance: should knowfreq 40%

basics

~20 s

Adding a partition narrows what the DEFAULT partition may hold, so PostgreSQL scans the default and fails with SQLSTATE 23514 if any row belongs to the new range. Move those rows into a standalone table and attach it, in one transaction.

open as a page

In PostgreSQL, why do the clicks parent's statistics go stale even with autovacuum on, and how does PostgreSQL 18 change the way you refresh them?

level: seniorimportance: should knowfreq 36%

basics

~10 s

Autovacuum never processes a partitioned parent, so its whole-hierarchy statistics exist only after a manual ANALYZE. Schedule ANALYZE on the parent; PostgreSQL 18's ANALYZE ONLY clicks refreshes them without re-analyzing every partition.

open as a page

With pg_partman 5.5 managing the monthly clicks table in PostgreSQL, what do create_partition(), premake, the default table and run_maintenance_proc() do, and what must you monitor?

level: seniorimportance: should knowfreq 42%

basics

~20 s

pg_partman automates declarative partitions: create_partition() registers an existing partitioned table, premake (default 4) keeps future partitions ready, a default partition catches strays, and run_maintenance_proc() or its background worker creates and retires partitions. Monitor default-table rows and maintenance failures.

open as a page

In PostgreSQL, when is a WITH query folded into the outer query instead of materialized, and what do MATERIALIZED and NOT MATERIALIZED change?

level: seniorimportance: should knowfreq 55%

basics

~10 s

A non-recursive, side-effect-free WITH query referenced once is folded into the outer query by default; referenced twice it is computed once and stored. MATERIALIZED forces storing, NOT MATERIALIZED forces folding where allowed.

open as a page

In PostgreSQL, which index lets DISTINCT ON (symbol) ... ORDER BY symbol, traded_at DESC avoid a sort on a huge ticks table, and when is it still slow?

level: seniorimportance: should knowfreq 38%

basics

~20 s

A B-tree on (symbol, traded_at DESC) delivers rows already in DISTINCT ON order, so the plan becomes Unique over an index scan with no sort; it still reads every tick, so few symbols with many ticks need a jump-per-symbol query.

open as a page

showing 1–30 of 37