RSS

PostgreSQL mini cookbook: Dealing with the system tables

PostgreSQL mini cookbook: Dealing with the system tables, those tricks back to 2001 and still works today. 😉

Seeing what tables exist

You want to check if a table already exists.

Solution

There is a view called pg_tables or the system table pg_class - you can query either. There is less information in pg_tables but it is easier to understand.

List all the tables, similar to \dt:

SELECT * FROM pg_tables
  WHERE schemaname!='pg_catalog'
    AND schemaname!='information_schema';
SELECT * FROM pg_tables WHERE tablename LIKE 'foo%';

SELECT * FROM pg_class WHERE relname LIKE 'foo%';

Example: view all user defined tables:

=# SELECT * FROM pg_tables
    WHERE schemaname!='pg_catalog'
      AND schemaname!='information_schema';

 schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
 public     | film_category | foo        |            | t          | f        | t
 public     | staff         | foo        |            | t          | f        | t
 public     | country       | foo        |            | t          | f        | t
 public     | inventory     | foo        |            | t          | f        | t
 public     | rental        | foo        |            | t          | f        | t
 public     | store         | foo        |            | t          | f        | t
 public     | city          | foo        |            | t          | f        | t
 public     | actor         | foo        |            | t          | f        | t
 public     | payment       | foo        |            | t          | f        | t
 public     | address       | foo        |            | t          | f        | t
 public     | language      | foo        |            | t          | f        | t
 public     | film_actor    | foo        |            | t          | f        | t
 public     | category      | foo        |            | t          | f        | t
 public     | film          | foo        |            | t          | f        | t
 public     | customer      | foo        |            | t          | f        | t
(15 rows)

Example: view all pg_catalog tables:

=# SELECT * FROM pg_tables
-#   WHERE schemaname='pg_catalog';

 schemaname |        tablename        | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-------------------------+------------+------------+------------+----------+-------------
 pg_catalog | pg_statistic            | foo        |            | t          | f        | f
 pg_catalog | pg_type                 | foo        |            | t          | f        | f
 pg_catalog | pg_authid               | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_attribute            | foo        |            | t          | f        | f
 pg_catalog | pg_proc                 | foo        |            | t          | f        | f
 pg_catalog | pg_user_mapping         | foo        |            | t          | f        | f
 pg_catalog | pg_attrdef              | foo        |            | t          | f        | f
 pg_catalog | pg_constraint           | foo        |            | t          | f        | f
 pg_catalog | pg_index                | foo        |            | t          | f        | f
 pg_catalog | pg_operator             | foo        |            | t          | f        | f
 pg_catalog | pg_opfamily             | foo        |            | t          | f        | f
 pg_catalog | pg_opclass              | foo        |            | t          | f        | f
 pg_catalog | pg_am                   | foo        |            | t          | f        | f
 pg_catalog | pg_amop                 | foo        |            | t          | f        | f
 pg_catalog | pg_amproc               | foo        |            | t          | f        | f
 pg_catalog | pg_largeobject_metadata | foo        |            | t          | f        | f
 pg_catalog | pg_aggregate            | foo        |            | t          | f        | f
 pg_catalog | pg_rewrite              | foo        |            | t          | f        | f
 pg_catalog | pg_trigger              | foo        |            | t          | f        | f
 pg_catalog | pg_event_trigger        | foo        |            | t          | f        | f
 pg_catalog | pg_description          | foo        |            | t          | f        | f
 pg_catalog | pg_cast                 | foo        |            | t          | f        | f
 pg_catalog | pg_enum                 | foo        |            | t          | f        | f
 pg_catalog | pg_namespace            | foo        |            | t          | f        | f
 pg_catalog | pg_conversion           | foo        |            | t          | f        | f
 pg_catalog | pg_depend               | foo        |            | t          | f        | f
 pg_catalog | pg_database             | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_db_role_setting      | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_tablespace           | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_pltemplate           | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_auth_members         | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_shdepend             | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_shdescription        | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_ts_config            | foo        |            | t          | f        | f
 pg_catalog | pg_ts_config_map        | foo        |            | t          | f        | f
 pg_catalog | pg_ts_dict              | foo        |            | t          | f        | f
 pg_catalog | pg_ts_parser            | foo        |            | t          | f        | f
 pg_catalog | pg_ts_template          | foo        |            | t          | f        | f
 pg_catalog | pg_extension            | foo        |            | t          | f        | f
 pg_catalog | pg_foreign_data_wrapper | foo        |            | t          | f        | f
 pg_catalog | pg_foreign_server       | foo        |            | t          | f        | f
 pg_catalog | pg_foreign_table        | foo        |            | t          | f        | f
 pg_catalog | pg_default_acl          | foo        |            | t          | f        | f
 pg_catalog | pg_seclabel             | foo        |            | t          | f        | f
 pg_catalog | pg_shseclabel           | foo        | pg_global  | t          | f        | f
 pg_catalog | pg_collation            | foo        |            | t          | f        | f
 pg_catalog | pg_range                | foo        |            | t          | f        | f
 pg_catalog | pg_largeobject          | foo        |            | t          | f        | f
 pg_catalog | pg_class                | foo        |            | t          | f        | f
 pg_catalog | pg_inherits             | foo        |            | t          | f        | f
 pg_catalog | pg_language             | foo        |            | t          | f        | f
(51 rows)

Example: view all information_schema tables:

=# SELECT * FROM pg_tables
-#   WHERE schemaname='information_schema';

     schemaname     |        tablename        | tableowner | tablespace | hasindexes | hasrules | hastriggers
--------------------+-------------------------+------------+------------+------------+----------+-------------
 information_schema | sql_implementation_info | foo        |            | f          | f        | f
 information_schema | sql_languages           | foo        |            | f          | f        | f
 information_schema | sql_packages            | foo        |            | f          | f        | f
 information_schema | sql_sizing              | foo        |            | f          | f        | f
 information_schema | sql_sizing_profiles     | foo        |            | f          | f        | f
 information_schema | sql_features            | foo        |            | f          | f        | f
 information_schema | sql_parts               | foo        |            | f          | f        | f
(7 rows)

Viewing triggers and their functions

You want a list of triggers and the functions they call on certain tables.

Solution

To see the triggers and functions on tables foo_xxx use:

SELECT cl.relname,
       tr.tgname AS triggername,
       tr.tgenabled,
       fn.proname AS func_name
  FROM pg_trigger AS tr,
       pg_class AS cl,
       pg_proc AS fn
  WHERE tr.tgrelid=cl.oid
    AND tr.tgfoid=fn.oid
    AND cl.relname ˜ '^foo%';

Example:

=# SELECT cl.relname,
-#        tr.tgname AS triggername,
-#        tr.tgenabled,
-#        fn.proname AS func_name
-#   FROM pg_trigger AS tr,
-#        pg_class AS cl,
-#        pg_proc AS fn
-#   WHERE tr.tgrelid=cl.oid
-#     AND tr.tgfoid=fn.oid;

    relname    |         triggername          | tgenabled |        func_name
---------------+------------------------------+-----------+-------------------------
 film          | film_fulltext_trigger        | O         | tsvector_update_trigger
 address       | last_updated                 | O         | last_updated
 store         | last_updated                 | O         | last_updated
 address       | RI_ConstraintTrigger_a_72626 | O         | RI_FKey_restrict_del
 address       | RI_ConstraintTrigger_a_72627 | O         | RI_FKey_cascade_upd
 customer      | RI_ConstraintTrigger_c_72628 | O         | RI_FKey_check_ins
 ...

Viewing function bodies

You want to see function definition: the source for a previously defined function.

Solution

You can use either of the following:

\df+ myfunc

or

SELECT oid, proname, prosrc
  FROM pg_proc
  WHERE proname='myfunc';

Example:

=# \df
                                                           List of functions
 Schema |            Name            | Result data type |                         Argument data types                         |  Type
--------+----------------------------+------------------+---------------------------------------------------------------------+---------
 public | _group_concat              | text             | text, text                                                          | normal
 public | film_in_stock              | SETOF integer    | p_film_id integer, p_store_id integer, OUT p_film_count integer     | normal
 public | film_not_in_stock          | SETOF integer    | p_film_id integer, p_store_id integer, OUT p_film_count integer     | normal
 public | get_customer_balance       | numeric          | p_customer_id integer, p_effective_date timestamp without time zone | normal
 public | group_concat               | text             | text                                                                | agg
 public | inventory_held_by_customer | integer          | p_inventory_id integer                                              | normal
 public | inventory_in_stock         | boolean          | p_inventory_id integer                                              | normal
 public | last_day                   | date             | timestamp without time zone                                         | normal
 public | last_updated               | trigger          |                                                                     | trigger
 public | rewards_report             | SETOF customer   | min_monthly_purchases integer, min_dollar_amount_purchased numeric  | normal
(10 rows)

=# \df film_in_stock
                                                  List of functions
 Schema |     Name      | Result data type |                       Argument data types                       |  Type
--------+---------------+------------------+-----------------------------------------------------------------+--------
 public | film_in_stock | SETOF integer    | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal
(1 row)

=# \df+ film_in_stock
                                                                                                     List of functions
 Schema |     Name      | Result data type |                       Argument data types                       |  Type  | Security | Volatility | Owner | Language |                Source code                 | Description
--------+---------------+------------------+-----------------------------------------------------------------+--------+----------+------------+-------+----------+--------------------------------------------+-------------
 public | film_in_stock | SETOF integer    | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal | invoker  | volatile   | ming  | sql      |                                           +|
        |               |                  |                                                                 |        |          |            |       |          |      SELECT inventory_id                  +|
        |               |                  |                                                                 |        |          |            |       |          |      FROM inventory                       +|
        |               |                  |                                                                 |        |          |            |       |          |      WHERE film_id = $1                   +|
        |               |                  |                                                                 |        |          |            |       |          |      AND store_id = $2                    +|
        |               |                  |                                                                 |        |          |            |       |          |      AND inventory_in_stock(inventory_id);+|
        |               |                  |                                                                 |        |          |            |       |          |                                            |
(1 row)

Example: Use pg_proc table to view function definition:

=# SELECT oid, proname, prosrc
-=   FROM pg_proc
-=   WHERE proname='film_in_stock';

  oid  |    proname    |                   prosrc
-------+---------------+--------------------------------------------
 72408 | film_in_stock |                                           +
       |               |      SELECT inventory_id                  +
       |               |      FROM inventory                       +
       |               |      WHERE film_id = $1                   +
       |               |      AND store_id = $2                    +
       |               |      AND inventory_in_stock(inventory_id);+
       |               |
(1 row)

Viewing rules

You want to see what rules you have defined for a particular view/table.

Solution

Use the pg_rules system table.

SELECT * FROM pg_rules WHERE tablename='foo';

Example:

=# SELECT * FROM pg_rules;
 schemaname |  tablename  |   rulename    |                     definition
------------+-------------+---------------+--------------------------------------------------------------
 pg_catalog | pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS
            |             |               |     ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING;
 pg_catalog | pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS
            |             |               |     ON UPDATE TO pg_catalog.pg_settings
            |             |               |    WHERE (new.name = old.name)
            |             |               |     DO SELECT set_config(old.name, new.setting, false)
            |             |               |       AS set_config;

Viewing indexes

You want to see what indexes you have defined for a particular view/table.

Solution

Use the pg_indexes system table.

The view pg_indexes provides access to useful information about each index in the database.

pg_indexes Columns:

Name Type References Description
schemaname name pg_namespace.nspname Name of schema containing table and index
tablename name pg_class.relname Name of table the index is for
indexname name pg_class.relname Name of index
tablespace name pg_tablespace.spcname Name of tablespace containing index (null if default for database)
indexdef text Index definition (a reconstructed CREATE INDEX command)

Example: List all the indexes and its definition:

=# SELECT tablename,
-#        indexname,
-#        indexdef AS definition
-# FROM pg_indexes
-# WHERE
-#     schemaname = 'public'
-# ORDER BY
-#     tablename,
-#     indexname;

   tablename |        indexname    |                                             definition
-------------+---------------------+---------------------------------------------------------------------------------------------------
 actor       | actor_pkey          | CREATE UNIQUE INDEX actor_pkey ON public.actor USING btree (actor_id)
 actor       | idx_actor_last_name | CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name)
 address     | address_pkey        | CREATE UNIQUE INDEX address_pkey ON public.address USING btree (address_id)
 address     | idx_fk_city_id      | CREATE INDEX idx_fk_city_id ON public.address USING btree (city_id)
 category    | category_pkey       | CREATE UNIQUE INDEX category_pkey ON public.category USING btree (category_id)
 city        | city_pkey           | CREATE UNIQUE INDEX city_pkey ON public.citUSING btree (city_id)

You can also use \d <tablename>:

=# \d actor
                                         Table "public.actor"
   Column    |            Type             |                        Modifiers
-------------+-----------------------------+----------------------------------------------------------
 actor_id    | integer                     | not null default nextval('actor_actor_id_seq'::regclass)
 first_name  | character varying(45)       | not null
 last_name   | character varying(45)       | not null
 last_update | timestamp without time zone | not null default now()
Indexes:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
    "idx_actor_last_name" btree (last_name)
Referenced by:
    TABLE "film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

References

DNS Firewall for iOS

Encrypted your DNS to protect your privacy and firewall to block phishing, malicious domains, block ads in all browsers and apps

Ad