PostgreSQL 11.5 cascade update multi column of one row

Question

I hava one table named device_statistics,it store device info of one app,the table create scrip is:

CREATE TABLE public.device_statistics
(
    id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    abnormalcount integer,
    appid character varying(32) COLLATE pg_catalog."default" NOT NULL,
    inactivecount integer,
    offlinecount integer,
    onlinecount integer,
    statisticstime date,
    totalcount integer,
    CONSTRAINT ods_device_statistics_pkey PRIMARY KEY (id)
)

when a device offline,I must update the offlinecount value,since totalcount=abnormalcount + inactivecount + offlinecount + onlinecount so can I auto update totalcount value when abnormalcount,inactivecount,offlinecount or onlinecount updated.

for example:

before a device offline,the row like(only show we need):

appid  offlinecount totalcount

test        10            32

when a device offline and I update the offlinecount value,I want the follow row like:

appid  offlinecount totalcount
test       9           31

the value of totalcout is auto update,how to do?

richyen · Accepted Answer · 2019-10-28T16:29:56.397

Before PostgreSQL 12, you could instead create a view:

CREATE TABLE public.device_statistics
(
    id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    abnormalcount integer,
    appid character varying(32) COLLATE pg_catalog."default" NOT NULL,
    inactivecount integer,
    offlinecount integer,
    onlinecount integer,
    statisticstime date,
    CONSTRAINT ods_device_statistics_pkey PRIMARY KEY (id)
)

CREATE OR REPLACE VIEW public.device_statistics_with_total AS
SELECT id,
       abnormalcount,
       appid,
       inactivecount,
       offlinecount,
       onlinecount,
       statisticstime,
       abnormalcount + inactivecount + offlinecount + onlinecount AS totalcount
FROM device_statistics;

If you can afford to upgrade to PostgreSQL v. 12, you can use the new generated columns feature:

CREATE TABLE public.device_statistics
(
    id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    abnormalcount integer,
    appid character varying(32) COLLATE pg_catalog."default" NOT NULL,
    inactivecount integer,
    offlinecount integer,
    onlinecount integer,
    statisticstime date,
    totalcount integer GENERATED ALWAYS AS (abnormalcount + inactivecount + offlinecount + onlinecount) STORED,
    CONSTRAINT ods_device_statistics_pkey PRIMARY KEY (id)
)

Disclosure: I work for EnterpriseDB (EDB)

See also: https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql — richyen, Oct 28 '19 at 07:23

PostgreSQL 11.5 cascade update multi column of one row

1 Answers1