src/test/regress/sql/psql_crosstab.sql

   1 --
   2 -- \crosstabview
   3 --
   4
   5 CREATE TABLE ctv_data (v, h, c, i, d) AS
   6 VALUES
   7    ('v1','h2','foo', 3, '2015-04-01'::date),
   8    ('v2','h1','bar', 3, '2015-01-02'),
   9    ('v1','h0','baz', NULL, '2015-07-12'),
  10    ('v0','h4','qux', 4, '2015-07-15'),
  11    ('v0','h4','dbl', -3, '2014-12-15'),
  12    ('v0',NULL,'qux', 5, '2014-07-15'),
  13    ('v1','h2','quux',7, '2015-04-04');
  14
  15 -- make plans more stable
  16 ANALYZE ctv_data;
  17
  18 -- running \crosstabview after query uses query in buffer
  19 SELECT v, EXTRACT(year FROM d), count(*)
  20  FROM ctv_data
  21  GROUP BY 1, 2
  22  ORDER BY 1, 2;
  23 -- basic usage with 3 columns
  24  \crosstabview
  25
  26 -- ordered months in horizontal header, quoted column name
  27 SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
  28  count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
  29  \crosstabview v "month name" 4 num
  30
  31 -- ordered months in vertical header, ordered years in horizontal header
  32 SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS """month"" name",
  33   EXTRACT(month FROM d) AS month,
  34   format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
  35   FROM ctv_data
  36   GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
  37 ORDER BY month
  38 \crosstabview """month"" name" year format year
  39
  40 -- combine contents vertically into the same cell (V/H duplicates)
  41 SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
  42  \crosstabview 1 2 3
  43
  44 -- horizontal ASC order from window function
  45 SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
  46 FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
  47  \crosstabview v h c r
  48
  49 -- horizontal DESC order from window function
  50 SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
  51 FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
  52  \crosstabview v h c r
  53
  54 -- horizontal ASC order from window function, NULLs pushed rightmost
  55 SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
  56 FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
  57  \crosstabview v h c r
  58
  59 -- only null, no column name, 2 columns: error
  60 SELECT null,null \crosstabview
  61
  62 -- only null, no column name, 3 columns: works
  63 SELECT null,null,null \crosstabview
  64
  65 -- null display
  66 \pset null '#null#'
  67 SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
  68 GROUP BY v, h ORDER BY h,v
  69  \crosstabview v h i
  70 \pset null ''
  71
  72 -- refer to columns by position
  73 SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
  74 FROM ctv_data GROUP BY v, h ORDER BY h,v
  75  \crosstabview 2 1 4
  76
  77 -- refer to columns by positions and names mixed
  78 SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
  79 FROM ctv_data GROUP BY v, h ORDER BY h,v
  80  \crosstabview 1 "h" 4
  81
  82 -- refer to columns by quoted names, check downcasing of unquoted name
  83 SELECT 1 as "22", 2 as b, 3 as "Foo"
  84  \crosstabview "22" B "Foo"
  85
  86 -- error: bad column name
  87 SELECT v,h,c,i FROM ctv_data
  88  \crosstabview v h j
  89
  90 -- error: need to quote name
  91 SELECT 1 as "22", 2 as b, 3 as "Foo"
  92  \crosstabview 1 2 Foo
  93
  94 -- error: need to not quote name
  95 SELECT 1 as "22", 2 as b, 3 as "Foo"
  96  \crosstabview 1 "B" "Foo"
  97
  98 -- error: bad column number
  99 SELECT v,h,i,c FROM ctv_data
 100  \crosstabview 2 1 5
 101
 102 -- error: same H and V columns
 103 SELECT v,h,i,c FROM ctv_data
 104  \crosstabview 2 h 4
 105
 106 -- error: too many columns
 107 SELECT a,a,1 FROM generate_series(1,3000) AS a
 108  \crosstabview
 109
 110 -- error: only one column
 111 SELECT 1 \crosstabview
 112
 113 DROP TABLE ctv_data;
 114
 115 -- check error reporting (bug #14476)
 116 CREATE TABLE ctv_data (x int, y int, v text);
 117
 118 INSERT INTO ctv_data SELECT 1, x, '*' || x FROM generate_series(1,10) x;
 119 SELECT * FROM ctv_data \crosstabview
 120
 121 INSERT INTO ctv_data VALUES (1, 10, '*'); -- duplicate data to cause error
 122 SELECT * FROM ctv_data \crosstabview
 123
 124 DROP TABLE ctv_data;