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?.

CREATE OR REPLACE FUNCTION pseudo_encrypt(VALUE int) returns int AS $$
DECLARE
l1 int;
l2 int;
r1 int;
r2 int;
i int:=0;
BEGIN
 l1:= (VALUE >> 16) & 65535;
 r1:= VALUE & 65535;
 WHILE i < 3 LOOP
   l2 := r1;
   r2 := l1 # ((((1366 * r1 + 150889) % 714025) / 714025.0) * 32767)::int;
   l1 := l2;
   r1 := r2;
   i := i + 1;
 END LOOP;
 RETURN ((r1 << 16) + l1);
END;
$$ LANGUAGE plpgsql strict immutable;
SELECT FLOOR(pseudo_encrypt(7) * RANDOM());
SELECT FLOOR(pseudo_encrypt(7) * RANDOM());


floor


710700229




floor


1569268151


CREATE TABLE tbl2
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  org TEXT NOT NULL DEFAULT SUBSTRING(MD5(RANDOM()::TEXT) FROM 1 FOR 4),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  update_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT tbl2_pk PRIMARY KEY (id)
);

INSERT INTO tbl2 (name, address, gender)
VALUES
('Name_23', 'Address_23', 'M'),
('Name_24', 'Address_24', 'F'),
('Name_25', 'Address_25', 'M');
3 rows affected

SELECT * FROM tbl2


id
org
name
address
gender
created_at
update_at


6486ec24b49e0192c79972b1f0da4d94
f293
Name_23
Address_23
M
2020-02-26 05:54:57.609884+00
2020-02-26 05:54:57.609884+00

0cfba4cc3c1118f7a4e4311347fa3ebc
f150
Name_24
Address_24
F
2020-02-26 05:54:57.609884+00
2020-02-26 05:54:57.609884+00

d65bbdaec239f7152c9a0f0e6332b899
43a2
Name_25
Address_25
M
2020-02-26 05:54:57.609884+00
2020-02-26 05:54:57.609884+00


CREATE TABLE tbl1
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  CONSTRAINT tbl1_pk PRIMARY KEY (id)
);
INSERT INTO tbl1 (name, address, gender)
VALUES
('Name1', 'Address_1', 'M'),
('Name2', 'Address_2', 'F'),
('Name3', 'Address_3', 'M');
3 rows affected

SELECT * FROM tbl1;


id
name
address
gender


9b5df9700f92a6ad29cc6f5ad738cb5a
Name1
Address_1
M

9710dea03df98f8cbbc27ff68ca39ae3
Name2
Address_2
F

93cab3778c4f1c63b72dfe10daf42fc8
Name3
Address_3
M


INSERT INTO tbl2 (name, address, gender)
SELECT name, address, gender
FROM tbl1 
-- WHERE tbl.id NOT IN (SELECT id FROM tbl1) -- MD5 should ensure uniqueness
RETURNING name, address, gender;  -- optional - read about it!


name
address
gender


Name1
Address_1
M

Name2
Address_2
F

Name3
Address_3
M


SELECT * FROM tbl2;


id
org
name
address
gender
created_at
update_at


6486ec24b49e0192c79972b1f0da4d94
f293
Name_23
Address_23
M
2020-02-26 05:54:57.609884+00
2020-02-26 05:54:57.609884+00

0cfba4cc3c1118f7a4e4311347fa3ebc
f150
Name_24
Address_24
F
2020-02-26 05:54:57.609884+00
2020-02-26 05:54:57.609884+00

d65bbdaec239f7152c9a0f0e6332b899
43a2
Name_25
Address_25
M
2020-02-26 05:54:57.609884+00
2020-02-26 05:54:57.609884+00

47bfc217429c0f22ab69264af7e1893a
6cea
Name1
Address_1
M
2020-02-26 05:54:57.62117+00
2020-02-26 05:54:57.62117+00

4e09b36679da7647c9539ff0fa6fa90b
c803
Name2
Address_2
F
2020-02-26 05:54:57.62117+00
2020-02-26 05:54:57.62117+00

f7c1bcb55f53d51d23803cbd747669bf
e6d2
Name3
Address_3
M
2020-02-26 05:54:57.62117+00
2020-02-26 05:54:57.62117+00

id	org	name	address	gender	created_at	update_at
6486ec24b49e0192c79972b1f0da4d94	f293	Name_23	Address_23	M	2020-02-26 05:54:57.609884+00	2020-02-26 05:54:57.609884+00
0cfba4cc3c1118f7a4e4311347fa3ebc	f150	Name_24	Address_24	F	2020-02-26 05:54:57.609884+00	2020-02-26 05:54:57.609884+00
d65bbdaec239f7152c9a0f0e6332b899	43a2	Name_25	Address_25	M	2020-02-26 05:54:57.609884+00	2020-02-26 05:54:57.609884+00

id	name	address	gender
9b5df9700f92a6ad29cc6f5ad738cb5a	Name1	Address_1	M
9710dea03df98f8cbbc27ff68ca39ae3	Name2	Address_2	F
93cab3778c4f1c63b72dfe10daf42fc8	Name3	Address_3	M