3

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.

1
  • 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? Commented Apr 14, 2016 at 22:24

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.