Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

CREATE TABLE tbl (tbl_id serial, comp my_type);
SELECT ROW('a', NULL, 'c')::my_type AS col1
    , '(a,NULL,c)'::my_type AS col2;


col1
col2


(a,,c)
(a,NULL,c)


INSERT INTO tbl (comp.name, comp.location) VALUES ('a', 'c')
RETURNING *;

UPDATE tbl
SET    comp.name = 'X'
     , comp.street = 'Y'
WHERE  tbl_id = 1
RETURNING *;

UPDATE tbl
SET    comp.name  = NULL
WHERE  tbl_id = 1
RETURNING *;


tbl_id
comp


1
(a,,c)




tbl_id
comp


1
(X,Y,c)




tbl_id
comp


1
(,Y,c)


DO
$$
DECLARE
    _var my_type;
BEGIN
   SELECT INTO _var.location, _var.name  -- in any chosen order
          'c', 'a';
   RAISE EXCEPTION '%', _var;  --  to see it in the fiddle
END
$$;
ERROR:  (a,,c)
CONTEXT:  PL/pgSQL function inline_code_block line 7 at RAISE