CODEWITHSUNDEEP
postgresqlsql-update
Jackson
Jackson

Reputation: 405

Is there a better way to do update PostgreSQL

I'm trying to update the ended_at and active columns in the test_subscription table when the max period_end has not passed.

I'm using the below query but I doubt it's the most idiomatic way. Any suggestions on improvements are very much welcome.

Creating the tables:

CREATE TABLE test_subscription (
   id INTEGER PRIMARY key,
   started_at timestamp,
   ended_at TIMESTAMP,
   active boolean
);
CREATE TABLE test_invoice (
   id INTEGER PRIMARY key,
   subscription_id INTEGER,
   period_start timestamp,
   period_end timestamp
);

INSERT INTO test_subscription (id, started_at, ended_at, active)
    values(1, '2017-01-01 00:00:00', NULL, TRUE);

INSERT INTO test_subscription (id, started_at, ended_at, active)
    values(2, '2017-01-01 00:00:00', NULL, TRUE);

INSERT INTO test_invoice (id, subscription_id, period_start, period_end)
    values(1, 1, '2017-01-01 00:00:00', '2017-12-01 00:00:00');

INSERT INTO test_invoice (id, subscription_id, period_start, period_end)
    values(2, 1, '2017-12-02 00:00:00', '2019-12-01 00:00:00');

INSERT INTO test_invoice (id, subscription_id, period_start, period_end)
    values(3, 2, '2017-01-01 00:00:00', '2017-12-01 00:00:00');

I'm updating using the below. 

UPDATE test_subscription
    SET ended_at = (CASE WHEN (SELECT
                                   MAX(period_end)
                                FROM test_invoice
                                WHERE test_subscription.id = test_invoice.subscription_id
                               ) < now()
                          THEN (SELECT MAX(period_end)
                                FROM test_invoice
                                WHERE test_subscription.id = test_invoice.subscription_id
                               )
                          ELSE NULL
                     end),
       active = (CASE WHEN (SELECT MAX(period_end)
                              FROM test_invoice
                              WHERE test_subscription.id = test_invoice.subscription_id
                             ) < now()
                       THEN TRUE
                       ELSE FALSE
                  end);

Upvotes: 1

Views: 69

Answers (2)

user330315
user330315

Reputation:

Updates like that are usually faster if you first collect all the aggregates, then run the update using that intermediate result. Co-related sub-queries tend to be much slower.

update test_subscription s
  set ended_at = case when t.latest_end < current_timestamp then t.latest_end end,
      active = t.latest_end < current_timestamp
  from (
    select s.id,
           max(i.period_end) as latest_end
    from test_subscription s 
      join test_invoice i on s.id = i.subscription_id
    group by s.id
   ) t 
where t.id = s.id;

Online example: http://rextester.com/NMMF41667

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

You can put the MAX within CASE

UPDATE test_subscription s 
SET  ( ended_at, active ) = (SELECT MAX(CASE 
                                            WHEN period_end < NOW() THEN 
                                            period_end 
                                          END), 
                                      MAX(CASE 
                                            WHEN period_end < NOW() THEN 'TRUE' 
                                            ELSE 'FALSE' 
                                          END) :: BOOLEAN 
                               FROM   test_invoice i 
                               WHERE  s.id = i.subscription_id);

Demo

Upvotes: 0

Related Questions