Advanced SQL

Advanced SQL

PL/pgSQL

DB9 Difference: PL/pgSQL limitations

DB9 supports PL/pgSQL with variable declarations, IF/ELSIF, CASE, FOR loops (with EXIT WHEN and CONTINUE WHEN), PERFORM, RAISE, RETURN, dynamic EXECUTE — including INTO and USING — and BEGIN ... EXCEPTION and nested BEGIN ... END blocks, in both a CREATE FUNCTION body and a DO block.

WHILE and FOREACH loops are not supported anywhere, and they fail with 42601 — in a DO block immediately, but in a CREATE FUNCTION body at call time, not at create time: CREATE FUNCTION succeeds and the function is registered in pg_proc, then every call raises syntax error at or near "WHILE" (or ... "FOREACH"). A migration that only replays DDL will therefore look clean for these two. Rewrite a WHILE loop as a FOR loop with EXIT WHEN, and a FOREACH over an array as FOR ... IN SELECT unnest(arr).

Cursors are not supported either, but they fail the other way round — at create time, because what the parser rejects is the declaration rather than a statement in the body:

SQL

CREATE FUNCTION f() RETURNS int AS $$
  DECLARE c CURSOR FOR SELECT 1; BEGIN RETURN 1; END $$ LANGUAGE plpgsql;
-- ERROR:  unexpected token in PL/pgSQL type declaration: FOR   (42601)

CREATE FUNCTION f() RETURNS int AS $$
  DECLARE c refcursor; BEGIN RETURN 1; END $$ LANGUAGE plpgsql;
-- ERROR:  type "refcursor" does not exist   (42704)

Both spellings fail identically in a DO block, and a SQL-level DECLARE ... CURSOR FOR ends at internal error (0A000). So a DDL replay does not quietly accept cursor code the way it accepts a WHILE loop — it stops on it, and you find out during the migration rather than in production. FOR row IN SELECT ... LOOP covers the read-and-iterate case most cursors are used for, in both a DO block and a CREATE FUNCTION body; migrate anything that needs a scrollable or held cursor before switching to DB9.

DB9 supports PL/pgSQL functions and procedures with variable declarations and standard control flow. CALL runs a LANGUAGE plpgsql procedure through the same interpreter as a function body — IF, loops, CASE, EXECUTE, RAISE and exception handlers included — on both the wire protocol and the HTTP SQL API:

SQL

CREATE PROCEDURE add_user(n TEXT) LANGUAGE plpgsql AS $$
BEGIN INSERT INTO users (name) VALUES (n); END;
$$;                                        -- CREATE PROCEDURE

CALL add_user('alice');                    -- CALL; the row is inserted

CREATE PROCEDURE registers the procedure in pg_proc with prokind = 'p', and a LANGUAGE sql procedure runs its plain SQL statements with the arguments substituted. A LANGUAGE plpgsql body must be wrapped in BEGIN ... END: bare SQL is refused at create time with 42601 (PL/pgSQL function must have BEGIN block), as PostgreSQL also refuses it. A CREATE PROCEDURE with no LANGUAGE clause at all is rejected with a bare internal error (XX000), where PostgreSQL reports 42P13 (no language specified).

Multi-line DECLARE entries parse correctly — a declaration whose type or value spans several lines produces the same value as PostgreSQL, in both a DO block and a CREATE FUNCTION body:

SQL

DECLARE v int := 1
  + 2;               -- 3
DECLARE s text := 'abc'
  || 'def';          -- 'abcdef'
DECLARE v int
  := 42;             -- 42

Dynamic SQL and exception handling

EXECUTE works in a CREATE FUNCTION body as well as a DO block. The command string may be a literal, a variable, or an expression, and INTO and USING behave as in PostgreSQL:

SQL

CREATE FUNCTION count_rows(t TEXT) RETURNS INT AS $$
DECLARE n INT;
BEGIN
  EXECUTE 'SELECT count(*) FROM ' || quote_ident(t) INTO n;
  RETURN n;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION double_it(p INT) RETURNS INT AS $$
DECLARE n INT;
BEGIN
  EXECUTE 'SELECT $1 * 2' INTO n USING p;
  RETURN n;
END;
$$ LANGUAGE plpgsql;

Exception handling (BEGIN ... EXCEPTION) works in a CREATE FUNCTION body as well as a DO block. Handlers are selected by condition name, and SQLSTATE and SQLERRM are populated:

SQL

CREATE FUNCTION insert_user(p_name TEXT) RETURNS TEXT AS $$
BEGIN
  INSERT INTO users (name) VALUES (p_name);
  RETURN 'inserted';
EXCEPTION WHEN unique_violation THEN
  RETURN 'duplicate: ' || SQLSTATE;   -- 'duplicate: 23505'
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  EXECUTE 'CREATE TABLE audit_snapshot(x int)';
EXCEPTION WHEN others THEN
  RAISE NOTICE 'setup skipped';
END;
$$;

Exception handlers follow PostgreSQL rollback semantics: statements that ran before the exception are rolled back, and only the handler’s effects persist.

Nested BEGIN ... END blocks work in a function body too, and see the enclosing block’s variables.

CASE statements

CASE works in both function bodies and DO blocks, in simple and searched form:

SQL

CREATE FUNCTION size_label(n INT) RETURNS TEXT AS $$
BEGIN
  CASE
    WHEN n > 100 THEN RETURN 'large';
    WHEN n > 10 THEN RETURN 'medium';
    ELSE RETURN 'small';
  END CASE;
END;
$$ LANGUAGE plpgsql;

Handle CASE_NOT_FOUND with WHEN OTHERS

As in PostgreSQL, a CASE statement in which no branch matches and no ELSE is present raises case not found (20000, with HINT: CASE statement is missing ELSE part.). Unlike PostgreSQL, an exception handler written for it by name does not catch it — EXCEPTION WHEN case_not_found lets the error through, while EXCEPTION WHEN others catches it with SQLSTATE 20000. Include an ELSE branch, or trap the error with WHEN others and test SQLSTATE.

SQL

CREATE FUNCTION increment(val INT) RETURNS INT AS $$
BEGIN
  RETURN val + 1;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION safe_divide(a NUMERIC, b NUMERIC) RETURNS NUMERIC AS $$
BEGIN
  RETURN a / NULLIF(b, 0);
END;
$$ LANGUAGE plpgsql;

SELECT … INTO

SELECT ... INTO variable assigns a query result to a declared variable.

SQL

CREATE FUNCTION user_count() RETURNS INT AS $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM users;
  RETURN n;
END;
$$ LANGUAGE plpgsql;

RETURNING … INTO

DML statements inside PL/pgSQL functions can capture returned values into variables using RETURNING ... INTO. Works with INSERT, UPDATE, and DELETE.

SQL

CREATE FUNCTION create_order(p_item TEXT) RETURNS INT AS $$
DECLARE
  new_id INT;
BEGIN
  INSERT INTO orders (item) VALUES (p_item) RETURNING id INTO new_id;
  RETURN new_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION archive_user(p_id INT) RETURNS TEXT AS $$
DECLARE
  removed_name TEXT;
BEGIN
  DELETE FROM users WHERE id = p_id RETURNING name INTO removed_name;
  RETURN COALESCE(removed_name, 'not found');
END;
$$ LANGUAGE plpgsql;

Multiple columns can be captured into separate variables:

SQL

RETURNING id, name INTO v_id, v_name;

If the DML statement affects zero rows, the target variables are set to NULL. Expressions (not just column names) are supported in the RETURNING list.

More than one returned row is an error, not a silent truncation — matching PostgreSQL, which raises the same P0003 (adding HINT: Make sure the query returns a single row, or use LIMIT 1.):

SQL

DO $$ DECLARE v int; BEGIN
  DELETE FROM orders RETURNING id INTO v;   -- 3 matching rows
END $$;
-- ERROR:  query returned more than one row   (P0003)

INSERT and UPDATE behave the same way, and the whole statement is rolled back — nothing it would have written or removed survives, so the three rows above are still present after the error. Narrow the WHERE clause until it matches one row, or iterate with FOR row IN DELETE FROM ... RETURNING ... if you need every row.

Plain SELECT ... INTO is the looser case, on both engines: it takes the first row and raises nothing, so add an ORDER BY when which row you get matters.

SQL

DO $$ DECLARE v int; BEGIN
  SELECT id INTO v FROM orders ORDER BY id;   -- 3 matching rows
  RAISE NOTICE 'got %', v;                    -- got 1 — first row, no error
END $$;

Triggers

Row-level and statement-level triggers on INSERT, UPDATE, and DELETE. A row trigger may carry a WHEN (...) condition and an UPDATE OF column, ... list — see what CREATE TRIGGER accepts below.

SQL

CREATE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Supported trigger timing: BEFORE, AFTER. Supported events: INSERT, UPDATE, DELETE. A BEFORE trigger function runs as PL/pgSQL: it can assign to NEW (NEW.name := upper(NEW.name);) and the modified row is what gets stored, skip the row with RETURN NULL, or reject it with RAISE EXCEPTION — conditionally, inside IF … END IF, as well as unconditionally:

SQL

CREATE FUNCTION reject_bad() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.name = 'bad' THEN
    RAISE EXCEPTION 'rejected';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER guard BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION reject_bad();

INSERT INTO users VALUES (1, 'bad');   -- ERROR: rejected (P0001) — nothing is written

A BEFORE trigger function cannot use counted loops, CASE statements, EXECUTE, or nested blocks

CREATE TRIGGER ... BEFORE ... checks the trigger function’s body, and refuses one that uses a construct DB9 cannot run in a BEFORE trigger — the trigger is not created:

Construct in the function body	CREATE TRIGGER ... BEFORE fails with
FOR i IN 1..n LOOP	0A000 BEFORE trigger functions do not support PL/pgSQL FOR range statements
bare LOOP … END LOOP	0A000 … do not support PL/pgSQL LOOP statements
WHILE or FOREACH loop	42601 syntax error, as in any function
CASE statement	0A000 … do not support PL/pgSQL CASE statements
EXECUTE	0A000 … do not support PL/pgSQL EXECUTE statements
EXCEPTION handler	0A000 BEFORE trigger functions do not support EXCEPTION handlers
nested BEGIN … END	0A000 … do not support PL/pgSQL block statements

The query form of the FOR loop — FOR r IN SELECT … LOOP — is the exception: it is accepted, and the loop body runs, on BEFORE INSERT, BEFORE UPDATE and BEFORE DELETE alike, with the row variables each event provides (assigning NEW from the loop result, reading OLD). Only the counted FOR i IN 1..n form and the bare LOOP are refused.

Declare the loop variable, though. In a BEFORE trigger DB9 requires an explicit record variable and rejects the implicit form at CREATE TRIGGER:

ERROR:  BEFORE trigger FOR query target must be a declared local variable

So write DECLARE r RECORD; in the function body. Code arriving from PostgreSQL already does: PostgreSQL refuses an undeclared query-loop variable outright, at CREATE FUNCTION (loop variable of loop over rows must be a record variable or list of scalar variables), so a function that compiled there carries the declaration. DB9 is the more permissive of the two — it accepts the implicit form in a plain function and in an AFTER trigger, and only a BEFORE trigger insists on the declaration. (The loop that does declare its variable implicitly in both is the counted FOR i IN 1..n form, which a BEFORE trigger rejects for a different reason.)

Because the refusal lands on CREATE TRIGGER rather than on the function, a script that ignores the error leaves the table with no trigger at all, and the INSERTs that follow succeed silently.

The same check runs again when the trigger fires, so replacing an attached function with CREATE OR REPLACE FUNCTION does not get around it: every statement that fires the trigger then fails with the same 0A000 and writes nothing.

What a validation or rewrite trigger usually needs all works: IF / ELSIF / ELSE, RAISE EXCEPTION, PERFORM, DECLAREd locals, SELECT … INTO, a FOR r IN SELECT … LOOP query loop over a declared record variable, assignments to NEW from any expression (a local variable, or a subquery against another table), DML against other tables, and TG_OP / TG_TABLE_NAME. A CASE expression on the right of an assignment (NEW.col := CASE WHEN … END) is fine; rewrite a CASE statement as IF / ELSIF, or move logic that needs a counted FOR loop, a CASE statement, EXECUTE or an EXCEPTION handler into an AFTER trigger, which runs them all. This applies to BEFORE INSERT, BEFORE UPDATE and BEFORE DELETE alike, on both the wire protocol and the HTTP SQL API.

An AFTER trigger function runs the full interpreter, including the constructs a BEFORE trigger refuses — counted FOR loops, CASE statements, EXECUTE, nested blocks and EXCEPTION handlers. A compact one-line body (BEGIN INSERT INTO … ; RETURN NEW; END;), as formatters and ORMs emit it, is handled correctly for both RETURN NEW; and RETURN OLD;.

What CREATE TRIGGER accepts

FOR EACH STATEMENT has statement-level semantics: the trigger runs once per statement however many rows it touches, including a statement that matches no rows at all.

SQL

CREATE TRIGGER audit_stmt AFTER UPDATE ON orders
  FOR EACH STATEMENT EXECUTE FUNCTION audit_trigger();

UPDATE orders SET status = 'shipped';              -- every row → 1 execution
UPDATE orders SET status = 'x' WHERE id = -1;      -- no rows   → 1 execution

A row-level trigger can be narrowed with a WHEN condition over OLD and NEW columns, and an UPDATE trigger with a column list:

SQL

CREATE TRIGGER price_changed AFTER UPDATE ON products
  FOR EACH ROW WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION audit_trigger();

CREATE TRIGGER stock_touched BEFORE UPDATE OF price, in_stock ON products
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

price_changed fires only when the price actually changes; stock_touched fires whenever price or in_stock appears in the SET list, changed or not, and never for an update that names only other columns.

Trigger arguments work on row and statement triggers alike: EXECUTE FUNCTION f('a', 'b') passes them to the function in TG_NARGS and TG_ARGV[], records them in pg_trigger.tgnargs and tgargs, and pg_get_triggerdef() reproduces them.

These forms are rejected at CREATE TRIGGER, each with 0A000 and a message that names the feature:

Form	Message
WHEN on a FOR EACH STATEMENT trigger	statement trigger WHEN predicates are not supported
WHEN (OLD.* IS DISTINCT FROM NEW.*)	whole-record trigger WHEN predicates are not supported
BEFORE / AFTER TRUNCATE	TRUNCATE triggers are not supported
CREATE CONSTRAINT TRIGGER	constraint triggers are not supported
REFERENCING NEW TABLE AS ...	trigger transition relations are not supported
INSTEAD OF on a view	INSTEAD OF triggers are not supported

Compare individual columns in place of the whole-record WHEN form, as in price_changed above.

A WHEN condition is also limited in what it may reference. Integer, numeric, text, boolean and JSONB columns work, with comparison operators, LIKE, IS DISTINCT FROM and functions such as lower(), upper(), abs() and coalesce(). A condition is refused at CREATE TRIGGER if it touches any temporal column (timestamp, timestamptz, date, time or interval), calls a session-dependent or volatile function (now(), current_date, current_user, session_user, random()), casts to a date, or calls a user-defined function:

ERROR: trigger WHEN supports the documented context-independent scalar subset; temporal values, SQL functions, unclassified builtins, and session-dependent casts are not supported   (0A000)

So the common WHEN (OLD.updated_at IS DISTINCT FROM NEW.updated_at) guard cannot be written — even WHEN (NEW.updated_at IS NOT NULL) is rejected. Put that test inside the trigger function with an IF, or key the condition on a non-temporal column. (A user-defined function called without its schema is refused with a different message, catalog function '<name>' in a stored policy must be schema-qualified; qualifying it does not help — it then gets the message above.)

DROP TRIGGER on a trigger that does not exist fails with 42704 (trigger "…" for table "…" does not exist), as in PostgreSQL. Use DROP TRIGGER IF EXISTS in migration scripts.

Event triggers

CREATE EVENT TRIGGER is supported for the ddl_command_start, ddl_command_end and sql_drop events, with an optional WHEN TAG IN (...) filter. The function receives TG_EVENT and TG_TAG and runs in the same transaction as the DDL — its writes roll back with it — and it aborts the statement by raising:

SQL

CREATE FUNCTION no_new_tables() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'DDL blocked by policy';
END $$;

CREATE EVENT TRIGGER block_create ON ddl_command_start
  WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION no_new_tables();

CREATE TABLE t (id int);            -- ERROR: DDL blocked by policy (P0001); no table is created

ALTER EVENT TRIGGER block_create DISABLE;
DROP EVENT TRIGGER block_create;

Event triggers are listed in pg_event_trigger. The table_rewrite and login events are rejected (0A000 event table_rewrite is not supported).

While an enabled event trigger exists, most DDL is refused

As long as the database has an enabled event trigger, only a narrow set of DDL runs. Everything else fails with 0A000 and a message of the form CREATE VIEW variant is not supported with matching event triggers.

Despite the wording, the trigger does not have to match the statement: one filtered to WHEN TAG IN ('CREATE TABLE') refuses CREATE VIEW just the same. With such a trigger in place — one whose function does nothing — this is what was measured. (“Still runs” means the statement gets as far as firing the trigger; a function that raises, like no_new_tables above, still aborts the statements it fires for.)

Still runs	Refused (0A000)
CREATE TABLE with ordinary columns, PRIMARY KEY, SERIAL, identity, UNIQUE and CHECK	CREATE TABLE with a foreign key, CREATE TEMP TABLE, CREATE TABLE ... AS SELECT
CREATE INDEX on a btree (plain, UNIQUE, partial, IF NOT EXISTS), DROP INDEX	CREATE INDEX ... USING gin / USING hnsw, CREATE INDEX CONCURRENTLY, CREATE VIEW, CREATE OR REPLACE VIEW, CREATE MATERIALIZED VIEW, DROP VIEW
ALTER TABLE ... ADD COLUMN with no constraint (NOT NULL DEFAULT is fine), RENAME COLUMN, RENAME TO, ALTER COLUMN ... SET DEFAULT / SET NOT NULL / DROP NOT NULL	ALTER TABLE ... ADD COLUMN with UNIQUE, CHECK, REFERENCES or SERIAL, DROP COLUMN, ALTER COLUMN ... TYPE / DROP DEFAULT, ADD CONSTRAINT (CHECK, UNIQUE, FOREIGN KEY), ENABLE ROW LEVEL SECURITY
CREATE / DROP FUNCTION, CREATE / DROP TRIGGER	CREATE SEQUENCE, CREATE TYPE ... AS ENUM, CREATE SCHEMA, CREATE EXTENSION, CREATE POLICY
DROP TABLE (without CASCADE), TRUNCATE, and INSERT / UPDATE / DELETE	DROP TABLE ... CASCADE, GRANT, REVOKE, COMMENT ON

Treat the left column as the exception rather than the rule, and read it narrowly: the same statement with one more clause often lands on the right (SET DEFAULT runs, DROP DEFAULT does not). A statement that is not listed on the left should be assumed refused until you have tried it.

A disabled trigger does not count. Before running any migration, run ALTER EVENT TRIGGER <name> DISABLE, and ALTER EVENT TRIGGER <name> ENABLE afterwards — or drop and recreate it.

Over the HTTP SQL API, send ALTER EVENT TRIGGER <name> DISABLE in a separate request before the migration. Combining it with CREATE VIEW in one request still raises 0A000, even though the trigger is disabled in the catalog afterwards. The same statements succeed as separate requests, or sequentially over a PostgreSQL wire connection.

Sequences

SQL

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXTVAL('order_seq');          -- 1000
SELECT NEXTVAL('order_seq');          -- 1001
SELECT SETVAL('order_seq', 2000);
SELECT NEXTVAL('order_seq');          -- 2001
DROP SEQUENCE order_seq;

▶ Run

CURRVAL('order_seq') and LASTVAL() return the value the current session last drew, so they need a NEXTVAL earlier on the same connection. Without one they raise 55000 (currval of sequence "order_seq" is not yet defined in this session, or lastval is not yet defined in this session). Over the HTTP SQL API every request is a new session, so send them in the same request as the NEXTVAL:

SQL

SELECT NEXTVAL('order_seq'); SELECT CURRVAL('order_seq'); SELECT LASTVAL();

Sequence options: START, INCREMENT, MINVALUE, MAXVALUE, CACHE, CYCLE.

ALTER SEQUENCE changes any of them after creation (RESTART [WITH], INCREMENT BY, the bounds, CACHE, CYCLE), as well as OWNER TO, OWNED BY and RENAME TO; only SET SCHEMA, SET LOGGED and SET UNLOGGED raise a bare ERROR: internal error carrying 0A000. SETVAL() repositions a sequence too. DROP SEQUENCE refuses (2BP01) while a column default or identity column still depends on the sequence — see DDL — Other DDL for both.

See also: DDL — Identity columns for GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY.

Custom Types

SQL

-- Enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
ALTER TYPE mood ADD VALUE 'excited';

-- Composite type
CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);

▶ Run

Enum values sort by label text, not by declaration order

PostgreSQL orders an enum by the position of each label in CREATE TYPE. DB9 compares enum values as their label text, so ordering follows the alphabet instead — without any error:

SQL

CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'critical');
CREATE TABLE tickets (id INT PRIMARY KEY, p priority);
INSERT INTO tickets VALUES (1, 'high'), (2, 'low'), (3, 'critical'), (4, 'medium');

SELECT p FROM tickets ORDER BY p;                -- critical, high, low, medium  (PostgreSQL: low, medium, high, critical)
SELECT max(p) FROM tickets;                      -- medium                       (PostgreSQL: critical)
SELECT count(*) FROM tickets WHERE p >= 'high';  -- 3                            (PostgreSQL: 2)

The same applies to <, >, BETWEEN, min, greatest, and least, and it does not change when the column has an index. pg_enum.enumsortorder does record the declared order — including values placed with ADD VALUE … BEFORE or AFTER — but comparisons do not consult it. enum_first(), enum_last() and enum_range() do not exist (42883).

Where order matters, sort on an explicit rank rather than on the enum itself:

SQL

SELECT p FROM tickets
ORDER BY array_position(ARRAY['low', 'medium', 'high', 'critical'], p::text);
-- low, medium, high, critical

Equality, IN, and grouping are unaffected. See DDL — Other DDL for which ALTER TYPE and DROP TYPE forms are supported.

Collations

SQL

CREATE COLLATION my_collation (LOCALE = 'en_US.utf8');
DROP COLLATION my_collation;

▶ Run

A collation you create is real: it persists across sessions, and declaring it on a column changes how that column sorts and compares.

SQL

CREATE COLLATION my_collation (LOCALE = 'en_US.utf8');
CREATE TABLE coll_demo (t TEXT COLLATE my_collation);
INSERT INTO coll_demo VALUES ('B'), ('a');

SELECT t FROM coll_demo ORDER BY t;
-- a
-- B

A TEXT column declared without a collation sorts the same way as COLLATE "C" — by byte value — so the same two rows come back in the opposite order (B, then a). The same applies to comparisons: ('a' COLLATE my_collation) < 'B' is true, while an uncollated 'a' < 'B' is false.

The LOCALE you name is ignored

Declaring any collation switches the column to one fixed locale-aware order. The LOCALE value has no effect — a collation created with LOCALE = 'C' sorts identically to one created with LOCALE = 'en_US.utf8':

SQL

CREATE COLLATION coll_c  (LOCALE = 'C');
CREATE COLLATION coll_en (LOCALE = 'en_US.utf8');
-- rows 'a', 'A', 'b', 'B'
-- COLLATE coll_c   -> a, A, b, B      PostgreSQL's C collation gives A, B, a, b
-- COLLATE coll_en  -> a, A, b, B
-- no collation     -> A, B, a, b

So you can choose between byte order and locale order, but not between locales. DB9’s database-default ordering is bytewise where PostgreSQL follows the database locale — a known parity gap (DB9-FEAT-132).

A window’s ORDER BY drops the collation

A collation applies to a plain ORDER BY on the column and to an ORDER BY written inside an aggregate, but not to a window’s ORDER BY. That one silently falls back to byte order on the same column, in the same query:

SQL

-- rows 'a', 'A', 'b', 'B' in a column declared COLLATE my_collation
SELECT t FROM cx ORDER BY t;                      -- a, A, b, B   (collated)
SELECT string_agg(t, ',' ORDER BY t) FROM cx;     -- a,A,b,B      (collated)
SELECT array_agg(t ORDER BY t) FROM cx;           -- {a,A,b,B}    (collated)
SELECT t, row_number() OVER (ORDER BY t) FROM cx; -- A, B, a, b   (byte order)

So row_number(), rank() and first_value() over a collated column work in byte order. Naming the collation in the window does not help — OVER (ORDER BY t COLLATE my_collation) returns the same byte-ordered result. If the numbering has to follow the collation, sort with a plain ORDER BY and number the rows in your application.

The catalog, however, does not reflect any of this. Two lookups you might reach for are misleading:

Lookup	What it returns	Reality
SELECT collname FROM pg_collation	only C, POSIX, default	your collation exists
pg_attribute.attcollation for a COLLATE column	100, the default OID — identical to a column declared with no collation	the column does carry the declared collation

So do not use either one to check whether a collation exists. Create it again instead: if it is already there, that fails with ERROR: collation "my_collation" already exists (42710) — and if it was not, remember to DROP COLLATION the one you just made.