Set a default value for a jsonb property in PostgreSQL

Question

Let's say that I have a jsonb column with documents like:

{
 "lead": "The lead",
 "video": "An URL to the video"
}

In some cases I also need to store the name of a picture that's uploaded to the server by the user in the JSON. Obviously I don't want clashes on the filenames so I wonder if it's possible to use uuid_generate_v4() as default value:

insert into document (title, content) values (
 "The title of the document",
 '{
 "lead": "The lead",
 "video": "An URL to the video",
 "picture": ":?" -- Here I need the value returned by uuid_generate_v4()
 }'
);

Is it possible or should I get the uuid in the application before inserting the data in the database?

Thanks beforehand.

• András Váczi: Do you mean you want to amend the value you get, if the "picture" key is missing or empty? Do you have only one document type? If multiple, do they all have (or need) a filename?

Answer 1

You can resort to row_to_json for things like this.

insert into document (title, content)
select 'The title of the document', row_to_json(n)
from (
 select 'The lead' as lead,
 'An URL to the video' as video,
 uuid_generate_v4() as picture
 ) n