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.

Definition by PostgreSQL version

pg_stats (PostgreSQL 15)

                     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 14)

                     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 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

Categories

System catalogue

See also

pg_statistic, pg_stats_ext, pg_stats_ext_exprs