Backfill new column with values based on foreign key

by Razzle Shazl   Last Updated October 17, 2018 22:06 PM

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

Tags : postgresql


Related Questions


Postgres gist slow index f_unaccent

Updated April 09, 2018 20:06 PM