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

  1. That's constructor takes a record and supports merge (or as PostgreSQL likes to say "concatenate" (||)). An example of this is hstore.

  2. Using any type that supports an operation on a record, with a composite type. Two options that come to mind are jsonb and hstore.

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?