PostgreSQL

Safety rules — apply whenever running SQL against PostgreSQL

1. Never put a password in a file, script, doc, test or commit. Pass it per command: PGPASSWORD=... psql ... (psql and Npgsql both read PGPASSWORD), or a connection string with no password plus the environment variable. A password the user gives in chat is for that session's commands only unless they say where it is recorded (their private global CLAUDE.md, never a repo).
2. Test inside a transaction you roll back: BEGIN; ... ROLLBACK; in the -f script. Remember that sequences (GENERATED ... AS IDENTITY, serial) are not rolled back, so ids keep climbing.
3. One statement per ad hoc -c; for anything longer use -f file.sql with -v ON_ERROR_STOP=1 -q. Without ON_ERROR_STOP a failed statement inside BEGIN aborts the transaction and every later line says "current transaction is aborted", hiding the first error: read the first error only.
4. Bound inspection queries (LIMIT); SET statement_timeout = '30s' for anything that might scan.
5. psql from Git Bash: "/c/Program Files/PostgreSQL/18/bin/psql.exe" -h localhost -U <user> -d <db> -f file.sql. localhost works while the machine name may be refused by pg_hba.conf (no pg_hba.conf entry for host ...): use the address the user has allowed.

Tool: python C:/Github/tools/sql_run.py --engine postgres --host localhost --user <user> --db <db> -e "<one statement>" runs one ad hoc statement with a 10 s connect and 30 s statement_timeout, a 10000 row stop, PGPASSWORD only and redacted output; it refuses a second statement and \ commands. It does not wrap a test in BEGIN ... ROLLBACK (rule 2): use -f for that. Read its docstring for the flags.

Names

• Unquoted names fold to lower case; "CustomerId" keeps its case and must then be quoted everywhere ("public"."Customer"). A PascalCase sample database works if every identifier is quoted; real databases are usually snake_case.
• A parameter or variable named like a column makes plpgsql say "column reference is ambiguous". Prefix parameters ("pName"), or write #variable_conflict use_column at the top of the body.

Reading the schema (what a code generator needs)

• information_schema.tables / columns (portable): udt_name (int4, bool, varchar, timestamp, timestamptz, numeric, uuid, bytea, jsonb), character_maximum_length, numeric_precision/scale, is_nullable, column_default, is_identity / identity_start, is_generated / generation_expression.
• Keys, unique indexes and foreign keys come from pg_catalog: pg_index (indisprimary, indisunique, indnkeyatts = key columns without INCLUDE columns, indkey[0] is 0-based), pg_constraint (contype = 'f'; conkey / confkey are parallel arrays: unnest(conkey, confkey) WITH ORDINALITY pairs the columns of a composite key), pg_class + pg_namespace. pg_indexes only gives index definitions as text.
• A serial column has no identity flag: it is a nextval('seq'::regclass) default. Treat both as identity.
• information_schema booleans can be NULL: (c.is_identity = 'YES' OR c.column_default LIKE 'nextval(%') is NULL when the default is NULL, and a reader's GetBoolean throws "Column 'x' is null". Wrap in COALESCE(..., false). Only a live test found it.
• Constraint names are unique per table, not per schema: group foreign keys by (constraint, other table).
• reltuples is -1 until a table is analyzed and stale after a bulk load: count for real (SELECT count(*) FROM (SELECT 1 FROM t LIMIT 10001) x) for small tables.

Functions instead of stored procedures

• Search/list: CREATE OR REPLACE FUNCTION ... RETURNS SETOF "public"."T" LANGUAGE sql STABLE then SELECT * FROM f(...) (composable, EF maps it like the table). Scalar count: RETURNS integer, called SELECT f(...) AS "Value" so EF's SqlQueryRaw<int> finds the column.
• Parameters without a default must come before parameters with one; call optional ones by name ("pName" => 'x'). OFFSET n LIMIT m or LIMIT m OFFSET n; case-insensitive search is ILIKE.
• No OUTPUT parameters: return the new key (RETURNING "Id" INTO v_id; RETURN v_id). Errors: RAISE EXCEPTION '...' USING ERRCODE = '55509'. A foreign-key delete failure is the foreign_key_violation exception (EXCEPTION WHEN foreign_key_violation THEN ...; the block makes a sub-transaction). The function runs in the caller's transaction: there is no BEGIN TRANSACTION inside it.
• CREATE OR REPLACE FUNCTION with a different parameter list adds a second function (an overload); it does not replace the first, and a call that fits both fails with function f(integer) is not unique. Run DROP FUNCTION IF EXISTS "s"."f"(<old types>) first (checked on 18.6).
• Upsert: UPDATE ...; IF FOUND THEN ... END IF; INSERT ... or ON CONFLICT (key) DO NOTHING; after loading rows with explicit identity values, move the counter: SELECT setval(pg_get_serial_sequence('"public"."T"', 'Id'), (SELECT max("Id") FROM "public"."T")). OVERRIDING SYSTEM VALUE is needed to insert into a GENERATED ALWAYS identity.

Npgsql / EF Core

• A DateTime maps to timestamp with time zone and Npgsql refuses any non-UTC value ("Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone'"). For timestamp columns say so: [Column(TypeName = "timestamp")] or configurationBuilder.Properties<DateTime>().HaveColumnType("timestamp") in ConfigureConventions.
• FromSqlRaw("SELECT * FROM f(@p1, @p2)", new NpgsqlParameter("@p1", NpgsqlDbType.Text) { Value = (object?)x ?? DBNull.Value }): type a null parameter. The money type works as decimal ([Column(TypeName = "money")]) but real databases use numeric.
• An enum column rejects the text parameter EF sends (column "m" is of type mood but expression is of type text). Create assignment casts once per enum: CREATE CAST (text AS the_enum) WITH INOUT AS ASSIGNMENT; and the same from character varying; the insert then works. Compare or order the column as m::text (for ILIKE). Checked on 18.6.
• Entities with jsonb columns need [Column(TypeName = "jsonb")] on the string property.

CHECK constraints (schema reading, 2026-10)

pg_get_constraintdef(oid) gives CHECK (((points >= 0) AND (points <= 10))); BETWEEN comes back expanded to two comparisons, a numeric literal can carry a cast ((0)::numeric) and an IN list comes back as = ANY (ARRAY['a'::text, 'b'::text]). Strip casts and parentheses before parsing. A strict bound (> 0) must stay strict: a whole-number column turns it into the next integer, a decimal box treats it as inclusive and the database still refuses the edge. CREATE DATABASE x TEMPLATE y fails while anybody is connected to y: terminate the sessions first (pg_terminate_backend).

Copying a database for a test

CREATE DATABASE "X_scratch" TEMPLATE "X"; copies a database without a dump. It needs the CREATEDB privilege and no other session on the source: a running API holds a pooled connection and the statement fails with source database "X" is being accessed by other users. Stop the API (or terminate its sessions) first. Drop with DROP DATABASE "X_scratch" and list pg_database afterwards. Checked on 18.6: the copy failed with one other session open, worked when idle, and the drop left nothing behind (the test login was a superuser, so the missing-privilege case was not tried).

Quote mixed-case names when they travel through a shell. Windows PowerShell 5.1 drops the double quotes inside an argument passed to a native program (psql -c 'SELECT 1 AS "MixedCase"' printed the header mixedcase), so CREATE DATABASE "X_scratch" can create a lower-case name and a "Dropped" message can sit over a database that is still there. PowerShell 7 keeps the quotes, and so does piping the SQL to psql on stdin (both tested).

Trigger history (2026-10)

One function and one AFTER UPDATE OR DELETE ... FOR EACH ROW trigger: TG_OP says UPDATE or DELETE, OLD.<col> is the before-image, current_user is the login, now() AT TIME ZONE 'utc' the UTC time. Quote PascalCase names in the function body too.

psycopg 3 / SQLAlchemy (2026-10)

• URL form: URL.create("postgresql+psycopg", username=..., password=os.environ.get("PGPASSWORD"), host=..., port=5432, database=...); the package is psycopg[binary] (no compiler needed on Windows). localhost works where the machine name is refused by pg_hba.conf.
• A PostgreSQL enum column needs sqlalchemy.dialects.postgresql.ENUM(..., name="type", create_type=False); leave a money column out or cast it, SQLAlchemy has no plain type for it.
• ILIKE with escape="\\" gives a case-insensitive "contains"; escape \, % and _ in the user's text first.
• numeric comes back as Decimal; FastAPI/Pydantic write it as a string unless told to write a number.
• A delete blocked by a foreign key raises IntegrityError (SQLSTATE 23503): roll back and answer 400 "in use", do not let it become a 500.

How this was verified

Tested on this machine. The evidence, the versions used and the items not re-run are in reference/verification.md: read it before saying a claim here was tested, and treat anything listed there as not re-run as unverified.

What this does not prove

• That a rule holds on another major version of PostgreSQL (written against 18) or on a managed service.
• That the Npgsql, EF Core, psycopg or SQLAlchemy notes hold for other package versions; they were not re-run.
• That a generated function is right for names with other quoting, extensions or partitioned tables.