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();


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


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);
CREATE TABLE public.camera_shares (
	id serial NOT NULL,
	camera_id int4 NOT NULL,
	user_id int4 NOT NULL,
	sharer_id int4 NULL,
	kind varchar(50) NOT NULL,
	created_at timestamptz NOT NULL,
	updated_at timestamptz NOT NULL,
	message text NULL,
	CONSTRAINT camera_shares_pkey PRIMARY KEY (id)
);
CREATE INDEX camera_shares_camera_id_index ON public.camera_shares USING btree (camera_id);
CREATE UNIQUE INDEX camera_shares_camera_id_user_id_index ON public.camera_shares USING btree (camera_id, user_id);
CREATE INDEX camera_shares_user_id_index ON public.camera_shares USING btree (user_id);


-- public.camera_shares foreign keys

ALTER TABLE public.camera_shares ADD CONSTRAINT camera_shares_camera_id_fkey FOREIGN KEY (camera_id) REFERENCES cameras(id);
ALTER TABLE public.camera_shares ADD CONSTRAINT camera_shares_sharer_id_fkey FOREIGN KEY (sharer_id) REFERENCES users(id);
ALTER TABLE public.camera_shares ADD CONSTRAINT camera_shares_user_id_fkey FOREIGN KEY (user_id) 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');
10 rows affected

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);
10 rows affected

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}');
5 rows affected

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);
10 rows affected

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);
10 rows affected

INSERT INTO public.camera_shares (camera_id,user_id,sharer_id,kind,created_at,updated_at,message) VALUES
	 (1,1,2,'private','2020-04-02 19:41:32+05','2020-04-02 19:41:32+05',NULL),
	 (2,6,2,'private','2019-03-25 13:29:08+05','2019-03-25 13:29:08+05', NULL),
	 (1,4,2,'private','2019-07-01 13:38:44+05','2019-07-01 13:38:44+05',NULL),
	 (3,5,2,'private','2019-10-03 11:51:36+05','2019-10-03 11:51:36+05', NULL),
	 (1,5,2,'private','2019-11-09 18:29:34+05','2019-11-09 18:29:34+05',NULL),
	 (4,5,2,'private','2020-02-03 14:52:22+05','2020-02-03 14:52:22+05',NULL),
	 (2,1,5,'private','2020-07-17 23:30:01+05','2020-07-17 23:30:01+05',NULL),
	 (2,9,6,'public','2014-05-16 20:41:19.15227+05','2014-05-16 20:41:19.15227+05',NULL),
	 (2,2,7,'private','2020-04-02 20:06:25+05','2020-04-02 20:06:25+05',NULL),
	 (2,3,8,'private','2018-05-16 22:24:35.136927+05','2018-05-16 22:24:35.136931+05','Here you go Peter. ');
10 rows affected

-- select * from users;
-- select * from cameras
-- select * from compares
-- select * from archives
-- select * from timelapses
select id from cameras where owner_id=1


id


1


select camera_id from camera_shares where user_id=1


camera_id


1

2


CREATE FUNCTION archives_view(int) RETURNS TABLE(
  tab_name text,
  title	   text,
  exid     text,
  created_at timestamptz,
  from_date  timestamptz,
  to_date    timestamptz,
  embed_code text,
  file_name  text,
  frames     int,
  url        text,
  public boolean,
  status int,
  type	 text,
  extra	 json,
  requested_by int,
  camera_id	int,
  requester_name text,
  requester_email text,	
  camera_exid text,	
  name text,	
  camera_name text,
  camera_timezone text,
  user_id int
)
AS
$$ 
  WITH CORE AS (
    SELECT
      'ARCHIVES' AS TAB_NAME,
      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,
      ARC.REQUESTED_BY,
      ARC.CAMERA_ID
    FROM
      PUBLIC.ARCHIVES AS ARC
    UNION ALL
    SELECT
      'TIMELAPSES',
      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,
      TL.USER_ID,
      TL.CAMERA_ID
    FROM
      PUBLIC.TIMELAPSES AS TL
    UNION ALL
    SELECT
      'COMPARES',
      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,
      COMP.REQUESTED_BY,
      COMP.CAMERA_ID
    FROM
      PUBLIC.COMPARES AS COMP ),
    MORE_INFO AS (
      SELECT
        C.*,
        TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)) AS REQUESTER_NAME,
        U.EMAIL AS REQUESTER_EMAIL,
        CAM.EXID AS CAMERA_EXID,
        CAM.NAME,
        CAM.NAME AS CAMERA_NAME,
        CAM.TIMEZONE AS CAMERA_TIMEZONE,
        U.ID AS USER_ID
      FROM
        CORE C
      JOIN USERS U ON
        C.REQUESTED_BY = U.ID AND U.ID = $1
      JOIN CAMERAS CAM ON
        C.CAMERA_ID = CAM.ID )
      SELECT
        *
      FROM
        MORE_INFO
$$
    LANGUAGE SQL;
select * 
from archives_view(1)
order by CREATED_AT DESC;


tab_name
title
exid
created_at
from_date
to_date
embed_code
file_name
frames
url
public
status
type
extra
requested_by
camera_id
requester_name
requester_email
camera_exid
name
camera_name
camera_timezone
user_id


ARCHIVES
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
1
1
FRANCESCO INTOCCIA
fintocci@its.jnj.com
siskm-guhev
Sisk Mater 1
Sisk Mater 1
Europe/Dublin
1

COMPARES
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
1
4
FRANCESCO INTOCCIA
fintocci@its.jnj.com
norma-cfnbk
Normal Test Camera
Normal Test Camera
Europe/Dublin
1

ARCHIVES
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
1
4
FRANCESCO INTOCCIA
fintocci@its.jnj.com
norma-cfnbk
Normal Test Camera
Normal Test Camera
Europe/Dublin
1

ARCHIVES
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
1
1
FRANCESCO INTOCCIA
fintocci@its.jnj.com
siskm-guhev
Sisk Mater 1
Sisk Mater 1
Europe/Dublin
1

COMPARES
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
1
1
FRANCESCO INTOCCIA
fintocci@its.jnj.com
siskm-guhev
Sisk Mater 1
Sisk Mater 1
Europe/Dublin
1

ARCHIVES
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
1
3
FRANCESCO INTOCCIA
fintocci@its.jnj.com
dslrf-bxtgj
DSLR FTP
DSLR FTP
Etc/UTC
1

TIMELAPSES
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}
1
4
FRANCESCO INTOCCIA
fintocci@its.jnj.com
norma-cfnbk
Normal Test Camera
Normal Test Camera
Europe/Dublin
1

ARCHIVES
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
1
2
FRANCESCO INTOCCIA
fintocci@its.jnj.com
stens-wjcyf
Stenson Court
Stenson Court
Europe/London
1

ARCHIVES
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
1
1
FRANCESCO INTOCCIA
fintocci@its.jnj.com
siskm-guhev
Sisk Mater 1
Sisk Mater 1
Europe/Dublin
1
tab_name	title	exid	created_at	from_date	to_date	embed_code	file_name	frames	url	public	status	type	extra	requested_by	camera_id	requester_name	requester_email	camera_exid	name	camera_name	camera_timezone	user_id
ARCHIVES	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	1	1	FRANCESCO INTOCCIA	fintocci@its.jnj.com	siskm-guhev	Sisk Mater 1	Sisk Mater 1	Europe/Dublin	1
COMPARES	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	1	4	FRANCESCO INTOCCIA	fintocci@its.jnj.com	norma-cfnbk	Normal Test Camera	Normal Test Camera	Europe/Dublin	1
ARCHIVES	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	1	4	FRANCESCO INTOCCIA	fintocci@its.jnj.com	norma-cfnbk	Normal Test Camera	Normal Test Camera	Europe/Dublin	1
ARCHIVES	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	1	1	FRANCESCO INTOCCIA	fintocci@its.jnj.com	siskm-guhev	Sisk Mater 1	Sisk Mater 1	Europe/Dublin	1
COMPARES	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	1	1	FRANCESCO INTOCCIA	fintocci@its.jnj.com	siskm-guhev	Sisk Mater 1	Sisk Mater 1	Europe/Dublin	1
ARCHIVES	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	1	3	FRANCESCO INTOCCIA	fintocci@its.jnj.com	dslrf-bxtgj	DSLR FTP	DSLR FTP	Etc/UTC	1
TIMELAPSES	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}	1	4	FRANCESCO INTOCCIA	fintocci@its.jnj.com	norma-cfnbk	Normal Test Camera	Normal Test Camera	Europe/Dublin	1
ARCHIVES	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	1	2	FRANCESCO INTOCCIA	fintocci@its.jnj.com	stens-wjcyf	Stenson Court	Stenson Court	Europe/London	1
ARCHIVES	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	1	1	FRANCESCO INTOCCIA	fintocci@its.jnj.com	siskm-guhev	Sisk Mater 1	Sisk Mater 1	Europe/Dublin	1