Clone field and modify without knowing the fields in Postgres

Question

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?

404 · Accepted Answer · 2018-04-05 15:47:56Z

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

answered Apr 5, 2018 at 15:47

404

8,7522 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Clone field and modify without knowing the fields in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related