clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591627 fiddles created (45700 in the last week).

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;
 hidden batch(es)


SELECT FLOOR(pseudo_encrypt(7) * RANDOM()); SELECT FLOOR(pseudo_encrypt(7) * RANDOM());
floor
710700229
floor
1569268151
 hidden batch(es)


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) );
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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) );
 hidden batch(es)


INSERT INTO tbl1 (name, address, gender) VALUES ('Name1', 'Address_1', 'M'), ('Name2', 'Address_2', 'F'), ('Name3', 'Address_3', 'M');
3 rows affected
 hidden batch(es)


SELECT * FROM tbl1;
id name address gender
9b5df9700f92a6ad29cc6f5ad738cb5a Name1 Address_1 M
9710dea03df98f8cbbc27ff68ca39ae3 Name2 Address_2 F
93cab3778c4f1c63b72dfe10daf42fc8 Name3 Address_3 M
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)