PostgreSQL CSV imports: defaults, identity and generated columns

Classify values before mapping headers

“Database-owned” is an import policy, not a single PostgreSQL column type. A timestamp with DEFAULT now() may represent a historical event that the source is allowed to supply, or an audit time that the source must never change. The DDL cannot settle that business meaning.

Write the decision down per field. For historical timestamps, also record the source timezone and treatment of missing values. For identifiers, state whether they are external references or production primary keys. Matching names do not establish matching identity.

A small import that leaves generation to PostgreSQL

Run this example in one psql session connected to a disposable PostgreSQL 17 database. The temporary table disappears when the session ends.

CREATE TEMP TABLE import_lines_demo (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  external_ref text NOT NULL UNIQUE,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  status text NOT NULL DEFAULT 'new',
  total numeric GENERATED ALWAYS AS (quantity * unit_price) STORED
);

COPY import_lines_demo (external_ref, quantity, unit_price)
FROM STDIN WITH (FORMAT csv, HEADER MATCH);
external_ref,quantity,unit_price
line-a,2,12.50
line-b,3,4.00
\.

SELECT id, external_ref, status, total
FROM import_lines_demo ORDER BY id;

The result contains IDs 1 and 2, status new on both rows, and totals 25.00 and 12.00. This is an inline psql example: the line containing backslash-dot ends the data stream. It is not a block to paste into a web SQL editor.

The import list has three columns because there are three source fields. ID, status and total are absent from the input. PostgreSQL supplies them according to their different definitions. This is easier to review than sending six fields and hoping blank values mean the right thing.

For a separate local file, use the same column list with psql’s \copy. The COPY reference specifies how the field order, omitted columns and input options are interpreted.

Omitted, NULL and empty are different inputs

With the default PostgreSQL CSV settings, an unquoted empty field represents NULL. A quoted empty field, "", represents an empty string. Neither is the same as leaving a column out of the input list.

external_ref,quantity,unit_price,status
line-c,1,8.00,
line-d,1,8.00,""

Imported with status included and the default CSV options, the first record violates the table’s NOT NULL constraint. The second would supply an empty status rather than new. There is no allowed-status CHECK in this demonstration table, so an empty string on its own would be accepted. That illustrates why “accepted by PostgreSQL” can still be wrong for the application.

In an INSERT statement, SQL DEFAULT can request a default explicitly. The text DEFAULT in an ordinary CSV field is just text. PostgreSQL 17 has a separate COPY DEFAULT marker option; use it only with an agreed, collision-free file convention. It is not necessary when the whole column can be omitted.

When some records intentionally supply a value and others need the default, a single omitted-column list cannot express both cases. Options include a documented default marker, grouping rows by their write shape, or staging followed by deliberate INSERT statements. Do not replace the distinction with “empty means default” unless the data owner actually approves that rule.

COPY can supply an ALWAYS identity

GENERATED ALWAYS AS IDENTITY prevents an ordinary INSERT from casually supplying the identity value. COPY FROM can still use IDs present in its input. Treat that as an explicit import capability, not an authorization boundary.

After the earlier demonstration, the following COPY can insert a chosen ID:

COPY import_lines_demo (id, external_ref, quantity, unit_price)
FROM STDIN WITH (FORMAT csv);
900,line-explicit,1,1.00
\.

If your policy is to create new destination identities, exclude id from both the mapping and the COPY list. If preserving IDs is intentional, check conflicts and the associated sequence state. Supplying an identity does not by itself advance the sequence to the greatest imported ID; a later generated value can eventually collide.

Sequence repair belongs to the database owner’s migration plan. Do not run a generic “set sequence to max ID” command during concurrent application writes: values may already have been allocated. Rehearse the chosen procedure with the application’s concurrency and maintenance conditions. The identity documentation and sequence function reference explain the mechanisms.

Generated expressions are recomputed, not imported

A customer’s spreadsheet may have its own “Total” column. For the example table, the destination total is computed from quantity and unit price. Leave the source total outside the write mapping and compare it separately if it is useful evidence.

If the totals disagree, decide which rule is wrong. A source system might calculate tax before rounding while the target rounds first. Overwriting a generated destination value is not the correction path. You need either an approved input transformation or a reviewed change to the target model.

PostgreSQL 17 supports stored generated columns. Generation follows relevant BEFORE-trigger changes to the base row. The presence of a generated expression therefore does not establish that the value will equal a calculation performed earlier in a preview. Read the expression and applicable triggers, then test the actual write path. See PostgreSQL 17’s generated-column rules.

What to put in the import review

For each column, record its value supplier, omission rule, allowed source overrides and the evidence behind that decision. Include the exact destination table and ordered COPY column list. If a required value comes from the application, name the authority that supplies it.

• Test omitted, NULL and empty values separately where they are meaningful.
• Test an attempted source override of every protected column.
• Confirm whether preserved IDs require a sequence or reference-mapping procedure.
• Compare stored derived values with the approved business rules after insertion.

PG Import Check can help classify supplied DDL: required source values, ordinary defaults, identities and generated expressions remain distinct. It also shows structural findings and analysis gaps. It does not execute expressions, inspect the live schema, validate source records or infer that a default authorizes a field.

For fields such as tenant ID, continue with the authority and RLS guide. For checking the resulting values, use post-import verification.