Search code examples
jsonpostgresqluuid

Generate UUID for Postgres JSON document

I'm inserting into a Postgres table with a JSON document and I want to generate a unique ID for the document. I can do that on my own, of course, but I was wondering if there was a way to have PG do it.

INSERT INTO test3 (data) VALUES ('{"key": "value", "unique": ????}')

The docs seem to indicate that JSON records fit into various SQL data types, but I don't see how that actually works.

Solution

  • How about just concatenating? Assuming your column is of type json/jsonb, something like the following should work:

    INSERT INTO test3 (data) VALUES (('{"key": "value", "unique": "' || uuid_generate_v4() || '"}')::jsonb)