CODEWITHSUNDEEP
postgresqlpostgresql-9.6
Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

Automatically cast value type to column type

TL;DR; In short, what I need is to somehow cast text to unknown from where Postgres will magically cast that to correct type; or some alternative solution to this while keeping in mind things that I want to avoid.

Error in question:

ERROR: column "id" is of type integer but expression is of type text

Say I've got this table:

CREATE TEMP TABLE unknown_test (
  id int,
  some_timestamp timestamp,
  value1 int,
  value2 int,
  value3 text);

Currently I'm doing DML on that table with queries like that:

INSERT INTO unknown_test (id, some_timestamp, value1, value2, value3)
VALUES ('5', '2018-01-10 14:11:03.763396', '3', '15', 'test2');

So values are unknown type and Postgres has some kind of built in cast for that (it is not in select * from pg_cast where castsource = 'unknown'::regtype;). This works, but is somewhat slow.

What I want to do is this (obviously I have actual table, not values()):

INSERT INTO unknown_test (id, some_timestamp, value1, value2, value3)
SELECT json_data->>'id', json_data->>'some_timestamp', json_data->>'value1', json_data->>'value2', json_data->>'value3'
  FROM (VALUES (jsonb_build_object('id', 1, 'some_timestamp', now(), 'value1', 21, 'value2', 5, 'value3', 'test')),
               (jsonb_build_object('id', 2, 'some_timestamp', now(), 'value1', 22, 'value2', 15, 'value3', 'test2')),
               (jsonb_build_object('id', 3, 'some_timestamp', now(), 'value1', 32, 'value2', 25, 'value3', 'test5')),
               (jsonb_build_object('id', 4, 'some_timestamp', now(), 'value1', 42, 'value2', 55, 'value3', 'test7'))
       ) AS j(json_data);

Sadly, those will give text type and will complain that I need to explicitly cast it. I can't do that, because I don't know what types those are. I could find out of course by checking pg_catalog or by storing in json data info about type. Both of those require some additional computation and/or storage and I want to avoid any unnecessary overhead to this (my pg_catalog is really fat).

Second thing I want to avoid is CREATE CAST for text type, unless someone can assure me it won't break anything.

Doing loop and dynamic SQL to get unknown type is my current approach and I need something faster, so my idea was not to use loop, but table instead.

Upvotes: 0

Views: 894

Answers (1)

user330315
user330315

Reputation:

You can use jsonb_populate_record for this: 

SELECT (jsonb_populate_record(null::unknown_test, json_data)).*
FROM ...

This will create a record of the same type as the table unknown_test and then the whole record is expanded into individual columns using the (...).* syntax.

This requires that the (first level) keys in the JSON document have exactly the same names as the columns in the table.

Upvotes: 2

Related Questions