1

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

3

1 Answer 1

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);
3
  • thanks! the Max value seems to work really well - it takes some time (10sec) but this query is only to populate that column.
    – domiho1
    Aug 25, 2022 at 13:21
  • 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.
    – domiho1
    Aug 25, 2022 at 13:29
  • 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. Aug 25, 2022 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.