How to add new JSON column in Postgres

Question

I am trying to add a json type column to the table, but it doesn’t work and I cannot get normal examples, what am I doing wrong?

ALTER TABLE user ADD COLUMN purshased_product SET DATA TYPE JSONB USING purshased_product::JSONB;

I'm not trying to change the column, but just create a new one with json type

@Convert(converter = PurshasedProductConverter.class)
private PurshasedProductConverter[] purshasedProducts;

my variable

Answer 1

To add a new column use:

ALTER TABLE "user" ADD COLUMN purshased_product jsonb;

Online example: https://rextester.com/SVST52826

The set data type and using clauses are only used to modify existing columns.

---

Note that useris a reserved keyword. It's a bad idea to create a table with that name. If you insist on that, you have to use double quotes each time you refer to the table (as I did)

Answer 2

Sample:

ALTER TABLE schema.t_my_table
ADD COLUMN purshased_product jsonb; (You may also use the type json)

Please check here to understand the diff. between the two data types json and jsonb.

The data types json and jsonb, as defined by the PostgreSQL documentation,are almost identical; the key difference is that json data is stored as an exact copy of the JSON input text, whereas jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.