PostgreSQL - copying numeric value from jsonb field to integer column

Question

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

Vao Tsun · Accepted Answer · 2018-02-19 11:41:55Z

2

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

answered Feb 19, 2018 at 11:41

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - copying numeric value from jsonb field to integer column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related