This page in other versions: 9.0 / 9.1 / 9.2 / 9.3 / 9.4  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

Chapter 43. System Catalogs

Table of Contents
43.1. Overview
43.2. pg_aggregate
43.3. pg_am
43.4. pg_amop
43.5. pg_amproc
43.6. pg_attrdef
43.7. pg_attribute
43.8. pg_cast
43.9. pg_class
43.10. pg_constraint
43.11. pg_conversion
43.12. pg_database
43.13. pg_depend
43.14. pg_description
43.15. pg_group
43.16. pg_index
43.17. pg_inherits
43.18. pg_language
43.19. pg_largeobject
43.20. pg_listener
43.21. pg_namespace
43.22. pg_opclass
43.23. pg_operator
43.24. pg_proc
43.25. pg_rewrite
43.26. pg_shadow
43.27. pg_statistic
43.28. pg_trigger
43.29. pg_type
43.30. System Views
43.31. pg_indexes
43.32. pg_locks
43.33. pg_rules
43.34. pg_settings
43.35. pg_stats
43.36. pg_tables
43.37. pg_user
43.38. pg_views

The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL's system catalogs are regular tables. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. Normally, one should not change the system catalogs by hand, there are always SQL commands to do that. (For example, CREATE DATABASE inserts a row into the pg_database catalog --- and actually creates the database on disk.) There are some exceptions for particularly esoteric operations, such as adding index access methods.

43.1. Overview

Table 43-1 lists the system catalogs. More detailed documentation of each catalog follows below.

Most system catalogs are copied from the template database during database creation and are thereafter database-specific. A few catalogs are physically shared across all databases in a cluster; these are marked in the descriptions of the individual catalogs.

Table 43-1. System Catalogs

Catalog Name Purpose
pg_aggregate aggregate functions
pg_am index access methods
pg_amop access method operators
pg_amproc access method support procedures
pg_attrdef column default values
pg_attribute table columns ("attributes")
pg_cast casts (data type conversions)
pg_class tables, indexes, sequences ("relations")
pg_constraint check constraints, unique constraints, primary key constraints, foreign key constraints
pg_conversion encoding conversion information
pg_database databases within this database cluster
pg_depend dependencies between database objects
pg_description descriptions or comments on database objects
pg_group groups of database users
pg_index additional index information
pg_inherits table inheritance hierarchy
pg_language languages for writing functions
pg_largeobject large objects
pg_listener asynchronous notification support
pg_namespace schemas
pg_opclass index access method operator classes
pg_operator operators
pg_proc functions and procedures
pg_rewrite query rewrite rules
pg_shadow database users
pg_statistic planner statistics
pg_trigger triggers
pg_type data types

Comments


May 19, 2005, 4:10 a.m.

If you need something similar to the DESCRIBE command available in MySQL or Oracle in order to get information about the columns of a table, then you can do something like:

SELECT
-- Field
pg_attribute.attname AS "Field",
-- Type
CASE pg_type.typname
WHEN 'int2' THEN 'smallint'
WHEN 'int4' THEN 'int'
WHEN 'int8' THEN 'bigint'
WHEN 'varchar' THEN 'varchar(' || pg_attribute.atttypmod-4 || ')'
ELSE pg_type.typname
END AS "Type",
-- Null
CASE WHEN pg_attribute.attnotnull THEN ''
ELSE 'YES'
END AS "Null",
-- Default
CASE pg_type.typname
WHEN 'varchar' THEN substring(pg_attrdef.adsrc from '^\'(.*)\'.*$')
ELSE pg_attrdef.adsrc
END AS "Default"
FROM pg_class
INNER JOIN pg_attribute
ON (pg_class.oid=pg_attribute.attrelid)
INNER JOIN pg_type
ON (pg_attribute.atttypid=pg_type.oid)
LEFT JOIN pg_attrdef
ON (pg_class.oid=pg_attrdef.adrelid AND pg_attribute.attnum=pg_attrdef.adnum)
WHERE pg_class.relname='table_name' AND pg_attribute.attnum>=1 AND NOT pg_attribute.attisdropped
ORDER BY pg_attribute.attnum;

where table_name is the name of the table (relation) whose columns you want to get information about.
However be aware that, unlike the MySQL DESCRIBE command, if there is no table named table_name, then the above SELECT command will simply display 0 row (no error).


May 20, 2005, 7:57 a.m.

Ooops... sorry! The above query gives:
ERROR: syntax error at or near "(" at character 386

Replace line 18
WHEN 'varchar' THEN substring(pg_attrdef.adsrc from '^'(.*)'.*$')
by
WHEN 'varchar' THEN substring(pg_attrdef.adsrc from '^\'(.*)\'.*$')
and it should work fine.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group