{}

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 field access Composite type (table.*)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 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

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

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

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

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

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 field access

Signature

Parameters

Examples

Composite type (table.*)

Signature

Parameters

Examples

IS JSON

Signatures

Parameters

Examples

Related PostgreSQL Categories