Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TYPE status AS ENUM ('Active', 'Failed');

DROP TABLE IF EXISTS Device_Status;

CREATE TABLE Device_Status

(

ID integer,

Status status,

temptime text

);

INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.528');

INSERT INTO Device_Status VALUES (2,'Active','2023-01-13 10.00.01.529');

INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.530');

INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.531');

INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.532');

INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.533');

INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.534');

INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.535');

INSERT INTO Device_Status VALUES (4,'Failed','2023-01-13 10.00.01.536');

INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.537');

insert into device_status values (1,'Failed','2023-01-13 10:00:01.538'),

(1,'Failed','2023-01-13 10:00:01.539'),

(1,'Failed','2023-01-13 10:00:01.540'),

(1,'Failed','2023-01-13 10:00:01.541'),

(1,'Failed','2023-01-13 10:00:01.542'),

(1,'Failed','2023-01-13 10:00:01.543'),

(1,'Failed','2023-01-13 10:00:01.544'),

(1,'Failed','2023-01-13 10:00:01.545'),

(1,'Failed','2023-01-13 10:00:01.546'),

(1,'Failed','2023-01-13 10:00:01.547');

ALTER TABLE Device_Status ADD timestamp TIMESTAMP;

UPDATE Device_Status set timestamp =

to_timestamp(temptime,'YYYY-MM-DD HH:MI:SS.MS');

ALTER TABLE Device_Status DROP COLUMN temptime;

WITH Device_Status_With_Change_Column AS

(

WITH flag AS

(

CREATE TYPE

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 10

ALTER TABLE

UPDATE 20

ALTER TABLE

SELECT 4