Is there a way to partially initialize a TYPE?
Code examples based on your type definition:
CREATE TYPE my_type AS (name text, street text, location text);
Your syntax is not possible (as you already know). A plain cast does not allow a (partial) list of fields.
SELECT ROW('a', 'c')::my_type(name, location);
In a plain SQL cast expression, you have to provide NULL values (or some other default) for missing fields:
SELECT ROW('a', NULL, 'c')::my_type;
Or, using a row type literal as input:
SELECT '(a,NULL,c)'::my_type;
INSERT
or UPDATE
statements can target fields of composite types individually. (The underlying type is derived from the target.) Demo:
CREATE TABLE tbl (tbl_id serial, comp my_type);
INSERT INTO tbl (comp.name, comp.location) VALUES ('a', 'c')
UPDATE tbl
SET comp.name = 'X'
, comp.street = 'Y'
WHERE tbl_id = 1;
Fields not filled explicitly default to NULL. (Unless the type has a different default, which would be uncommon.)
Not possible for DELETE
, obviously, which always removes the whole row. The logical equivalent would be to set a subfield NULL
:
UPDATE tbl
SET comp.name = NULL
WHERE tbl_id = 1;
Normally you would use variables in a procedural language function - default PL/pgSQL. There you have SELECT INTO
. And you can address fields of a composite type individually. Demo:
DO
$$
DECLARE
_var my_type;
BEGIN
SELECT INTO _var.location, _var.name -- in any chosen order
'c', 'a';
RAISE NOTICE '%', _var;
END
$$
db<>fiddle here
Do not confuse PL/pgSQL assignment with SELECT INTO
and the SQL command SELECT INTO
(which should not be used at all, use CREATE TABLE AS
instead). See:
- SELECT INTO with regexp_replace() doesn't write changes into newly generated table
Related, more sophisticated tricks:
- Casting NULL type when updating multiple rows
- How to set value of composite variable field using dynamic SQL
(demonstrating the hstore#=
operator)
Yes. You can do this using any type
That's constructor takes a
record
and supports merge (or as PostgreSQL likes to say "concatenate" (||
)). An example of this ishstore
.Using any type that supports an operation on a record, with a composite type. Two options that come to mind are
jsonb
andhstore
.
jsonb
-- In a single call to jsonb_populate_record
SELECT jsonb_populate_record(
null::my_type,
jsonb_build_object('name', 'Evan')
);
-- With a subsequent merge after construction
SELECT jsonb_populate_record(
null::my_type,
'{}'
) || jsonb_build_object('name', 'Evan');
hstore
hstore
actually supports doing this directly on a row,
-- With single-call methods to #= or populate_record
CREATE EXTENSION hstore;
SELECT null::my_type #= hstore('name', 'Evan');
SELECT populate_record(null::my_type, hstore('name', 'Evan'))
Here is what the first option looks like.
-- With a subsequent merge after construction
CREATE EXTENSION hstore;
SELECT hstore(null::my_type) || hstore('name', 'Evan');
See Also
- Why doesn't jsonb constructor from a NULL record behave like hstore constructor?