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:

NameTypeReferencesDescription
schemanamenamepg_namespace.nspnameName of schema containing table and index
tablenamenamepg_class.relnameName of table the index is for
indexnamenamepg_class.relnameName of index
tablespacenamepg_tablespace.spcnameName of tablespace containing index (null if default for database)
indexdeftextIndex 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

OmniLock - Block / Hide App on iOS

Block distractive apps from appearing on the Home Screen and App Library, enhance your focus and reduce screen time.

DNS Firewall for iOS and Mac OS

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

Ad