Update row data with dynamic lag

Question

I have a table of items and restaurants. We assume that if the restaurant entry is missing, it has to be completed with the last non-null restaurant value in the previous rows (as defined by ascending id values).

CREATE TABLE food_items (
 id SERIAL PRIMARY KEY,
 item VARCHAR(255) NOT NULL,
 restaurant VARCHAR(255)
);
INSERT INTO food_items(item, restaurant) 
VALUES ('Salad','McDonalds'), ('Burger',''),('Fries',''),('Salad','Taco Bell'),('Quesadilla','');

The tricky part is that the number of rows for each restaurant is variable, so it cannot be captured with a fixed lag.

• mustaccio: Please consider following these suggestions.
• Frank Heikens: That's not how a database works since records don't have an order without an order by in your SELECT statement. There is no relation between records if you don't create that relation. It's the responsibility of your front end to set the correct content for your INSERT statements. In your case you should prohibit empty strings '' and add a not null constraint to the restaurant column. A better database model would be nice as well, you shouldn't store 20.000 times the value "McDonalds", just google 3NF and improve your model
• eddie: @Frank Heikens The records have an order with the id serial primary key. The challenge is the variable lag window.
• Erwin Brandstetter: So do you have your answer?

Answer 1

You seem to be confounding empty string ('') and null. Both are very much distinct.

Assuming null values where your example shows empty strings.

UPDATE food_items f
SET restaurant = f1.r1
FROM (
 SELECT id, first_value(restaurant) OVER (PARTITION BY grp ORDER BY id) AS r1 
 FROM (
 SELECT *, count(restaurant) over (ORDER BY id) AS grp
 FROM food_items
 ) sub
 ) f1
WHERE f.id = f1.id
AND f.restaurant IS NULL; -- avoid empty updates

fiddle

See:

• Carry over long sequence of missing values with Postgres
• Select most recent non-null value for multiple columns

About avoiding empty updates:

• How do I (or can I) SELECT DISTINCT on multiple columns?