Skip to content

Instantly share code, notes, and snippets.

@onderkalaci

onderkalaci/json.sql

Last active November 27, 2020 10:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save onderkalaci/bf242c70aa0045496fd43d4949987034 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save onderkalaci/bf242c70aa0045496fd43d4949987034 to your computer and use it in GitHub Desktop.
Download ZIP
PG Data Types
Raw
json.sql
Create or replace function get_random_escape_char() returns char as
$$
declare
i integer:=(random() * 100)::int % 8;
begin
if i = 0 then
return '\t';
elseif i = 1 then
return '\n';
elseif i = 2 then
return '\b';
elseif i = 3 then
return '\\';
elseif i = 4 then
return '\r';
elseif i = 5 then
return '\f';
else
return '\t';
end if;
end;
$$ language plpgsql;
Create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
--chars char[] := '{''\\n'',''\\t''}';
result text := '';
i integer := 0;
begin
if length < 0 then
raise exception 'Given length cannot be less than 0';
end if;
for i in 1..length loop
result := get_random_escape_char() || result || chars[1+random()*(array_length(chars, 1)-1)] || get_random_escape_char();
end loop;
return result;
end;
$$ language plpgsql;
Create or replace function random_jsonb() returns jsonb as
$$
declare
i integer := (random() * 100);
begin
if i < 16 then
return (random()*100)::int;
elseif i<32 then
return 'true'::jsonb;
elseif i<48 then
return 'false'::jsonb;
elseif i<64 then
return '"' ||random_string((random()*20)::int) || '"';
ELSEIF i<80 THEN
return null;
ELSEIF i<90 THEN
RETURN (select to_jsonb(ARRAY(SELECT random_jsonb() from generate_series(1, 5)))).to_jsonb;
ELSE
RETURN (select jsonb_object(('{' || right(left(to_jsonb(array(SELECT case when x%2=1 then (random_string(3)::text) else (random_jsonb() #>> '{}') end from generate_series(1, 6) x)) #>> '{}', -1), -1) || '}')::text[]));
end if;
end;
$$ language plpgsql;
DROP TABLE IF EXISTS source_table, sink_table;
CREATE TABLE source_table(a jsonb);
INSERT INTO source_table select random_jsonb() FROM generate_series(0,10000);
CREATE TABLE sink_table (a jsonb);
Raw
unconventional_types.sql
DROP TABLE weird_types;
CREATE TABLE weird_types
(
col1 int[], col2 int[][], col3 int [][][], --int array
col4 varchar[], col5 varchar[][], col6 varchar [][][], --varchar array
col70 bit, col7 bit[], col8 bit[][], col9 bit [][][], --bit array
col10 bit varying(10),
col11 bit varying(10)[], col12 bit varying(10)[][], col13 bit varying(10)[][][], --int array
col14 bytea, col15 bytea[], col16 bytea[][], col17 bytea[][][],
col18 boolean, col19 boolean[], col20 boolean[][], col21 boolean[][][],
col22 inet, col23 inet[], col24 inet[][], col25 inet[][][],
col26 macaddr, col27 macaddr[], col28 macaddr[][], col29 macaddr[][][],
col30 numeric, col32 numeric[], col33 numeric[][], col34 numeric[][][],
col35 jsonb, col36 jsonb[], col37 jsonb[][], col38 jsonb[][][]
);
INSERT INTO weird_types (col1)
VALUES (ARRAY[1]), (ARRAY[1,2,3]);
INSERT INTO weird_types (col2)
VALUES (ARRAY[ARRAY[0,0,0]]), (ARRAY[ARRAY[1,2,3], ARRAY[5,6,7]]);
INSERT INTO weird_types (col3)
VALUES (ARRAY[ARRAY[ARRAY[0,0,0]]]), (ARRAY[ARRAY[ARRAY[1,2,3]], ARRAY[ARRAY[5,6,7]], ARRAY[ARRAY[1,2,3]], ARRAY[ARRAY[5,6,7]]]);
INSERT INTO weird_types (col4)
VALUES (ARRAY['1']), (ARRAY['1','2','3']);
INSERT INTO weird_types (col5)
VALUES (ARRAY[ARRAY['0','0','0']]), (ARRAY[ARRAY['1','2','3'], ARRAY['5','6','7']]);
INSERT INTO weird_types (col6)
VALUES (ARRAY[ARRAY[ARRAY['0','0','0']]]), (ARRAY[ARRAY[ARRAY['1','2','3']], ARRAY[ARRAY['5','6','7']], ARRAY[ARRAY['1','2','3']], ARRAY[ARRAY['5','6','7']]]);
INSERT INTO weird_types (col70)
VALUES ('1'), ('0');
INSERT INTO weird_types (col7)
VALUES (ARRAY[b'1']), (ARRAY[b'1',b'0',b'0']);
INSERT INTO weird_types (col8)
VALUES (ARRAY[ARRAY[b'0',b'0',b'0']]), (ARRAY[ARRAY[b'1',b'1',b'0'], ARRAY[b'0',b'0',b'1']]);
INSERT INTO weird_types (col9)
VALUES (ARRAY[ARRAY[ARRAY[b'0',b'0',b'0']]]), (ARRAY[ARRAY[ARRAY[b'1',b'1',b'1']], ARRAY[ARRAY[b'1','0','0']], ARRAY[ARRAY[b'1','1','1']], ARRAY[ARRAY[b'0','0','0']]]);
INSERT INTO weird_types (col10)
VALUES ('11101'), ('00010'), ('00'), ('0'), ('1'), ('11'), ('10'), ('01'), ('101'), ('011');
INSERT INTO weird_types (col11)
VALUES (ARRAY[b'1']), (ARRAY[b'11',b'10',b'01']);
INSERT INTO weird_types (col12)
VALUES (ARRAY[ARRAY[b'01',b'01',b'01']]), (ARRAY[ARRAY[b'11',b'010',b'101'], ARRAY[b'101',b'01111',b'1000001']]);
INSERT INTO weird_types (col13)
VALUES (ARRAY[ARRAY[ARRAY[b'011',b'110',b'0000']]]), (ARRAY[ARRAY[ARRAY[b'10000',b'111111',b'1101010101']], ARRAY[ARRAY[b'1101010','0','1']], ARRAY[ARRAY[b'1','1','11111111']], ARRAY[ARRAY[b'0000000','0','0']]]);
INSERT INTO weird_types (col14)
VALUES ('\xb4a8e04c0b'), ('\xb4a8e04c0b'), ('\xb4a8e04c0b'), ('\xb4a8e04c0b'), ('\xb4a8e04c0b'), ('\xb4a8e04c0b'), ('\x18a232a678'), ('\x38b2697632'), ('\xc5909a6f7d'), ('\x948f08d5f0');
INSERT INTO weird_types (col15)
VALUES (ARRAY['\xb4a8e04c0b'::bytea]), (ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea]);
INSERT INTO weird_types (col16)
VALUES (ARRAY[ARRAY[b'01',b'01',b'01']]), (ARRAY[ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea], ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea]]);
INSERT INTO weird_types (col17)
VALUES (ARRAY[ARRAY[ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea]]]), (ARRAY[ARRAY[ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea]], ARRAY[ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea]], ARRAY[ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea]], ARRAY[ARRAY['\xb4a8e04c0b'::bytea,'\x18a232a678'::bytea,'\x38b2697632'::bytea]]]);
INSERT INTO weird_types (col18)
VALUES ('1'), ('1'), ('1'), ('true'), ('false'), ('false'), ('true'), ('false'), ('1'), ('true');
INSERT INTO weird_types (col19) VALUES (ARRAY[true]), (ARRAY[1::boolean,true,false]);
INSERT INTO weird_types (col20)
VALUES (ARRAY[ARRAY[1::boolean,true,false]]), (ARRAY[ARRAY[1::boolean,true,false], ARRAY[1::boolean,true,false]]);
INSERT INTO weird_types (col21)
VALUES (ARRAY[ARRAY[ARRAY[1::boolean,true,false]]]), (ARRAY[ARRAY[ARRAY[1::boolean,true,false]], ARRAY[ARRAY[1::boolean,true,false]], ARRAY[ARRAY[1::boolean,true,false]], ARRAY[ARRAY[1::boolean,true,false]]]);
INSERT INTO weird_types (col22)
VALUES (inet '192.168.1/24'), (inet '192.168.1.1'), ('192.168.1.1'), ('192.168.1.80/28'), ('0.0.0.0'), ('0.0.0.0/32'), ('0.0.0.0/24'), ('8.8.8.8/24'), ('::ffff:fff0:1'), ('::ffff:1');
INSERT INTO weird_types (col23) VALUES (ARRAY[inet '192.168.1.1']), (ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']);
INSERT INTO weird_types (col24)
VALUES (ARRAY[ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']]), (ARRAY[ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']]);
INSERT INTO weird_types (col25)
VALUES (ARRAY[ARRAY[ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']]]), (ARRAY[ARRAY[ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']], ARRAY[ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']], ARRAY[ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']], ARRAY[ARRAY[inet '0.0.0.0', '0.0.0.0/32', '::ffff:fff0:1', '192.168.1/24']]]);
INSERT INTO weird_types (col26)
VALUES (macaddr '08:00:2b:01:02:03'), (macaddr '08002b:010203'), (macaddr '08002b-010203'), ('0800.2b01.0203'), ('08002b010203');
INSERT INTO weird_types (col27) VALUES (ARRAY[macaddr '08:00:2b:01:02:03']), (ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']);
INSERT INTO weird_types (col28)
VALUES (ARRAY[ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']]), (ARRAY[ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']]);
INSERT INTO weird_types (col29)
VALUES (ARRAY[ARRAY[ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']]]), (ARRAY[ARRAY[ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']], ARRAY[ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']], ARRAY[ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']], ARRAY[ARRAY[macaddr '08002b-010203', macaddr '08002b-010203', '08002b010203']]]);
INSERT INTO weird_types (col32)
VALUES (ARRAY[1.1]), (ARRAY[1.1,2.22,3.33]);
INSERT INTO weird_types (col33)
VALUES (ARRAY[ARRAY[0,0.111,0.15]]), (ARRAY[ARRAY[1.55,2.66,3.88], ARRAY[11.5,10101.6,7111.1]]);
INSERT INTO weird_types (col34)
VALUES (ARRAY[ARRAY[ARRAY[0,0,0]]]), (ARRAY[ARRAY[ARRAY[1,2,3]], ARRAY[ARRAY[5,6,7]], ARRAY[ARRAY[1.1,2.1,3]], ARRAY[ARRAY[5.0,6.0,7.0]]]);
INSERT INTO weird_types (col35)
VALUES (random_jsonb()), (random_jsonb());
INSERT INTO weird_types (col36)
VALUES (ARRAY[random_jsonb()]), (ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]);
INSERT INTO weird_types (col37)
VALUES (ARRAY[ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]]), (ARRAY[ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()], ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]]);
INSERT INTO weird_types (col38)
VALUES (ARRAY[ARRAY[ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]]]), (ARRAY[ARRAY[ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]], ARRAY[ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]], ARRAY[ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]], ARRAY[ARRAY[random_jsonb(),random_jsonb(),random_jsonb(),random_jsonb()]]]);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment