0

This question is partly answered in this other question, but not fully.

I have a table and I want to clone a record modifying one field. (Basically I want to create fake data for tests, without worrying if the schema is changing).

Using the ideas from the other questions I can do:

SELECT json_populate_record(my_table, json_object(ARRAY['id_table', 2000::text])) 
FROM my_table WHERE id_table = 1

That will return a record with exactly the same fields as the one with id_table 1 but the id_table which will be changed.

However, I don't know how to insert that into the table. If I do:

INSERT INTO my_table 
SELECT json_populate_record(my_table, json_object(ARRAY['id_table', 2000::text])) 
FROM my_table where id_table = 1

I get

ERROR column id_table is of type bigint but expression is of type my_table"

How can one reinsert the field?

Gabriel Furstenheim
  • 2,969
  • 30
  • 27

1 Answers1

1
INSERT INTO my_table 
SELECT (json_populate_record(my_table, json_object(ARRAY['id_table', 2000::text]))::my_table).*
FROM my_table 
WHERE id_table = 1;

http://www.sqlfiddle.com/#!17/8180d/3

404
  • 8,022
  • 2
  • 27
  • 47