Postgres 12

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();

CREATE TABLE public.users (
	id serial NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	firstname text NOT NULL,
	lastname text NOT NULL,
	username text NOT NULL,
	"password" text NOT NULL,
	country_id int4 NULL,
	confirmed_at timestamptz NULL,
	email text NOT NULL,
	reset_token text NULL,
	token_expires_at timestamp NULL,
	api_id text NULL,
	api_key text NULL,
	is_admin bool NOT NULL DEFAULT false,
	last_login_at timestamptz NULL,
	payment_method int4 NULL DEFAULT 0,
	encrypted_password varchar NULL DEFAULT ''::character varying,
	sign_in_count int4 NOT NULL DEFAULT 0,
	last_sign_in_at timestamp NULL,
	last_sign_in_ip inet NULL,
	referral_url varchar(255) NULL,
	company_id int8 NULL,
	linkedin_url text NULL,
	twitter_url text NULL,
	telephone text NULL,
	persona text NULL,
	zoho_id varchar(255) NULL,
	session_count int4 NULL DEFAULT 0,
	last_seen_at timestamptz NULL,
	last_session_started_at timestamptz NULL,
	CONSTRAINT pk_users PRIMARY KEY (id)
);

CREATE TABLE public.cameras (
	id serial NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now(),
	exid text NOT NULL,
	owner_id int4 NOT NULL,
	is_public bool NOT NULL,
	config json NOT NULL,
	"name" text NOT NULL,
	last_polled_at timestamptz NULL DEFAULT now(),
	is_online bool NULL,
	timezone text NULL,
	last_online_at timestamptz NULL DEFAULT now(),
	"location" geography NULL,
	mac_address macaddr NULL,
	model_id int4 NULL,
	discoverable bool NOT NULL DEFAULT false,
	thumbnail_url text NULL,
	is_online_email_owner_notification bool NOT NULL DEFAULT false,
	alert_emails text NULL,
	offline_reason varchar(255) NULL,
	project_id int8 NULL,
	sim_id int8 NULL,
	ptz bool NOT NULL DEFAULT false,
	channel int4 NULL,
	CONSTRAINT pk_streams PRIMARY KEY (id)
);
CREATE UNIQUE INDEX cameras_exid_index ON public.cameras USING btree (exid);
CREATE INDEX cameras_mac_address_index ON public.cameras USING btree (mac_address);
CREATE INDEX ix_streams_owner_id ON public.cameras USING btree (owner_id);

ALTER TABLE public.cameras ADD CONSTRAINT cameras_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES users(id);

CREATE TABLE public.archives (
	id serial NOT NULL,
	camera_id int4 NOT NULL,
	exid text NOT NULL,
	title text NOT NULL,
	from_date timestamptz NOT NULL,
	to_date timestamptz NOT NULL,
	status int4 NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	requested_by int4 NOT NULL,
	embed_time bool NULL,
	public bool NULL,
	frames int4 NULL DEFAULT 0,
	url varchar(255) NULL,
	file_name varchar(255) NULL,
	"type" varchar(255) NULL,
	error_message text NULL,
	CONSTRAINT archives_pkey PRIMARY KEY (id)
);

ALTER TABLE public.archives ADD CONSTRAINT archives_camera_id_fkey FOREIGN KEY (camera_id) REFERENCES cameras(id);
ALTER TABLE public.archives ADD CONSTRAINT archives_requested_by_fkey FOREIGN KEY (requested_by) REFERENCES users(id);

CREATE TABLE public.timelapses (
	id serial NOT NULL,
	camera_id int4 NOT NULL,
	exid varchar(255) NOT NULL,
	title varchar(255) NOT NULL,
	frequency int4 NOT NULL,
	snapshot_count int4 NULL DEFAULT 0,
	status int4 NOT NULL,
	date_always bool NULL DEFAULT false,
	from_datetime timestamp NULL,
	time_always bool NULL DEFAULT false,
	to_datetime timestamp NULL,
	watermark_logo text NULL,
	watermark_position varchar(255) NULL,
	inserted_at timestamp NOT NULL,
	updated_at timestamp NOT NULL,
	user_id int4 NOT NULL,
	extra json NULL,
	CONSTRAINT timelapses_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX timelapse_exid_unique_index ON public.timelapses USING btree (exid);

ALTER TABLE public.timelapses ADD CONSTRAINT timelapses_camera_id_fkey FOREIGN KEY (camera_id) REFERENCES cameras(id);
ALTER TABLE public.timelapses ADD CONSTRAINT timelapses_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);

CREATE TABLE public.compares (
	id serial NOT NULL,
	exid varchar(255) NOT NULL,
	"name" varchar(255) NOT NULL,
	before_date timestamp NOT NULL,
	after_date timestamp NOT NULL,
	embed_code varchar(255) NOT NULL,
	camera_id int4 NOT NULL,
	inserted_at timestamp NOT NULL,
	updated_at timestamp NOT NULL,
	create_animation bool NULL DEFAULT false,
	status int4 NOT NULL DEFAULT 0,
	requested_by int4 NOT NULL,
	public bool NULL DEFAULT true,
	CONSTRAINT compares_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX compare_exid_unique_index ON public.compares USING btree (exid);

ALTER TABLE public.compares ADD CONSTRAINT compares_camera_id_fkey FOREIGN KEY (camera_id) REFERENCES cameras(id);
ALTER TABLE public.compares ADD CONSTRAINT compares_requested_by_fkey FOREIGN KEY (requested_by) REFERENCES users(id);

INSERT INTO public.users (created_at,updated_at,firstname,lastname,username,"password",country_id,confirmed_at,email,reset_token,token_expires_at,api_id,api_key,is_admin,last_login_at,payment_method,encrypted_password,sign_in_count,last_sign_in_at,last_sign_in_ip,referral_url,company_id,linkedin_url,twitter_url,telephone,persona,zoho_id,session_count,last_seen_at,last_session_started_at) VALUES
	 ('2017-10-16 15:32:14.150934+05','2020-12-15 23:05:47.77491+05','FRANCESCO','INTOCCIA','fintocci','$2a$12$xIv',100,'2017-10-16 15:32:13+05','fintocci@its.jnj.com',NULL,NULL,'8b088a57','5854c21934544d70b5286d7eb0868876',false,'2018-05-04 21:22:11.992139+05',2,'jLnAKVO',0,NULL,NULL,NULL,2907,'https://www.linkedin.com/in/francesco-intoccia-6379431b',NULL,NULL,NULL,'432169000008650488',9,NULL,NULL),
	 ('2020-10-28 17:41:08.692263+05','2021-01-07 21:39:38.464816+05','Reuben','Kirrane','rkirrane@deniswilliams.ie','$2b$12$erwTBqq5fk9Vp',100,'2020-10-28 17:41:08.479745+05','rkirrane@deniswilliams.ie',NULL,'2020-11-30 09:53:53.671081','cbc9e072','263ea96c0bb54bbfac869f82b0021f62',false,'2021-01-07 21:39:37.184381+05',0,'',4,NULL,'86.45.26.197'::inet,'fionn@evercam.io shared camera cgdmc-piqer',3865,NULL,NULL,'+353860294681','HQ','432169000051419055',5,'2021-01-07 21:39:38.436134+05','2021-01-07 21:39:38.436134+05'),
	 ('2020-03-26 22:55:47.901869+05','2021-04-19 14:39:11.327593+05','Bartosz','Borowiak','b.borowiak@waterman-moylan.ie','$2b$12$LvXTrKK',100,'2020-03-26 22:55:47.696433+05','b.borowiak@waterman-moylan.ie',NULL,NULL,'6a7f437a','943e0653a4234acdbf1cf20d8b3f4ae5',false,'2021-04-19 14:39:06.116871+05',0,'',19,NULL,'188.141.50.10'::inet,'construction@evercam.io shared camera irish-tiera',2797,NULL,NULL,NULL,'HQ','432169000036041083',49,'2021-04-19 14:39:11.327482+05','2021-04-19 14:39:11.327482+05'),
	 ('2018-05-17 16:42:28.225984+05','2020-12-15 23:05:36.415438+05','Paul','Braham','paul.braham@hse.ie','$2a$10$U.U',100,'2018-05-17 16:42:27+05','paul.braham@hse.ie','3e9a7089ffc8f31a0ed2ba6a1bf88259','2018-05-19 09:17:36.860184','3947ce36','ce3bb16fb1bd4e4cb32b5288182fadcf',false,'2018-06-08 18:40:03.912919+05',2,'$2a$10$.lEeMFK',0,NULL,NULL,NULL,2665,'https://www.linkedin.com/in/paul-braham-3b5a6795',NULL,NULL,NULL,'432169000009677055',5,NULL,NULL),
	 ('2018-07-05 14:49:47.797514+05','2020-12-15 23:05:33.503511+05','Liam','Rahilly','lrahilly@meicltd.com','$2a$12$KBNDJBBdUDVOjcBqErU',100,'2018-07-05 14:49:47+05','lrahilly@meicltd.com',NULL,NULL,'4d380adf','28b1a2c4f8824f87b527927e4afbb559',false,'2018-07-05 19:04:45.98841+05',2,'$2a$/y4WJXhKRnX1O',0,NULL,NULL,NULL,2842,NULL,NULL,NULL,NULL,'432169000020553441',2,NULL,NULL),
	 ('2018-06-07 21:01:59.802852+05','2021-01-25 22:36:45.697583+05','Ricky','Fretwell','rick.fretwell@gmail.com','$2a$12$3Cz9kZvKZyo9RTe',75,'2018-06-07 21:01:59+05','rick.fretwell@gmail.com',NULL,NULL,'3016bd04','93b944226c77415c80c63dadb31f8b55',false,'2018-10-12 18:58:53.005926+05',2,'$2asgdsgds',0,NULL,NULL,NULL,3011,NULL,NULL,NULL,'PM','432169000009123036',15,NULL,NULL),
	 ('2018-09-11 14:33:32.681788+05','2021-01-13 16:50:52.429029+05','Site Manager','DHL','sandgate@developmenthouseltd.co.uk','$2a$10$wra',75,'2018-09-11 14:33:32+05','sandgate@developmenthouseltd.co.uk',NULL,NULL,'26699043','bab8d1e53a95494984c2790c225bb207',false,'2018-12-04 19:35:12.373132+05',2,'$2a$10$w',2,'2019-01-28 07:49:22.924148','212.140.202.116'::inet,NULL,2645,NULL,NULL,NULL,'PM','432169000013651003',5,NULL,NULL),
	 ('2018-05-25 15:18:47.255188+05','2021-02-09 21:57:09.810436+05','tommy','macken','tmacken@rhatiganohl.com','$2a$12$8mR9GQ3r2iJcted6kOg',100,'2018-05-25 15:18:47+05','tmacken@rhatiganohl.com',NULL,NULL,'7f12fcb2','83cf65a01d4b495d91165ad2b605fd45',false,'2021-02-09 21:57:09.810267+05',2,'$2a$12$8mR9GQ3r.cD1/',1,NULL,'89.100.126.110'::inet,NULL,2834,NULL,NULL,NULL,NULL,'432169000007088001',3,NULL,NULL),
	 ('2018-09-27 16:08:41.721788+05','2020-12-15 23:05:27.060385+05','Henry','Scanlon','hscanlon@johnpaul.ie','$2b$12$',100,'2018-09-27 16:08:41+05','hscanlon@johnpaul.ie','3d4f89d42235416693db9149dff4bc09','2020-12-05 23:50:17.877408','c05d6a81','dd8367b40efd40f78ff0598fcfa249e4',false,'2020-12-05 04:51:04.298163+05',2,'$2a$12.',3,NULL,'89.19.67.31'::inet,NULL,2541,NULL,NULL,NULL,'PM','432169000020565181',4,NULL,NULL),
	 ('2021-03-01 20:22:04.236702+05','2021-03-01 20:22:08.816447+05','Daniela','Enkelmann','denkelmann@kohlerronan.com','$2b$',228,'2021-03-01 20:22:04.009769+05','denkelmann@kohlerronan.com',NULL,NULL,'4a840732','011cf8035f56419f955bd4db7be802f6',false,'2021-03-01 20:22:05.091482+05',0,'',0,NULL,'69.195.32.10'::inet,'construction@evercam.io shared camera millr-psbjc',4116,NULL,NULL,NULL,'HQ','432169000063554073',1,'2021-03-01 20:22:06.213188+05','2021-03-01 20:22:06.213188+05');

INSERT INTO public.cameras (created_at,updated_at,exid,owner_id,is_public,config,"name",last_polled_at,is_online,timezone,last_online_at,"location",mac_address,model_id,discoverable,thumbnail_url,is_online_email_owner_notification,alert_emails,offline_reason,project_id,sim_id,ptz,channel) VALUES
	 ('2021-04-21 16:42:27.363367+05','2021-04-21 16:43:20.770069+05','siskm-guhev',1,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"dsd.io","external_http_port":"50204","external_rtsp_port":"50202","nvr_http_port":"50201","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Sisk Mater 1','2021-04-21 13:46:29.405674+05',NULL,'Europe/Dublin','2021-04-21 13:46:29.405715+05','SRID=4326;POINT (-6.265669 53.3598586)'::geography,NULL,3318,false,NULL,false,NULL,NULL,720,366,false,101),
	 ('2021-02-09 19:00:04.038143+05','2021-04-19 11:06:28.755652+05','stens-wjcyf',2,false,'{"auth":{"basic":{"password":"Merlin53","username":"admin"}},"external_host":"dsdfs.io","external_http_port":"20804","external_rtsp_port":"20802","nvr_http_port":"20801","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Stenson Court','2021-03-11 13:33:36+05',NULL,'Europe/London','2021-03-11 12:45:22+05','SRID=4326;POINT (-1.1541171 53.5098615)'::geography,NULL,3318,false,NULL,false,NULL,NULL,580,673,false,101),
	 ('2021-03-23 01:47:53.639515+05','2021-04-19 11:06:29.300114+05','dslrf-bxtgj',3,false,'{"auth":{"basic":{"password":"afz9XKqYLuExCKZu","username":"evercam_admin"}},"external_host":"dsd.io","external_http_port":"80","external_rtsp_port":"1111","nvr_http_port":"1111","urls":{"jpg":"evercam.io"}}','DSLR FTP','2021-03-22 15:55:00.648167+05',NULL,'Etc/UTC','2021-03-22 15:55:00.648188+05','SRID=4326;POINT (0 0)'::geography,NULL,151,false,NULL,false,NULL,NULL,211,573,false,NULL),
	 ('2021-02-12 18:56:18.812258+05','2021-04-19 11:06:29.205915+05','norma-cfnbk',4,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"185.233.176","external_http_port":"11079","external_rtsp_port":"11080","nvr_http_port":"11079","urls":{"h264":"/ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture?videoResolutionWidth=1920&videoResolutionHeight=1080"}}','Normal Test Camera','2021-03-16 13:10:33+05',NULL,'Europe/Dublin','2021-03-16 13:10:13+05','SRID=4326;POINT (0 0)'::geography,NULL,3317,false,NULL,false,NULL,NULL,586,NULL,false,101),
	 ('2021-03-15 19:49:35.166558+05','2021-04-19 11:06:28.82161+05','jubil-tjlmk',5,false,'{"auth":{"basic":{"password":"Permanex1","username":"admin"}},"external_host":"dsd.io","external_http_port":"20839","external_rtsp_port":"20837","nvr_http_port":"20836","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Jubilee Phase 2','2021-03-15 13:55:43.355517+05',NULL,'Europe/London','2021-03-15 13:55:43.355538+05','SRID=4326;POINT (-0.0866958 51.50490790000001)'::geography,NULL,3318,false,NULL,false,NULL,NULL,656,705,false,101),
	 ('2019-11-01 18:54:31.367507+05','2021-04-19 11:06:28.884481+05','cairn-fegtm',6,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"citywest.dyndns","external_http_port":"65001","external_rtsp_port":"9100","nvr_http_port":"8100","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Cairn Homes Citywest','2020-11-11 21:38:38+05',NULL,'Europe/Dublin','2020-11-11 21:38:38+05','SRID=4326;POINT (-6.4285886 53.2849647)'::geography,NULL,3318,false,NULL,false,NULL,'nxdomain',154,NULL,false,101),
	 ('2019-08-06 17:25:35.801228+05','2021-04-19 11:06:29.635423+05','colog-ywtlq',7,false,'{"auth":{"basic":{"password":"DT$8nK!86","username":"tw"}},"external_host":"220.35","external_http_port":"8001","external_rtsp_port":"9001","nvr_http_port":"","urls":{"h264":"axis-media/media.amp?resolution=800x600","jpg":"axis-cgi/jpg/image.cgi"}}','Cologne 1','2021-04-07 15:14:11+05',true,'Europe/Berlin','2021-04-07 15:13:11+05','SRID=4326;POINT (6.909336 50.9484714)'::geography,NULL,4,false,NULL,false,NULL,NULL,NULL,NULL,false,NULL),
	 ('2019-07-08 18:23:24.593418+05','2021-04-19 11:06:28.943171+05','graha-wkiaj',8,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"dsdsd.io","external_http_port":"20254","external_rtsp_port":"20252","nvr_http_port":"20251","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','Graham Drumaroad','2021-02-27 01:42:09+05',true,'Europe/Dublin','2021-02-27 01:41:18+05','SRID=4326;POINT (-5.8936402 54.3329881)'::geography,'4c:bd:8f:26:f0:16'::macaddr,3318,false,NULL,false,NULL,NULL,249,234,false,101),
	 ('2021-01-15 21:29:09.642408+05','2021-04-19 11:06:29.419438+05','polop-pcsdm',9,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"185.176.121","external_http_port":"11190","urls":{"h264":"/ISAPI/Streaming/channels/2401/av_stream","jpg":"ISAPI/Streaming/channels/2401/picture?videoResolutionWidth=1920&videoResolutionHeight=1080"}}','Polo Parking','2021-01-18 11:39:01+05',NULL,'Europe/Dublin','2021-01-18 11:38:37+05','SRID=4326;POINT (0 0)'::geography,NULL,3317,false,NULL,false,NULL,NULL,17,NULL,false,2401),
	 ('2021-04-20 19:21:10.799916+05','2021-04-21 11:09:08.369512+05','bmdda-dzlfx',10,false,'{"auth":{"basic":{"password":"dsgdsgdsg","username":"admin"}},"external_host":"dsdsd.io","external_http_port":"51104","external_rtsp_port":"50102","nvr_http_port":"50101","urls":{"h264":"ISAPI/Streaming/channels/101/av_stream","jpg":"ISAPI/Streaming/channels/101/picture"}}','BMD Davitt Rd TC1-1','2021-04-20 13:58:35.571222+05',NULL,'Europe/London','2021-04-20 13:58:35.571258+05','SRID=4326;POINT (-6.3155058 53.3355761)'::geography,NULL,3318,false,NULL,false,NULL,NULL,719,409,false,101);

INSERT INTO public.timelapses (camera_id,exid,title,frequency,snapshot_count,status,date_always,from_datetime,time_always,to_datetime,watermark_logo,watermark_position,inserted_at,updated_at,user_id,extra) VALUES
	 (1,'longp-mvxla','Long Ponds',60,6767,3,true,'2016-08-29 00:00:00',true,'2016-08-29 23:59:59','','TopLeft','2017-05-18 08:16:13','2017-07-13 04:45:45.174592',2,'{"id":798}'),
	 (2,'basem-hfmjp','Basement Today',15,360,6,true,'2019-01-22 10:00:00',true,'2019-01-22 11:00:00','none',NULL,'2019-01-22 11:49:46.333172','2019-01-22 11:49:46.333183',3,NULL),
	 (3,'3pp-cloxd','3PP',30,7982,3,true,'1900-01-01 08:00:00',false,'8888-12-31 19:00:00','','TopLeft','2017-05-18 08:16:12','2017-07-12 17:25:45.413265',4,'{"id":717}'),
	 (4,'digit-rgblx','Digital HUb',15,6804,3,true,'1900-01-01 06:00:00',false,'8888-12-31 19:00:00','','TopLeft','2017-05-18 08:16:10','2017-05-18 08:16:10',1,'{"id":260}'),
	 (5,'demo-jvnzg','Demo',360,768,3,true,'1900-01-01 00:00:00',true,'8888-12-31 23:59:59','','TopLeft','2017-05-18 08:16:10','2017-05-18 08:16:10',2,'{"id":559}');

INSERT INTO public.compares (exid,"name",before_date,after_date,embed_code,camera_id,inserted_at,updated_at,create_animation,status,requested_by,public) VALUES
	 ('compa-ylxwctg','Compare: 8th Sep to 29th Oct','2017-09-08 14:35:25','2018-10-29 05:51:47','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''iaaco-auftq 2017-09-08-14_35_25 2018-10-29-05_51_47 compa-ylxwctg autoplay''></script>',1,'2018-10-29 05:52:09.659566','2018-10-29 05:52:15.391303',true,1,2,true),
	 ('test-hroqe','test ','2017-06-21 13:20:55','2018-01-17 10:27:58','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''point-iytnz 2017-06-21-13_20_55 2018-01-17-10_27_58 test-hroqe autoplay''></script>',2,'2018-01-17 11:08:19.845168','2018-04-03 05:19:54.360234',true,1,3,true),
	 ('compa-ltafnon','Compare: 26th Jul to 29th Oct','2018-07-26 14:23:00','2018-10-29 05:37:00','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''crome-tidcz 2018-07-26-14_23_00 2018-10-29-05_37_00 compa-ltafnon autoplay''></script>',3,'2018-10-29 05:53:39.989565','2018-10-29 05:53:44.78214',true,1,2,true),
	 ('compa-tjhacxd','Compare: 23rd Jan to 27th Oct','2018-01-23 13:37:55','2018-10-27 12:07:22','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''scotc-rpfbq 2018-01-23-13_37_55 2018-10-27-12_07_22 compa-tjhacxd autoplay''></script>',1,'2018-10-29 05:56:52.67979','2018-10-29 05:56:58.646538',true,1,2,true),
	 ('compa-vztxtjc','Compare: 18th Jul to 1st Nov','2015-07-18 15:07:00','2018-11-01 00:05:43','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''bennett 2015-07-18-15_07_00 2018-11-01-00_05_43 compa-vztxtjc autoplay''></script>',2,'2018-11-15 10:03:24.85408','2018-11-15 10:03:33.140659',true,1,4,true),
	 ('compa-momncjm','Compare: 11th Oct to 19th Nov','2018-10-11 11:10:51','2018-11-19 15:26:56','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''leins-ugxco 2018-10-11-11_10_51 2018-11-19-15_26_56 compa-momncjm autoplay''></script>',1,'2018-11-19 15:28:52.359459','2018-11-19 15:29:04.35427',true,1,1,true),
	 ('compa-cockhyp','Compare: 11th Jan to 19th Dec','2018-01-11 14:36:19','2018-12-19 14:12:09','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''mscpi-pkjwh 2018-01-11-14_36_19 2018-12-19-14_12_09 compa-cockhyp autoplay''></script>',4,'2018-12-19 14:13:35.856163','2018-12-19 14:13:42.713771',true,1,5,true),
	 ('itsac-ihtlze','Its a compare','2016-03-19 05:21:12','2020-01-01 08:45:34','<div id=''evercam-compare''></div><script src=''https://dash2.evercam.io/evercam_compare.js'' class=''gpocam 2016-03-19-05_21_12 2020-01-01-08_45_34 itsac-ihtlze autoplay''></script>',4,'2020-01-01 08:46:23.172885','2020-01-01 08:46:28.088856',true,1,1,true),
	 ('compa-kycgmav','Compare: 1st Nov to 5th Apr','2018-11-01 10:11:04','2019-04-05 11:04:02','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''south-ragcn 2018-11-01-10_11_04 2019-04-05-11_04_02 compa-kycgmav autoplay''></script>',1,'2019-04-08 14:54:03.809314','2019-04-08 14:54:10.807723',true,1,2,true),
	 ('compa-kdxjbhh','Compare: 4th Dec to 14th Dec','2018-12-04 16:30:03','2018-12-14 16:12:04','<div id=''evercam-compare''></div><script src=''https://dash.evercam.io/assets/evercam_compare.js'' class=''still-bojft 2018-12-04-16_30_03 2018-12-14-16_12_04 compa-kdxjbhh autoplay''></script>',2,'2018-12-14 22:25:00.735079','2018-12-14 22:25:08.440506',true,1,4,true);

INSERT INTO public.archives (camera_id,exid,title,from_date,to_date,status,created_at,requested_by,embed_time,public,frames,url,file_name,"type",error_message) VALUES
	 (1,'concre-r6ys','Concrete Pour','2017-04-05 18:25:05+05','2017-04-05 18:55:05+05',2,'2017-04-05 19:46:38+05',1,false,false,840,NULL,NULL,'clip',NULL),
	 (1,'201809-27xn','2018-09-26T10:00:04.000Z','2018-09-26 14:00:04+05','2018-09-26 14:00:04+05',2,'2018-11-27 21:05:01.813853+05',1,NULL,NULL,0,NULL,'201809-27xn.jpeg','edit',NULL),
	 (4,'201902-htu9','2019-02-01T10:13:47.000Z','2019-02-01 15:13:47+05','2019-02-01 15:13:47+05',2,'2019-02-01 15:14:56+05',1,NULL,NULL,0,NULL,'201902-htu9.jpeg','edit',NULL),
	 (5,'extrac-png8','Extract','2017-04-12 21:25:00+05','2017-04-12 21:40:00+05',2,'2017-04-18 13:50:42+05',5,true,false,853,NULL,NULL,'clip',NULL),
	 (2,'joecli-kvpw','joe clip','2017-04-19 20:10:00+05','2017-04-19 20:40:00+05',2,'2017-04-20 04:02:35+05',1,NULL,NULL,799,NULL,NULL,'clip',NULL),
	 (3,'joescl-pgfl','Joe''s Clip','2017-04-19 20:40:00+05','2017-04-19 21:10:00+05',2,'2017-04-21 14:23:47+05',4,NULL,NULL,542,NULL,NULL,'clip',NULL),
	 (3,'bartra-bnm5','Bartra - Loughshinny Time-lapse, October 2018','2018-10-15 17:49:52+05','2018-10-15 17:49:52+05',2,'2018-10-15 18:49:51.783621+05',1,NULL,NULL,0,'https://vimeo.com/294316497',NULL,'url',NULL),
	 (5,'joescl-fv4u','Joe''s clip','2017-04-19 20:10:00+05','2017-04-19 20:40:00+05',2,'2017-04-21 14:25:13+05',4,NULL,NULL,799,NULL,NULL,'clip',NULL),
	 (1,'spark-kyzrq','Sparks Building, Newcastle - Time-lapse, July 2020','2020-07-28 20:25:33.449425+05','2020-07-28 20:25:33.449425+05',2,'2020-07-28 20:25:33.449623+05',1,NULL,NULL,0,'https://vimeo.com/442398703/ec9450b8e0',NULL,'url',NULL),
	 (2,'201611-luw8','2016-11-06 BWLiffeyCruises 1200-1230','2016-11-06 17:00:00+05','2016-11-06 17:30:00+05',2,'2016-11-23 14:36:32+05',4,false,false,897,NULL,NULL,'clip',NULL);
	 
	 
	 
      select
        C.EXID as CAMERA_ID,
        U.EMAIL,
        TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
        ARC.TITLE,
        ARC.EXID,
        ARC.CREATED_AT,
        ARC.FROM_DATE,
        ARC.TO_DATE,
        null as EMBED_CODE,
        ARC.FILE_NAME,
        ARC.FRAMES,
        ARC.URL,
        ARC.PUBLIC,
        ARC.STATUS,
        ARC.TYPE,
        null as EXTRA
      from
        PUBLIC.ARCHIVES as ARC
      left join USERS U on
        ARC.REQUESTED_BY = U.ID
      left join CAMERAS C on
        ARC.CAMERA_ID = C.ID
      union all
      select
        C.EXID as CAMERA_ID,
        U.EMAIL,
        TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
        TL.TITLE,
        TL.EXID,
        TL.INSERTED_AT,
        TL.FROM_DATETIME,
        TL.TO_DATETIME,
        null as EMBED_CODE,
        null as FILE_NAME,
        null as FRAMES,
        null as URL,
        null as PUBLIC,
        TL.STATUS,
        null as type,
        TL.EXTRA
      from
        PUBLIC.TIMELAPSES as TL
      left join USERS U on
        TL.USER_ID = U.ID
      left join CAMERAS C on
        TL.CAMERA_ID = C.ID
      union all
      select
        C.EXID as CAMERA_ID,
        U.EMAIL,
        TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
        COMP.EXID,
        COMP.NAME,
        COMP.INSERTED_AT,
        COMP.BEFORE_DATE,
        COMP.AFTER_DATE,
        COMP.EMBED_CODE,
        null as FILE_NAME,
        null as FRAMES,
        null as URL,
        COMP.PUBLIC,
        COMP.STATUS,
        null as type,
        null as EXTRA
      from
        PUBLIC.COMPARES as COMP
      left join USERS U on
        COMP.REQUESTED_BY = U.ID
      left join CAMERAS C on
        COMP.CAMERA_ID = C.ID;

version
PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit

10 rows affected

10 rows affected

5 rows affected

10 rows affected

10 rows affected

camera_id	email	btrim	title	exid	created_at	from_date	to_date	embed_code	file_name	frames	url	public	status	type	extra
siskm-guhev	fintocci@its.jnj.com	FRANCESCO INTOCCIA	Sparks Building, Newcastle - Time-lapse, July 2020	spark-kyzrq	2020-07-28 16:25:33.449623+01	2020-07-28 16:25:33.449425+01	2020-07-28 16:25:33.449425+01	null	null	0	https://vimeo.com/442398703/ec9450b8e0	null	2	url	null
siskm-guhev	fintocci@its.jnj.com	FRANCESCO INTOCCIA	2018-09-26T10:00:04.000Z	201809-27xn	2018-11-27 16:05:01.813853+00	2018-09-26 10:00:04+01	2018-09-26 10:00:04+01	null	201809-27xn.jpeg	0	null	null	2	edit	null
siskm-guhev	fintocci@its.jnj.com	FRANCESCO INTOCCIA	Concrete Pour	concre-r6ys	2017-04-05 15:46:38+01	2017-04-05 14:25:05+01	2017-04-05 14:55:05+01	null	null	840	null	f	2	clip	null
stens-wjcyf	paul.braham@hse.ie	Paul Braham	2016-11-06 BWLiffeyCruises 1200-1230	201611-luw8	2016-11-23 09:36:32+00	2016-11-06 12:00:00+00	2016-11-06 12:30:00+00	null	null	897	null	f	2	clip	null
stens-wjcyf	fintocci@its.jnj.com	FRANCESCO INTOCCIA	joe clip	joecli-kvpw	2017-04-20 00:02:35+01	2017-04-19 16:10:00+01	2017-04-19 16:40:00+01	null	null	799	null	null	2	clip	null
dslrf-bxtgj	fintocci@its.jnj.com	FRANCESCO INTOCCIA	Bartra - Loughshinny Time-lapse, October 2018	bartra-bnm5	2018-10-15 14:49:51.783621+01	2018-10-15 13:49:52+01	2018-10-15 13:49:52+01	null	null	0	https://vimeo.com/294316497	null	2	url	null
dslrf-bxtgj	paul.braham@hse.ie	Paul Braham	Joe's Clip	joescl-pgfl	2017-04-21 10:23:47+01	2017-04-19 16:40:00+01	2017-04-19 17:10:00+01	null	null	542	null	null	2	clip	null
norma-cfnbk	fintocci@its.jnj.com	FRANCESCO INTOCCIA	2019-02-01T10:13:47.000Z	201902-htu9	2019-02-01 10:14:56+00	2019-02-01 10:13:47+00	2019-02-01 10:13:47+00	null	201902-htu9.jpeg	0	null	null	2	edit	null
jubil-tjlmk	paul.braham@hse.ie	Paul Braham	Joe's clip	joescl-fv4u	2017-04-21 10:25:13+01	2017-04-19 16:10:00+01	2017-04-19 16:40:00+01	null	null	799	null	null	2	clip	null
jubil-tjlmk	lrahilly@meicltd.com	Liam Rahilly	Extract	extrac-png8	2017-04-18 09:50:42+01	2017-04-12 17:25:00+01	2017-04-12 17:40:00+01	null	null	853	null	f	2	clip	null
siskm-guhev	rkirrane@deniswilliams.ie	Reuben Kirrane	Long Ponds	longp-mvxla	2017-05-18 08:16:13+01	2016-08-29 00:00:00+01	2016-08-29 23:59:59+01	null	null	null	null	null	3	null	{"id":798}
stens-wjcyf	b.borowiak@waterman-moylan.ie	Bartosz Borowiak	Basement Today	basem-hfmjp	2019-01-22 11:49:46.333172+00	2019-01-22 10:00:00+00	2019-01-22 11:00:00+00	null	null	null	null	null	6	null	null
dslrf-bxtgj	paul.braham@hse.ie	Paul Braham	3PP	3pp-cloxd	2017-05-18 08:16:12+01	1900-01-01 08:00:00+00	8888-12-31 19:00:00+00	null	null	null	null	null	3	null	{"id":717}
norma-cfnbk	fintocci@its.jnj.com	FRANCESCO INTOCCIA	Digital HUb	digit-rgblx	2017-05-18 08:16:10+01	1900-01-01 06:00:00+00	8888-12-31 19:00:00+00	null	null	null	null	null	3	null	{"id":260}
jubil-tjlmk	rkirrane@deniswilliams.ie	Reuben Kirrane	Demo	demo-jvnzg	2017-05-18 08:16:10+01	1900-01-01 00:00:00+00	8888-12-31 23:59:59+00	null	null	null	null	null	3	null	{"id":559}
siskm-guhev	rkirrane@deniswilliams.ie	Reuben Kirrane	compa-kycgmav	Compare: 1st Nov to 5th Apr	2019-04-08 14:54:03.809314+01	2018-11-01 10:11:04+00	2019-04-05 11:04:02+01	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='south-ragcn 2018-11-01-10_11_04 2019-04-05-11_04_02 compa-kycgmav autoplay'></script>	null	null	null	t	1	null	null
siskm-guhev	fintocci@its.jnj.com	FRANCESCO INTOCCIA	compa-momncjm	Compare: 11th Oct to 19th Nov	2018-11-19 15:28:52.359459+00	2018-10-11 11:10:51+01	2018-11-19 15:26:56+00	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='leins-ugxco 2018-10-11-11_10_51 2018-11-19-15_26_56 compa-momncjm autoplay'></script>	null	null	null	t	1	null	null
siskm-guhev	rkirrane@deniswilliams.ie	Reuben Kirrane	compa-tjhacxd	Compare: 23rd Jan to 27th Oct	2018-10-29 05:56:52.67979+00	2018-01-23 13:37:55+00	2018-10-27 12:07:22+01	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='scotc-rpfbq 2018-01-23-13_37_55 2018-10-27-12_07_22 compa-tjhacxd autoplay'></script>	null	null	null	t	1	null	null
siskm-guhev	rkirrane@deniswilliams.ie	Reuben Kirrane	compa-ylxwctg	Compare: 8th Sep to 29th Oct	2018-10-29 05:52:09.659566+00	2017-09-08 14:35:25+01	2018-10-29 05:51:47+00	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='iaaco-auftq 2017-09-08-14_35_25 2018-10-29-05_51_47 compa-ylxwctg autoplay'></script>	null	null	null	t	1	null	null
stens-wjcyf	paul.braham@hse.ie	Paul Braham	compa-kdxjbhh	Compare: 4th Dec to 14th Dec	2018-12-14 22:25:00.735079+00	2018-12-04 16:30:03+00	2018-12-14 16:12:04+00	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='still-bojft 2018-12-04-16_30_03 2018-12-14-16_12_04 compa-kdxjbhh autoplay'></script>	null	null	null	t	1	null	null
stens-wjcyf	paul.braham@hse.ie	Paul Braham	compa-vztxtjc	Compare: 18th Jul to 1st Nov	2018-11-15 10:03:24.85408+00	2015-07-18 15:07:00+01	2018-11-01 00:05:43+00	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='bennett 2015-07-18-15_07_00 2018-11-01-00_05_43 compa-vztxtjc autoplay'></script>	null	null	null	t	1	null	null
stens-wjcyf	b.borowiak@waterman-moylan.ie	Bartosz Borowiak	test-hroqe	test	2018-01-17 11:08:19.845168+00	2017-06-21 13:20:55+01	2018-01-17 10:27:58+00	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='point-iytnz 2017-06-21-13_20_55 2018-01-17-10_27_58 test-hroqe autoplay'></script>	null	null	null	t	1	null	null
dslrf-bxtgj	rkirrane@deniswilliams.ie	Reuben Kirrane	compa-ltafnon	Compare: 26th Jul to 29th Oct	2018-10-29 05:53:39.989565+00	2018-07-26 14:23:00+01	2018-10-29 05:37:00+00	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='crome-tidcz 2018-07-26-14_23_00 2018-10-29-05_37_00 compa-ltafnon autoplay'></script>	null	null	null	t	1	null	null
norma-cfnbk	fintocci@its.jnj.com	FRANCESCO INTOCCIA	itsac-ihtlze	Its a compare	2020-01-01 08:46:23.172885+00	2016-03-19 05:21:12+00	2020-01-01 08:45:34+00	<div id='evercam-compare'></div><script src='https://dash2.evercam.io/evercam_compare.js' class='gpocam 2016-03-19-05_21_12 2020-01-01-08_45_34 itsac-ihtlze autoplay'></script>	null	null	null	t	1	null	null
norma-cfnbk	lrahilly@meicltd.com	Liam Rahilly	compa-cockhyp	Compare: 11th Jan to 19th Dec	2018-12-19 14:13:35.856163+00	2018-01-11 14:36:19+00	2018-12-19 14:12:09+00	<div id='evercam-compare'></div><script src='https://dash.evercam.io/assets/evercam_compare.js' class='mscpi-pkjwh 2018-01-11-14_36_19 2018-12-19-14_12_09 compa-cockhyp autoplay'></script>	null	null	null	t	1	null	null