6

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

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:

Related, more sophisticated tricks:

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.