postgres update text column value into integer column

Question

I have a PostgreSQL data-table with following data

id    assignmentId    folderId    folderId_new
1     2274            5332
2     2274            5348
3     2274            5349
4     2274            5350
46    2277            5355
75    2275            5351
76    2275            5352
77    2275            5353
78    2275            5354
...

and columns datatype

id                serial
assignmentId      integer
folderId          text
folderId_new      integer

I want to save the values of folderId column to folderId_new column.
I have tried following queries

update framethreshold set folderId_new = CAST (folderId AS integer)

update framethreshold set folderId_new = folderId::int

but results in the error:

ERROR: invalid input syntax for integer: "" SQL state: 22P02

ERROR: invalid input syntax for integer: "" ********** Error ********** ERROR: invalid input syntax for integer: "" SQL state: 22P02 — Vijay Dohare
– Vijay Dohare, Commented May 20, 2016 at 11:15

manish1706 · Accepted Answer · 2016-05-20 11:12:44Z

1

Ok, so you can write this query

update framethreshold set folderId_new = NULLIF(folderId, '')::int

hope this will work ;)

answered May 20, 2016 at 11:12

manish1706

1,60925 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

postgres update text column value into 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