5

I went by the example from the docu which went fine:

select * from json_populate_record(null::x, '{"a":1,"b":2}')

But my self-constructed JSON simplified similar to this did not work :-(

-- p_some_num of type int
select * from json_populate_record( null:my_record_type, '{"a":'||p_some_num||'',"b":2}' )

resulting in:

ERROR: function json_populate_record(my_record_type, text) does not exist

2 Answers 2

3

I should have read more carefully and was not aware of the implicit text to json conversion in the example. After some fiddling around with what was wrong, of course the following works:

select * from json_populate_record( 
  null:my_record_type, ('{"a":'||p_some_num||'',"b":2}')::json )
5
  • 2
    what are you using for my_record_type in this example?
    – ryantuck
    Jun 19, 2017 at 15:40
  • @Ryan Tuck: something like CREATE TYPE x(a int,b int) (as Pavan stated) Mar 4, 2019 at 11:13
  • why can't postgres derive the types by itself? Like pandas does when reading csv files Jun 3, 2021 at 10:56
  • @SergeyZaitsev it maybe could, but its open source and this is maybe not a widely used functionality and thus priority. pg is way behind oracle or m$ in some areas since decades. but it is robust and feature-rich enough to be a fitting competitor in many use cases. Jun 4, 2021 at 16:22
  • @SergeyZaitsev you can use tables, views or custom types as valid types. Unfortunately unable to make completely dynamic tables from JSONB though.
    – rup
    Dec 8, 2021 at 9:31
3

Andreas Dietrich answered your question, I'll be a little more specific with the example. You can find the answer over here.

According to the documentation, the base or record type is referred from either a Type or a Table. Using which it can determine the column mapping.

Type is a user defined data type. You can find about a Type construct here.

tldr;

for that to work, you have create a table like this.

CREATE TABLE x(a int,b int);

or a type like this.

CREATE TYPE x(a int,b int);

Then run the query.

select * from json_populate_record(null::x, '{"a":1,"b":2}')

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.