\set ECHO 0
-- int
SELECT quantile(x, -0.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value -0.500000 - needs to be in [0,1]
SELECT quantile(x, 1.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value 1.500000 - needs to be in [0,1]
SELECT quantile(x, 0.5) FROM generate_series(1,1000) s(x);
 quantile 
----------
      500
(1 row)

SELECT quantile(x, 0.1) FROM generate_series(1,1000) s(x);
 quantile 
----------
      100
(1 row)

SELECT quantile(x, 0) FROM generate_series(1,1000) s(x);
 quantile 
----------
        1
(1 row)

SELECT quantile(x, 1) FROM generate_series(1,1000) s(x);
 quantile 
----------
     1000
(1 row)

SELECT quantile(x, ARRAY[0.5]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {500}
(1 row)

SELECT quantile(x, ARRAY[0.1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {100}
(1 row)

SELECT quantile(x, ARRAY[0]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1}
(1 row)

SELECT quantile(x, ARRAY[1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1000}
(1 row)

SELECT quantile(x, ARRAY[0, 0.1, 0.5, 0.75, 1]) FROM generate_series(1,1000) s(x);
       quantile       
----------------------
 {1,100,500,750,1000}
(1 row)

-- bigint
SELECT quantile(x::bigint, -0.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value -0.500000 - needs to be in [0,1]
SELECT quantile(x::bigint, 1.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value 1.500000 - needs to be in [0,1]
SELECT quantile(x::bigint, 0.5) FROM generate_series(1,1000) s(x);
 quantile 
----------
      500
(1 row)

SELECT quantile(x::bigint, 0.1) FROM generate_series(1,1000) s(x);
 quantile 
----------
      100
(1 row)

SELECT quantile(x::bigint, 0) FROM generate_series(1,1000) s(x);
 quantile 
----------
        1
(1 row)

SELECT quantile(x::bigint, 1) FROM generate_series(1,1000) s(x);
 quantile 
----------
     1000
(1 row)

SELECT quantile(x::bigint, ARRAY[0.5]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {500}
(1 row)

SELECT quantile(x::bigint, ARRAY[0.1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {100}
(1 row)

SELECT quantile(x::bigint, ARRAY[0]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1}
(1 row)

SELECT quantile(x::bigint, ARRAY[1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1000}
(1 row)

SELECT quantile(x::bigint, ARRAY[0, 0.1, 0.5, 0.75, 1]) FROM generate_series(1,1000) s(x);
       quantile       
----------------------
 {1,100,500,750,1000}
(1 row)

-- double precision
SELECT quantile(x::double precision, -0.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value -0.500000 - needs to be in [0,1]
SELECT quantile(x::double precision, 1.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value 1.500000 - needs to be in [0,1]
SELECT quantile(x::double precision, 0.5) FROM generate_series(1,1000) s(x);
 quantile 
----------
      500
(1 row)

SELECT quantile(x::double precision, 0.1) FROM generate_series(1,1000) s(x);
 quantile 
----------
      100
(1 row)

SELECT quantile(x::double precision, 0) FROM generate_series(1,1000) s(x);
 quantile 
----------
        1
(1 row)

SELECT quantile(x::double precision, 1) FROM generate_series(1,1000) s(x);
 quantile 
----------
     1000
(1 row)

SELECT quantile(x::double precision, ARRAY[0.5]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {500}
(1 row)

SELECT quantile(x::double precision, ARRAY[0.1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {100}
(1 row)

SELECT quantile(x::double precision, ARRAY[0]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1}
(1 row)

SELECT quantile(x::double precision, ARRAY[1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1000}
(1 row)

SELECT quantile(x::double precision, ARRAY[0, 0.1, 0.5, 0.75, 1]) FROM generate_series(1,1000) s(x);
       quantile       
----------------------
 {1,100,500,750,1000}
(1 row)

-- numeric
SELECT quantile(x::numeric, -0.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value -0.500000 - needs to be in [0,1]
SELECT quantile(x::numeric, 1.5) FROM generate_series(1,1000) s(x);
ERROR:  invalid percentile value 1.500000 - needs to be in [0,1]
SELECT quantile(x::numeric, 0.5) FROM generate_series(1,1000) s(x);
 quantile 
----------
      500
(1 row)

SELECT quantile(x::numeric, 0.1) FROM generate_series(1,1000) s(x);
 quantile 
----------
      100
(1 row)

SELECT quantile(x::numeric, 0) FROM generate_series(1,1000) s(x);
 quantile 
----------
        1
(1 row)

SELECT quantile(x::numeric, 1) FROM generate_series(1,1000) s(x);
 quantile 
----------
     1000
(1 row)

SELECT quantile(x::numeric, ARRAY[0.5]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {500}
(1 row)

SELECT quantile(x::numeric, ARRAY[0.1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {100}
(1 row)

SELECT quantile(x::numeric, ARRAY[0]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1}
(1 row)

SELECT quantile(x::numeric, ARRAY[1]) FROM generate_series(1,1000) s(x);
 quantile 
----------
 {1000}
(1 row)

SELECT quantile(x::numeric, ARRAY[0, 0.1, 0.5, 0.75, 1]) FROM generate_series(1,1000) s(x);
       quantile       
----------------------
 {1,100,500,750,1000}
(1 row)

-- test of correct NULL handling (skipping with all NULLS)
CREATE TABLE parent_table (id int);
CREATE TABLE child_table  (id int, val int);
INSERT INTO parent_table SELECT i FROM generate_series(1,10) s(i);
INSERT INTO child_table  SELECT 2, i FROM generate_series(1,100) s(i);
INSERT INTO child_table  SELECT 4, i FROM generate_series(1,100) s(i);
INSERT INTO child_table  SELECT 6, i FROM generate_series(1,100) s(i);
INSERT INTO child_table  SELECT 8, i FROM generate_series(1,100) s(i);
INSERT INTO child_table  SELECT 10, i FROM generate_series(1,100) s(i);
SELECT parent_table.id, quantile(child_table.val, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id |           quantile           
----+------------------------------
  1 | 
  2 | {10,20,30,40,50,60,70,80,90}
  3 | 
  4 | {10,20,30,40,50,60,70,80,90}
  5 | 
  6 | {10,20,30,40,50,60,70,80,90}
  7 | 
  8 | {10,20,30,40,50,60,70,80,90}
  9 | 
 10 | {10,20,30,40,50,60,70,80,90}
(10 rows)

SELECT parent_table.id, quantile(child_table.val, 0.5) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id | quantile 
----+----------
  1 |         
  2 |       50
  3 |         
  4 |       50
  5 |         
  6 |       50
  7 |         
  8 |       50
  9 |         
 10 |       50
(10 rows)

SELECT parent_table.id, quantile(child_table.val::bigint, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id |           quantile           
----+------------------------------
  1 | 
  2 | {10,20,30,40,50,60,70,80,90}
  3 | 
  4 | {10,20,30,40,50,60,70,80,90}
  5 | 
  6 | {10,20,30,40,50,60,70,80,90}
  7 | 
  8 | {10,20,30,40,50,60,70,80,90}
  9 | 
 10 | {10,20,30,40,50,60,70,80,90}
(10 rows)

SELECT parent_table.id, quantile(child_table.val::bigint, 0.5) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id | quantile 
----+----------
  1 |         
  2 |       50
  3 |         
  4 |       50
  5 |         
  6 |       50
  7 |         
  8 |       50
  9 |         
 10 |       50
(10 rows)

SELECT parent_table.id, quantile(child_table.val::double precision, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id |           quantile           
----+------------------------------
  1 | 
  2 | {10,20,30,40,50,60,70,80,90}
  3 | 
  4 | {10,20,30,40,50,60,70,80,90}
  5 | 
  6 | {10,20,30,40,50,60,70,80,90}
  7 | 
  8 | {10,20,30,40,50,60,70,80,90}
  9 | 
 10 | {10,20,30,40,50,60,70,80,90}
(10 rows)

SELECT parent_table.id, quantile(child_table.val::double precision, 0.5) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id | quantile 
----+----------
  1 |         
  2 |       50
  3 |         
  4 |       50
  5 |         
  6 |       50
  7 |         
  8 |       50
  9 |         
 10 |       50
(10 rows)

SELECT parent_table.id, quantile(child_table.val::numeric, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id |           quantile           
----+------------------------------
  1 | 
  2 | {10,20,30,40,50,60,70,80,90}
  3 | 
  4 | {10,20,30,40,50,60,70,80,90}
  5 | 
  6 | {10,20,30,40,50,60,70,80,90}
  7 | 
  8 | {10,20,30,40,50,60,70,80,90}
  9 | 
 10 | {10,20,30,40,50,60,70,80,90}
(10 rows)

SELECT parent_table.id, quantile(child_table.val::numeric, 0.5) FROM parent_table LEFT JOIN child_table USING (id) GROUP BY id ORDER BY id;
 id | quantile 
----+----------
  1 |         
  2 |       50
  3 |         
  4 |       50
  5 |         
  6 |       50
  7 |         
  8 |       50
  9 |         
 10 |       50
(10 rows)

-- test of correct NULL handling (skipping with mixed NULL and not-NULL values)
TRUNCATE child_table;
INSERT INTO child_table  SELECT i, (CASE WHEN MOD(i,2) = 0 THEN i/2 ELSE NULL END) FROM generate_series(1,200) s(i);
SELECT quantile(val, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM (SELECT val FROM child_table ORDER BY id) AS foo;
           quantile           
------------------------------
 {10,20,30,40,50,60,70,80,90}
(1 row)

SELECT quantile(val::bigint, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM (SELECT val FROM child_table ORDER BY id) AS foo;
           quantile           
------------------------------
 {10,20,30,40,50,60,70,80,90}
(1 row)

SELECT quantile(val::double precision, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM (SELECT val FROM child_table ORDER BY id) AS foo;
           quantile           
------------------------------
 {10,20,30,40,50,60,70,80,90}
(1 row)

SELECT quantile(val::numeric, array[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) FROM (SELECT val FROM child_table ORDER BY id) AS foo;
           quantile           
------------------------------
 {10,20,30,40,50,60,70,80,90}
(1 row)