Is there a way to partially initialize a TYPE? I have created a custom type:

CREATE TYPE my_type as(name text, street text, location text);


Initializing such a type is easy:

SELECT ROW('a', 'b', 'c')::my_type;


I am wondering if there is an easy way to initialize a type partially, meaning that all unmentioned columns are set to null implicitly. In pseudo-code, something like:

SELECT ROW('a', 'c')::my_type(name, location);


In addition, is there also a way to initialize a type when the columns are not mentioned in the correct order? In pseudo-code, something like:

SELECT ROW('c', 'a', 'b')::my_type(location, name, street);
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&lt;>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&#39;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)