SQLize Online / PHPize Online  /  SQLtest Online

CREATE EXTENSION hstore; DROP TABLE IF EXISTS tmp_h_sometable; CREATE TABLE tmp_h_sometable ( h hstore NULL, dt_created timestamp NULL DEFAULT 'now'::text::timestamp without time zone, is_init bool NULL ); INSERT INTO tmp_h_sometable (h,dt_created,is_init) VALUES ('"pos"=>"202103081156460559467695", "_ID"=>"1", "op_ts"=>"2021-03-08 08:56:46.388", "table"=>"sometable", "MAJDAT"=>"20210305", "MAJHMS"=>"115646", "op_type"=>"U"','2021-03-15 14:37:09.033001',NULL), ('"pos"=>"202103081206370559640756", "_ID"=>"2", "op_ts"=>"2021-03-08 09:06:37.280", "table"=>"sometable", "MAJDAT"=>"20210308", "MAJHMS"=>"120637", "op_type"=>"D"','2021-03-15 14:51:34.173244',NULL), ('"pos"=>"202103081212350559718333", "_ID"=>"3", "op_ts"=>"2021-03-08 09:12:35.368", "table"=>"sometable", "MAJDAT"=>"20210107", "MAJHMS"=>"121235", "op_type"=>"U"','2021-03-15 15:13:11.715818',NULL), ('"pos"=>"202103081208080559650865", "_ID"=>"2", "op_ts"=>"2021-03-08 09:08:08.358", "table"=>"sometable", "MAJDAT"=>"20210309", "MAJHMS"=>"120808", "op_type"=>"U"','2021-03-15 15:18:45.857119',NULL), ('"pos"=>"202103081206370559640764", "_ID"=>"2", "op_ts"=>"2021-03-08 09:06:37.282", "table"=>"sometable", "MAJDAT"=>"20210303", "MAJHMS"=>"120637", "op_type"=>"I"','2021-03-15 15:29:43.868558',NULL), ('"pos"=>"202103081213480559740300", "_ID"=>"3", "op_ts"=>"2021-03-08 09:13:48.156", "table"=>"sometable", "MAJDAT"=>"20210305", "MAJHMS"=>"21348", "op_type"=>"D"','2021-03-15 15:34:11.050351',NULL), ('"pos"=>"202103081155380559443438", "_ID"=>"1", "op_ts"=>"2021-03-08 08:55:38.698", "table"=>"sometable", "MAJDAT"=>"20210208", "MAJHMS"=>"115538", "op_type"=>"I"','2021-03-15 14:35:37.133757',NULL), ('"pos"=>"202103081156460559467716", "_ID"=>"1", "op_ts"=>"2021-03-08 08:56:46.395", "table"=>"sometable", "MAJDAT"=>"20210307", "MAJHMS"=>"15646", "op_type"=>"U"','2021-03-15 14:56:21.570984',NULL), ('"pos"=>"202103081207590559650549", "_ID"=>"4", "op_ts"=>"2021-03-08 09:07:59.823", "table"=>"sometable", "MAJDAT"=>"20210208", "MAJHMS"=>"20759", "op_type"=>"I"','2021-03-15 14:56:21.570984',NULL), ('"pos"=>"202103081205400559617061", "_ID"=>"4", "op_ts"=>"2021-03-08 09:05:40.488", "table"=>"sometable", "MAJDAT"=>"20210301", "MAJHMS"=>"120540", "op_type"=>"I"','2021-03-15 15:29:33.790932',NULL); SELECT * FROM tmp_h_sometable; SELECT * FROM (SELECT _id, table_name, ts, op_ts, op_type, row_number()over(partition by _id, table_name order by op_ts) as rn FROM (SELECT (tmp_h_sometable.h -> '_ID') as _id, (tmp_h_sometable.h -> 'table') as table_name, (tmp_h_sometable.h -> 'op_ts') as op_ts, to_timestamp((tmp_h_sometable.h -> 'MAJDAT') || lpad((tmp_h_sometable.h -> 'MAJHMS'),6,'0'),'yyyymmddhh24miss')::timestamptz as ts, (tmp_h_sometable.h -> 'op_type') as op_type FROM tmp_h_sometable) tmp1) tmp2 WHERE rn = 1 or op_type<>'D';