pg_stats

A system catalogue view providing non-superuser access to pg_statistic

pg_stats is a system catalogue view providing non-superuser access to pg_statistic, and also presents the information contained in pg_statistic in a more readable format.

pg_stats was added in PostgreSQL 7.3 (commit 16ea152b).

Definition by PostgreSQL version

pg_stats (PostgreSQL 13)

                     View "pg_catalog.pg_stats"
         Column         |   Type   | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
 schemaname             | name     |           |          |
 tablename              | name     |           |          |
 attname                | name     |           |          |
 inherited              | boolean  |           |          |
 null_frac              | real     |           |          |
 avg_width              | integer  |           |          |
 n_distinct             | real     |           |          |
 most_common_vals       | anyarray |           |          |
 most_common_freqs      | real[]   |           |          |
 histogram_bounds       | anyarray |           |          |
 correlation            | real     |           |          |
 most_common_elems      | anyarray |           |          |
 most_common_elem_freqs | real[]   |           |          |
 elem_count_histogram   | real[]   |           |          |
    

Documentation: pg_stats

pg_stats (PostgreSQL 12)

                     View "pg_catalog.pg_stats"
         Column         |   Type   | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
 schemaname             | name     |           |          |
 tablename              | name     |           |          |
 attname                | name     |           |          |
 inherited              | boolean  |           |          |
 null_frac              | real     |           |          |
 avg_width              | integer  |           |          |
 n_distinct             | real     |           |          |
 most_common_vals       | anyarray |           |          |
 most_common_freqs      | real[]   |           |          |
 histogram_bounds       | anyarray |           |          |
 correlation            | real     |           |          |
 most_common_elems      | anyarray |           |          |
 most_common_elem_freqs | real[]   |           |          |
 elem_count_histogram   | real[]   |           |          |
    

Documentation: pg_stats

pg_stats (PostgreSQL 11)

                     View "pg_catalog.pg_stats"
         Column         |   Type   | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
 schemaname             | name     |           |          |
 tablename              | name     |           |          |
 attname                | name     |           |          |
 inherited              | boolean  |           |          |
 null_frac              | real     |           |          |
 avg_width              | integer  |           |          |
 n_distinct             | real     |           |          |
 most_common_vals       | anyarray |           |          |
 most_common_freqs      | real[]   |           |          |
 histogram_bounds       | anyarray |           |          |
 correlation            | real     |           |          |
 most_common_elems      | anyarray |           |          |
 most_common_elem_freqs | real[]   |           |          |
 elem_count_histogram   | real[]   |           |          |
    

Documentation: pg_stats

pg_stats (PostgreSQL 10)

                     View "pg_catalog.pg_stats"
         Column         |   Type   | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
 schemaname             | name     |           |          |
 tablename              | name     |           |          |
 attname                | name     |           |          |
 inherited              | boolean  |           |          |
 null_frac              | real     |           |          |
 avg_width              | integer  |           |          |
 n_distinct             | real     |           |          |
 most_common_vals       | anyarray |           |          |
 most_common_freqs      | real[]   |           |          |
 histogram_bounds       | anyarray |           |          |
 correlation            | real     |           |          |
 most_common_elems      | anyarray |           |          |
 most_common_elem_freqs | real[]   |           |          |
 elem_count_histogram   | real[]   |           |          |
    

Documentation: pg_stats

pg_stats (PostgreSQL 9.6)

          View "pg_catalog.pg_stats"
         Column         |   Type   | Modifiers
------------------------+----------+-----------
 schemaname             | name     |
 tablename              | name     |
 attname                | name     |
 inherited              | boolean  |
 null_frac              | real     |
 avg_width              | integer  |
 n_distinct             | real     |
 most_common_vals       | anyarray |
 most_common_freqs      | real[]   |
 histogram_bounds       | anyarray |
 correlation            | real     |
 most_common_elems      | anyarray |
 most_common_elem_freqs | real[]   |
 elem_count_histogram   | real[]   |
    

Documentation: pg_stats

pg_stats (PostgreSQL 9.5)

          View "pg_catalog.pg_stats"
         Column         |   Type   | Modifiers
------------------------+----------+-----------
 schemaname             | name     |
 tablename              | name     |
 attname                | name     |
 inherited              | boolean  |
 null_frac              | real     |
 avg_width              | integer  |
 n_distinct             | real     |
 most_common_vals       | anyarray |
 most_common_freqs      | real[]   |
 histogram_bounds       | anyarray |
 correlation            | real     |
 most_common_elems      | anyarray |
 most_common_elem_freqs | real[]   |
 elem_count_histogram   | real[]   |
    

Documentation: pg_stats

pg_stats (PostgreSQL 9.4)

          View "pg_catalog.pg_stats"
         Column         |   Type   | Modifiers
------------------------+----------+-----------
 schemaname             | name     |
 tablename              | name     |
 attname                | name     |
 inherited              | boolean  |
 null_frac              | real     |
 avg_width              | integer  |
 n_distinct             | real     |
 most_common_vals       | anyarray |
 most_common_freqs      | real[]   |
 histogram_bounds       | anyarray |
 correlation            | real     |
 most_common_elems      | anyarray |
 most_common_elem_freqs | real[]   |
 elem_count_histogram   | real[]   |
    

Documentation: pg_stats

pg_stats (PostgreSQL 9.3)

          View "pg_catalog.pg_stats"
         Column         |   Type   | Modifiers
------------------------+----------+-----------
 schemaname             | name     |
 tablename              | name     |
 attname                | name     |
 inherited              | boolean  |
 null_frac              | real     |
 avg_width              | integer  |
 n_distinct             | real     |
 most_common_vals       | anyarray |
 most_common_freqs      | real[]   |
 histogram_bounds       | anyarray |
 correlation            | real     |
 most_common_elems      | anyarray |
 most_common_elem_freqs | real[]   |
 elem_count_histogram   | real[]   |
    

Documentation: pg_stats

pg_stats (PostgreSQL 9.2)

          View "pg_catalog.pg_stats"
         Column         |   Type   | Modifiers
------------------------+----------+-----------
 schemaname             | name     |
 tablename              | name     |
 attname                | name     |
 inherited              | boolean  |
 null_frac              | real     |
 avg_width              | integer  |
 n_distinct             | real     |
 most_common_vals       | anyarray |
 most_common_freqs      | real[]   |
 histogram_bounds       | anyarray |
 correlation            | real     |
 most_common_elems      | anyarray |
 most_common_elem_freqs | real[]   |
 elem_count_histogram   | real[]   |
    

Documentation: pg_stats

pg_stats (PostgreSQL 9.1)

        View "pg_catalog.pg_stats"
      Column       |   Type   | Modifiers
-------------------+----------+-----------
 schemaname        | name     |
 tablename         | name     |
 attname           | name     |
 inherited         | boolean  |
 null_frac         | real     |
 avg_width         | integer  |
 n_distinct        | real     |
 most_common_vals  | anyarray |
 most_common_freqs | real[]   |
 histogram_bounds  | anyarray |
 correlation       | real     |
    

Documentation: pg_stats

pg_stats (PostgreSQL 9.0)

        View "pg_catalog.pg_stats"
      Column       |   Type   | Modifiers
-------------------+----------+-----------
 schemaname        | name     |
 tablename         | name     |
 attname           | name     |
 inherited         | boolean  |
 null_frac         | real     |
 avg_width         | integer  |
 n_distinct        | real     |
 most_common_vals  | anyarray |
 most_common_freqs | real[]   |
 histogram_bounds  | anyarray |
 correlation       | real     |
    

Documentation: pg_stats

pg_stats (PostgreSQL 8.4)

        View "pg_catalog.pg_stats"
      Column       |   Type   | Modifiers
-------------------+----------+-----------
 schemaname        | name     |
 tablename         | name     |
 attname           | name     |
 null_frac         | real     |
 avg_width         | integer  |
 n_distinct        | real     |
 most_common_vals  | anyarray |
 most_common_freqs | real[]   |
 histogram_bounds  | anyarray |
 correlation       | real     |
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct,
        CASE
            WHEN s.stakind1 = ANY (ARRAY[1, 4]) THEN s.stavalues1
            WHEN s.stakind2 = ANY (ARRAY[1, 4]) THEN s.stavalues2
            WHEN s.stakind3 = ANY (ARRAY[1, 4]) THEN s.stavalues3
            WHEN s.stakind4 = ANY (ARRAY[1, 4]) THEN s.stavalues4
            ELSE NULL::anyarray
        END AS most_common_vals,
        CASE
            WHEN s.stakind1 = ANY (ARRAY[1, 4]) THEN s.stanumbers1
            WHEN s.stakind2 = ANY (ARRAY[1, 4]) THEN s.stanumbers2
            WHEN s.stakind3 = ANY (ARRAY[1, 4]) THEN s.stanumbers3
            WHEN s.stakind4 = ANY (ARRAY[1, 4]) THEN s.stanumbers4
            ELSE NULL::real[]
        END AS most_common_freqs,
        CASE
            WHEN s.stakind1 = 2 THEN s.stavalues1
            WHEN s.stakind2 = 2 THEN s.stavalues2
            WHEN s.stakind3 = 2 THEN s.stavalues3
            WHEN s.stakind4 = 2 THEN s.stavalues4
            ELSE NULL::anyarray
        END AS histogram_bounds,
        CASE
            WHEN s.stakind1 = 3 THEN s.stanumbers1[1]
            WHEN s.stakind2 = 3 THEN s.stanumbers2[1]
            WHEN s.stakind3 = 3 THEN s.stanumbers3[1]
            WHEN s.stakind4 = 3 THEN s.stanumbers4[1]
            ELSE NULL::real
        END AS correlation
   FROM pg_statistic s
   JOIN pg_class c ON c.oid = s.starelid
   JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text);
    

Documentation: pg_stats

pg_stats (PostgreSQL 8.3)

        View "pg_catalog.pg_stats"
      Column       |   Type   | Modifiers
-------------------+----------+-----------
 schemaname        | name     |
 tablename         | name     |
 attname           | name     |
 null_frac         | real     |
 avg_width         | integer  |
 n_distinct        | real     |
 most_common_vals  | anyarray |
 most_common_freqs | real[]   |
 histogram_bounds  | anyarray |
 correlation       | real     |
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct,
        CASE 1
            WHEN s.stakind1 THEN s.stavalues1
            WHEN s.stakind2 THEN s.stavalues2
            WHEN s.stakind3 THEN s.stavalues3
            WHEN s.stakind4 THEN s.stavalues4
            ELSE NULL::anyarray
        END AS most_common_vals,
        CASE 1
            WHEN s.stakind1 THEN s.stanumbers1
            WHEN s.stakind2 THEN s.stanumbers2
            WHEN s.stakind3 THEN s.stanumbers3
            WHEN s.stakind4 THEN s.stanumbers4
            ELSE NULL::real[]
        END AS most_common_freqs,
        CASE 2
            WHEN s.stakind1 THEN s.stavalues1
            WHEN s.stakind2 THEN s.stavalues2
            WHEN s.stakind3 THEN s.stavalues3
            WHEN s.stakind4 THEN s.stavalues4
            ELSE NULL::anyarray
        END AS histogram_bounds,
        CASE 3
            WHEN s.stakind1 THEN s.stanumbers1[1]
            WHEN s.stakind2 THEN s.stanumbers2[1]
            WHEN s.stakind3 THEN s.stanumbers3[1]
            WHEN s.stakind4 THEN s.stanumbers4[1]
            ELSE NULL::real
        END AS correlation
   FROM pg_statistic s
   JOIN pg_class c ON c.oid = s.starelid
   JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE has_table_privilege(c.oid, 'select'::text);
    

Documentation: pg_stats

pg_stats (PostgreSQL 8.2)

        View "pg_catalog.pg_stats"
      Column       |   Type   | Modifiers
-------------------+----------+-----------
 schemaname        | name     |
 tablename         | name     |
 attname           | name     |
 null_frac         | real     |
 avg_width         | integer  |
 n_distinct        | real     |
 most_common_vals  | anyarray |
 most_common_freqs | real[]   |
 histogram_bounds  | anyarray |
 correlation       | real     |
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct,
        CASE 1
            WHEN s.stakind1 THEN s.stavalues1
            WHEN s.stakind2 THEN s.stavalues2
            WHEN s.stakind3 THEN s.stavalues3
            WHEN s.stakind4 THEN s.stavalues4
            ELSE NULL::anyarray
        END AS most_common_vals,
        CASE 1
            WHEN s.stakind1 THEN s.stanumbers1
            WHEN s.stakind2 THEN s.stanumbers2
            WHEN s.stakind3 THEN s.stanumbers3
            WHEN s.stakind4 THEN s.stanumbers4
            ELSE NULL::real[]
        END AS most_common_freqs,
        CASE 2
            WHEN s.stakind1 THEN s.stavalues1
            WHEN s.stakind2 THEN s.stavalues2
            WHEN s.stakind3 THEN s.stavalues3
            WHEN s.stakind4 THEN s.stavalues4
            ELSE NULL::anyarray
        END AS histogram_bounds,
        CASE 3
            WHEN s.stakind1 THEN s.stanumbers1[1]
            WHEN s.stakind2 THEN s.stanumbers2[1]
            WHEN s.stakind3 THEN s.stanumbers3[1]
            WHEN s.stakind4 THEN s.stanumbers4[1]
            ELSE NULL::real
        END AS correlation
   FROM pg_statistic s
   JOIN pg_class c ON c.oid = s.starelid
   JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE has_table_privilege(c.oid, 'select'::text);
    

Documentation: pg_stats

Change history

Work-in-progress

Categories

System catalogue

See also

pg_statistic, pg_stats_ext