Re: PostgreSQL Metadata

On Thu, 30 Oct 2003, David Wagoner wrote:

> In Oracle, you can "select * from dictionary" to see the data dictionary
> table names and descriptions. Is there something similar in PostgreSQL?

Old way (still supported, not going away):

\d from a psql session

New way: select * from information_schema.[table]

where table is one of the following:

role_table_grants
applicable_roles
role_usage_grants
check_constraints
routine_privileges
column_domain_usage
routines
column_privileges
schemata
columns
sql_features
column_udt_usage
sql_implementation_info
constraint_column_usage
sql_languages
constraint_table_usage
sql_packages
data_type_privileges
sql_sizing
domain_constraints
sql_sizing_profiles
domains
table_constraints
domain_udt_usage
table_privileges
element_types
tables
enabled_roles
triggered_update_columns
information_schema_catalog_name
triggers
key_column_usage
usage_privileges
parameters
view_column_usage
referential_constraints
views
role_column_grants
view_table_usage
role_routine_grants

This is new for 7.4 and implements the SQL specced information_schema.

>
> Also, in Oracle you can store comments on tables and columns which provides
> valuable metadata. Is this also possible in PostgreSQL? Here is an example
> create table statement in Oracle with comments:
>
> CREATE TABLE lock_date
> (
> lock_id NUMBER(9) NOT NULL,
> )
> TABLESPACE data
> /
> -- Comments for LOCK_DATE
> COMMENT ON TABLE lock_date IS 'Locks out data that arrives after
> Billing and Reporting periods.'
> /
> -- Column Comments for LOCK_DATE
> COMMENT ON COLUMN lock_date.lock_id IS 'Surrogate Primary Key for
> the LOCK_DATE table.'
> /
>
>
> Best regards,
>
> David B. Wagoner
> Database Administrator
> Arsenal Digital Solutions
> Web: http://www.arsenaldigital.com
>
> <<...OLE_Obj...>>
>
>
> The contents of this e-mail message may be privileged and/or confidential.
> If you are not the intended recipient, any review, dissemination, copying,
> distribution or other use of the contents of this message or any attachment
> by you is strictly prohibited. If you receive this communication in error,
> please notify us immediately by return e-mail or by telephone
> (919-466-6700), and please delete this message and all attachments from your
> system.
> Thank you.
>
>