[SOLVED] INSERT SELECT FROM VALUES casting

INSERT SELECT FROM VALUES casting

It's often desirable to INSERT from a SELECT expression (e.g. to qualify with a WHERE clause), but this can get postgresql confused about the column types.

Example:

CREATE TABLE example (a uuid primary key, b numeric);
INSERT INTO example 
SELECT a, b 
FROM (VALUES ('d853b5a8-d453-11e7-9296-cec278b6b50a', NULL)) as data(a,b);
=> ERROR:  column "a" is of type uuid but expression is of type text

This can be fixed by explicitly casting in the values:

INSERT INTO example 
SELECT a, b 
FROM (VALUES ('d853b5a8-d453-11e7-9296-cec278b6b50a'::uuid, NULL::numeric)) as data(a,b);

But that's messy and a maintenance burden. Is there some way to make postgres understand that the VALUES expression has the same type as a table row, i.e. something like

VALUES('d853b5a8-d453-11e7-9296-cec278b6b50a', NULL)::example%ROWTYPE

Edit:

The suggestion of using (data::example).* is neat, but unfortunately it complete seems to screw up the postgres query planner when combined with a WHERE clause like so:

INSERT INTO example 
SELECT (data::example).* 
FROM (VALUES ('d853b5a8-d453-11e7-9296-cec278b6b50a', NULL)) as data 
WHERE NOT EXISTS (SELECT * FROM example 
                  WHERE (data::example) 
                  IS NOT DISTINCT FROM example);

This takes minutes with a large table.

Solution

You can cast a record to a row type of your table:

INSERT INTO example 
SELECT (data::example).*
FROM (
    VALUES 
      ('d853b5a8-d453-11e7-9296-cec278b6b50a', NULL),
      ('54514c89-f188-490a-abbb-268f9154ab2c', 42)
) as data;

data::example casts the complete row to a record of type example. The (...).* then turns that into the columns defined in the table type example