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<>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)

StackExchange Database Administrators Q#217691, answer score: 5