{}

PostgreSQL Composite Type Functions

Complete reference for PostgreSQL composite type functions covering record field access, table.* row expansion, the IS JSON predicate, and composite type constructors. Covers named composite types, anonymous records, and the RECORD pseudo-type. Updated for PostgreSQL 16.

3 functions

Jump to function

Composite type (table.*)Composite type field access IS JSON

What are PostgreSQL Composite Type Functions?

PostgreSQL composite types allow columns to hold structured values with named fields, similar to a full table row stored in a single column. A composite value can be accessed with dot notation (mycolumn.fieldname) or constructed with ROW(). The IS JSON predicate (PostgreSQL 16+) validates whether a text or bytea value is well-formed JSON. Composite types are useful for returning multiple values from SQL functions and grouping related columns without creating a separate table.

Composite type (table.*)

PG 7.4+→ set of all fields

Expands all columns of a table row as individual fields. Useful in SELECT, INSERT, and with composite types.

DeveloperDBA

Signature

table.* → expanded row fields

Parameters

Parameter	Type	Description
table_ref	table or composite value	Source to expand

Examples

sql

SELECT u.* FROM users u WHERE id = 1;

→All columns of user row

sql

SELECT (ROW(1, 'Alice', true)).* AS (id int, name text, active bool);

→Expanded composite record fields

sql

SELECT json_populate_record(NULL::users, data).* FROM user_json_queue;

→Expanded typed record from JSON

sql

SELECT (p).*, row_number() OVER (PARTITION BY (p).category ORDER BY (p).price) AS rank
FROM (SELECT product_row AS p FROM product_catalog) sub;

→All product fields plus a per-category price rank, expanding a composite column inline

⚠Anti-Pattern— Using table.* expansion in INSERT…SELECT without naming target columns

When `SELECT t.*` order and the INSERT target column order differ (which can happen silently after an ALTER TABLE ADD COLUMN or if tables were created independently), values land in the wrong columns with no error raised.

✓ Instead: Always name columns explicitly in INSERT: `INSERT INTO target (col1, col2) SELECT col1, col2 FROM source` rather than relying on positional `SELECT *` expansion.

Chain `json_populate_record(NULL::target_type, json_col).*` to convert a JSON column into typed columns in one step — a clean pattern for importing JSON payloads into a staging table.

example

INSERT INTO users SELECT (json_populate_record(NULL::users, payload)).* FROM user_import_queue;

→Typed insert from JSON payload

Composite type field access

PG 7.4+→ field type

Accesses a named field from a composite type (structured type) value. Parentheses are required when the composite is a column reference.

DeveloperDBA

Signature

(composite_value).field_name → field_type

Parameters

Parameter	Type	Description
composite_value	composite type	A composite type value or column
field_name	identifier	Name of the field to access

Examples

sql

CREATE TYPE address AS (street text, city text, zip text);
SELECT (billing_address).city FROM customers;

→City from the billing_address composite column

sql

SELECT (ROW(1, 'Alice')).f2;

→Alice (anonymous composite)

sql

CREATE TYPE inventory_item AS (name text, unit_price numeric, quantity int);
SELECT (item).name, (item).unit_price * (item).quantity AS line_total
FROM order_lines;

→Item name and computed line total from a composite-type column

sql

UPDATE employees
SET contact = ROW((contact).phone, 'new@example.com', (contact).address)::contact_info
WHERE id = 42;

→Updates the email sub-field of a composite contact column while preserving other fields

⚠Anti-Pattern— Omitting parentheses around composite column references

Writing `billing_address.city` without parentheses makes PostgreSQL parse it as `table_alias.column` notation, raising a confusing error (`column "city" does not exist`) or silently resolving to the wrong table's column.

✓ Instead: Always wrap composite column references in parentheses: `(billing_address).city`. This is required syntax for composite-type field access.

To access a field from a composite-type column, you need parentheses: `(address_col).city`. Without them, PostgreSQL interprets it as table.column notation and may error or return unexpected results.

example

SELECT (shipping_address).street, (shipping_address).city, (shipping_address).zip FROM orders;

→Individual address fields from a composite column

IS JSON

PG 16+→ boolean

Tests whether a text or jsonb expression contains valid JSON. Optionally tests for a specific type (scalar, array, or object).

DeveloperDBA

Signatures

expression IS JSON [ SCALAR | ARRAY | OBJECT ] → boolean

expression IS NOT JSON → boolean

Parameters

Parameter	Type	Description
expression	text or jsonb	Value to test

Examples

sql

SELECT '"hello"' IS JSON;

→true

sql

SELECT '{"a":1}' IS JSON OBJECT;

→true

sql

SELECT '[1,2,3]' IS JSON ARRAY;

→true

sql

SELECT 'not json' IS JSON;

→false

⚠Anti-Pattern— Using IS JSON as the only validation before processing user-supplied JSON

`IS JSON` confirms syntactic validity only. `'null'::text IS JSON` returns true, and so does `'[]'::text IS JSON OBJECT` — wait, no: `IS JSON OBJECT` rejects non-objects. But it still admits any object regardless of keys, so schema conformance is not checked.

✓ Instead: Use `IS JSON OBJECT` to require an object, then validate required keys and types in the application layer or with a CHECK constraint using jsonb operators before accepting the payload.

Use `IS JSON` to filter valid JSON rows before casting: `WHERE payload IS JSON OBJECT` ensures only object-shaped JSON passes through, preventing errors in downstream processing.

example

INSERT INTO events SELECT payload::jsonb, received_at FROM raw_events WHERE payload IS JSON OBJECT;

→Only valid JSON objects are processed

PostgreSQL Composite Type Functions

Jump to function

What are PostgreSQL Composite Type Functions?

Composite type (table.*)

Signature

Parameters

Examples

Composite type field access

Signature

Parameters

Examples

IS JSON

Signatures

Parameters

Examples

Related PostgreSQL Categories