Geek Answers Handbook

How to convert a character to an integer in a PostgreSQL (9.1) function?

I have the following code:

BEGIN x := split_part(text, ',', 1); UPDATE albumphoto SET order = 1 WHERE idtable = 1 AND idx = x; END

But my column table named idx is a numeric type, and split_part returns the character type in the variable x . I tried using CAST , but I do not know how to use it correctly.

Any ideas?

+6
source share
2 answers

Like this:

 UPDATE albumphoto SET order = 1 WHERE idtable = 1 AND idx = CAST (x AS INTEGER);

(Use the appropriate numeric type instead of INTEGER ).

+9
source

Or easier:

 UPDATE albumphoto SET order = 1 WHERE idtable = 1 AND idx = split_part(text, ',', 1)::int -- or whatever type it is AND order IS DISTINCT FROM 1;

expression::type is a simple (non-SQL standard) Postgres method. See the manual in the Type Casts chapter for more details .
Learn more about data types in PostgreSQL .

The last predicate that I added is useful if order can already be 1 , in which case the update will not change anything, but it will still cost the same. Do not do anything instead. Related (consider the last paragraph):

And you do not need a variable here.

+7
source

More articles:


All Articles