Populate rows based on values of previous row and rows group

Question

with postgresSQL 13.6

Following the question from here Add value on one column based on values of a group of rows.

with rows grouped per service_num, is possible to populate the remaining row of that group, using the same text already included in one of those rows.

with the table:

datetime	cart	base	service_num	type
2022 08 23 10:10	aa	OUT	aa/202208/1
2022 08 23 10:20	aa	aa/202208/1
2022 08 23 10:30	aa	IN	aa/202208/1	corporate
2022 08 23 10:05	bb	OUT	bb/202208/1
2022 08 23 10:10	bb	bb/202208/1	private
2022 08 23 10:30	bb	bb/202208/1
2022 08 23 10:35	bb	IN	bb/202208/1
2022 09 23 10:10	aa	OUT	aa/202209/1
2022 09 23 10:20	aa	aa/202209/1	other
2022 09 23 10:30	aa	IN	aa/202209/1

populate like this:

datetime	cart	base	service_num	type
2022 08 23 10:10	aa	OUT	aa/202208/1	corporate
2022 08 23 10:20	aa	aa/202208/1	corporate
2022 08 23 10:30	aa	IN	aa/202208/1	corporate
2022 08 23 10:05	bb	OUT	bb/202208/1	private
2022 08 23 10:10	bb	bb/202208/1	private
2022 08 23 10:30	bb	bb/202208/1	private
2022 08 23 10:35	bb	IN	bb/202208/1	private
2022 09 23 10:10	aa	OUT	aa/202209/1	other
2022 09 23 10:20	aa	aa/202209/1	other
2022 09 23 10:30	aa	IN	aa/202209/1	other

the type wording may appear in any of the rows of each group.

i'm trying to use lag expression with only replicate what is on the previous row on select column

• Lennart - Slava Ukraini: Create a dbfiddle.uk/?rdbms=postgres_13 with the sample data above and update your post with the url
• Lennart - Slava Ukraini: Can't you just update each group with the max or min value for the group?
• domiho1: thanks, will explore that dbfiddle.uk . Max value of the group seems to work just fine!

Answer 1

From the looks of it you can update each group with the max or min value for that group:

UPDATE t t1
 SET type = (SELECT MAX(type) 
 FROM t t2 
 WHERE t2.service_num = t1.service_num); 

Untested.

You may concider moving type to a separate table (since it seems to be dependent of a non key attribute in your current table)

CREATE TABLE service_num_type -- Or whatever this may be 
( service_num text not null primary key
, type text not null -- type is a reserved world
);
INSERT INTO service_num_type
 (service_num, type)
SELECT DISTINCT service_num, type
FROM t
WHERE type is not null;
ALTER TABLE t
 DROP COLUMN type;
ALTER TABLE t ADD CONSTRAINT <constraint name>
 FOREIGN KEY (service_num)
 REFERENCES service_num_type
 (service_num);

• domiho1: thanks! the Max value seems to work really well - it takes some time (10sec) but this query is only to populate that column.
• domiho1: while not fully understand the concept of moving it to a separate table, the initial "type" input is made by hand - could build some automatedly, but many needs to human input to avoid what seems over complicated query to find the type of "type". Having all columns in a single table is seems easier to understand the "type" to be input. a view could be an option, but struggle to make input while on a view.
• Lennart - Slava Ukraini: With your current design you could end up in a situation where someone updates the type for one row - say - aa/202208/1 to corporate and another person updates another row for the same service_num to private.