Backfill new column with values based on foreign key I'm new to RDS and am looking for help in updating a new column with ascending values based on a FK.

Can I and should I be doing this as a sequence of SQL commands that calculates my business logic on server-side?  This seems plausible to me because the business logic (ascending based on FK) seems straightforward.  Or should I be running client code to read rows, calculate business logic on client, then write back to db?

Would I be using function to achieve this?

Specifically, I am adding a new column ordinal to my devices table.  The default value is 0 and I would like to update that column to instead count ascending for a particular user.  So, if there are 3 rows for a given user in devices, those 3 rows would respectively have ordinal set to 1, 2, and 3.

CREATE TABLE devices (
    id character varying PRIMARY KEY,
    user_id uuid REFERENCES users(id) ON DELETE CASCADE,
);
ALTER TABLE "devices" ADD COLUMN "ordinal" integer DEFAULT 0 NOT NULL;


I am running PostgreSQL 9.6.6

Thank you



I have made some headway on a function.  For the update step when I set ordinal, is there any way to set the column through the context of the current iteration?  I.e. something like current.ordinal = new_val?

Inspiration: Looping Through Query Results


CREATE OR REPLACE FUNCTION backfill_device_ordinal(uuid) RETURNS integer AS $$
DECLARE
    device RECORD;
    ordinal INTEGER;
BEGIN
    ordinal = 1;
    FOR device IN SELECT * FROM devices WHERE user_id = $1 LOOP
        -- Now "device" has one record from "devices"
        RAISE NOTICE 'Updating device ordinal: user=%, ordinal=%...', device.user_id, device.seat_ordinal;

        /* is this optimal? */
        EXECUTE format('UPDATE devices SET seat_ordinal = %I WHERE user_id = "%s" AND id = "%s"', ordinal, device.user_id, device.id);

        ordinal = ordinal + 1;
    END LOOP;

    RAISE NOTICE 'Done updating device ordinal.';
    RETURN ordinal;
END;
$$ LANGUAGE plpgsql;

SELECT * from get_next_device('00000000-1111-2222-3333-444444444444')
You can use a correlated subquery to update all rows at once.
To determine the ordinal value, just count how many other rows for the same user come before this one when you sort them:

UPDATE devices
SET seat_ordinal = (SELECT COUNT(*)
                    FROM devices AS d2
                    WHERE d2.user_id  = devices.user_id
                      AND d2.id      &lt;= devices.id);    -- some arbitrary sort order