Re: PostgreSQL Metadata

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

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.

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

Yes PostgreSQL does support COMMENTing on wide
range of objects in the same manner as Oracle.

rt3=# \h comment
Command: COMMENT
Description: define or change the comment of an object
Syntax:
COMMENT ON
[
TABLE object_name |
COLUMN table_name.column_name |
AGGREGATE agg_name (agg_type) |
CONSTRAINT constraint_name ON table_name |
DATABASE object_name |
DOMAIN object_name |
FUNCTION func_name (arg1_type, arg2_type, ...) |
INDEX object_name |
OPERATOR op (leftoperand_type, rightoperand_type) |
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name
] IS 'text'

rt3=#

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