CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

PostgreSQL - copying numeric value from jsonb field to integer column

I have the following schema:

CREATE TABLE survey_results (
    id integer NOT NULL,
    publish_id integer,  
    raw jsonb DEFAULT '{}'::jsonb
);

INSERT INTO survey_results (id, raw)
    VALUES (1, '{"survey": {"publish_id": 5}}');

I want to copy value from jsonb field to integer column with the following update:

UPDATE survey_results SET publish_id = raw#>>'{survey, publish_id}';

But this returns me:

ERROR: column "publish_id" is of type integer but expression is of type text Hint: You will need to rewrite or cast the expression. Position: 40

How can I fix that?

http://sqlfiddle.com/#!17/c50f2/4

Upvotes: 0

Views: 251

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

just cast?

UPDATE survey_results SET publish_id = (raw#>>'{survey, publish_id}')::int;

https://www.postgresql.org/docs/current/static/functions-json.html

Get JSON object at specified path as text

so you need to cast text to integer to match column data type

Upvotes: 2

Related Questions