0

I would like to store some of rows of data in database to as a JSON (in json type field) for backup reasons, before transaction is launched.

Something like:

INSERT INTO public.backup (user_id, data) VALUES (1, (SELECT * FROM ...))

Is it possible to do it simple, without parsing select and converting it to JSON in my application?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Andrey
  • 2,659
  • 4
  • 29
  • 54

1 Answers1

1

You can convert whole rows to json with row_to_json():

INSERT INTO public.backup (user_id, data)
SELECT 1, row_to_json(t)
FROM   tbl t
WHERE  ...;  -- select some rows

It's not as simple to preserve column names if the source is a query rather than a plain table. See:

In Postgres 9.4 or later, consider the data type jsonb for your data column. Same query, the result is cast to jsonb with the assignment automatically.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Actually I used SELECT array_to_json (array_agg (row_to_json(t))) FROM parts.managers t to put all in one row. Thank you. – Andrey May 02 '15 at 17:47