I have this 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 the split_part returns a character type to the variable x. I've tried using CAST, but I don't know how to use it properly.
Any ideas?
Like this:
UPDATE albumphoto SET order = 1 WHERE idtable = 1 AND idx = CAST (x AS INTEGER);
(Use appropriate numeric type instead of INTEGER).
Or simpler:
UPDATE albumphoto
SET "order" = 1
WHERE idtable = 1
AND idx = split_part(text, ',', 1)::int -- cast to actual type (if not a string type)
AND "order" IS DISTINCT FROM 1;
expression::type is the simple (non-SQL-standard) Postgres way to cast. Details in the manual in the chapter Type Casts.
More about data types in PostgreSQL.
The last predicate I added is useful if "order" could already be 1, in which case the update wouldn't change anything - at full cost. Rather do nothing instead. Related (see last paragraph):
And you don't need a variable.
