Search code examples
postgresqlpostgresql-10

Resequence (reorder) in column return false

Based on mysql:

SET @ordering_inc = 1;
SET @new_ordering = 0;
UPDATE tasks
SET order = CONCAT("p/", @new_ordering := @new_ordering + @ordering_inc));

Result:

id | order
21 | p/1
32 | p/2
53 | p/3

But in Postgresql not working

DO $$
DECLARE
  ordering_inc integer := 1;
  new_ordering integer := 0;
BEGIN
  UPDATE tasks
  SET order = CONCAT('p/', new_ordering = new_ordering + ordering_inc);
END $$;

Result:

id | order
21 | p/false
32 | p/false
53 | p/false

What am I doing wrong?

Note: I tried using := in the query but give a syntax error

Solution

  • In MySQL, the expression @new_ordering := @new_ordering + @ordering_inc assigns to the variable.

    Postgres, on the other hand, evaluates the expression new_ordering = new_ordering + ordering_inc according to standard SQL: it compares new_ordering to new_ordering + ordering_inc with the equality operator =, which yields the boolean false. When concatenating with concat(), that's coerced to text 'false'.

    For actual variable assignment see:

    But that's not what you need here. There are various ways to assign sequential numbers.

    You could use a (temporary) SEQUENCE for the task:

    CREATE TEMP SEQUENCE foo_seq;
UPDATE tasks
SET    "order" = 'p/' || nextval('foo_seq');

    See:

    To get a serial number in the table column (with arbitrary order), I would just:

    ALTER TABLE tasks
  DROP COLUMN "order"
, ADD COLUMN  "order" serial
;

    And if you don't want an arbitrary sort order, you have to ORDER BY something. Like by ORDER BY id. Using the (standard SQL) window function row_number() to generate the sequence. In a (non-standard) FROM clause to the UPDATE:

    UPDATE tasks t
SET    "order" = t1.rn
FROM  (SELECT id, row_number() OVER (ORDER BY id)  AS rn FROM tasks) t1
WHERE  t.id = t1.id;

    fiddle

    See:

    But you wouldn't want to use the reserved word order as identifier. That's begging for trouble.