Обсуждение: dictionary?
How do you accomplish this in pg? 1. Is there a table like the Oracle dictionary? 2. Is there a way to describe a table? I use these all the time in oracle. When I can't remember where some management metric is located in the system, I sort of grep for it based on a keyword. For example, if I need to find out about some snapshot (copy of a table from another db), I do this: select table_name from dictionary where table_name like '%SNAPSHOT%'; TABLE_NAME ------------------------------ ALL_REGISTERED_SNAPSHOTS ALL_SNAPSHOTS ... SQL> describe ALL_SNAPSHOTS Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) ... select name from ALL_SNAPSHOTS; ... TIA.
> How do you accomplish this in pg? > > 1. Is there a table like the Oracle dictionary? > 2. Is there a way to describe a table? > > I use these all the time in oracle. When I can't remember where > some management metric is located in the system, I sort of grep for > it based on a keyword. For example, if I need to find out about some > snapshot (copy of a table from another db), I do this: > > > select table_name from dictionary where table_name like '%SNAPSHOT%'; > > TABLE_NAME > ------------------------------ > ALL_REGISTERED_SNAPSHOTS > ALL_SNAPSHOTS > ... > > > SQL> describe ALL_SNAPSHOTS > Name Null? Type > ----------------------------------------- -------- ---------------------------- > OWNER NOT NULL VARCHAR2(30) > NAME NOT NULL VARCHAR2(30) > TABLE_NAME NOT NULL VARCHAR2(30) > ... > > > select name from ALL_SNAPSHOTS; > ... How about: \d tablename \dt tablename For more information check out the "Meta-Commands" from: http://www.postgresql.org/docs/8.1/interactive/app-psql.html Regards, Richard Broersma Jr.
You can query pg_tables, pg_indexes, etc.
Select tablename from pg_tables where tablename like 'snapshot%' ;
These views are based on the pg_class system table.
To describe a table, just do \d tablename.
--
Husam
http://firstdba.googlepages.com
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ray Stell
Sent: Friday, September 22, 2006 12:16 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] dictionary?
How do you accomplish this in pg?
1. Is there a table like the Oracle dictionary?
2. Is there a way to describe a table?
I use these all the time in oracle. When I can't remember where
some management metric is located in the system, I sort of grep for
it based on a keyword. For example, if I need to find out about some
snapshot (copy of a table from another db), I do this:
select table_name from dictionary where table_name like '%SNAPSHOT%';
TABLE_NAME
------------------------------
ALL_REGISTERED_SNAPSHOTS
ALL_SNAPSHOTS
...
SQL> describe ALL_SNAPSHOTS
Name Null? Type
----------------------------------------- --------
----------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
...
select name from ALL_SNAPSHOTS;
...
TIA.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may
containinformation that is legally privileged. If you are not the addressee, or the person responsible for delivering
itto the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is
strictlyprohibited. If you have received this message by mistake, please immediately notify us by replying to the
messageand delete the original message immediately thereafter.
Thank you.
FADLD Tag
**********************************************************************
> > great, how about the other half, finding the table of interest? > > > > On Fri, Sep 22, 2006 at 12:28:50PM -0700, Richard Broersma Jr wrote: > > > How do you accomplish this in pg? > > > > > > 1. Is there a table like the Oracle dictionary? > > > 2. Is there a way to describe a table? > > > > > > I use these all the time in oracle. When I can't remember where > > > some management metric is located in the system, I sort of grep for > > > it based on a keyword. For example, if I need to find out about some > > > snapshot (copy of a table from another db), I do this: > > > > > > > > > select table_name from dictionary where table_name like '%SNAPSHOT%'; > > > > > > TABLE_NAME > > > ------------------------------ > > > ALL_REGISTERED_SNAPSHOTS > > > ALL_SNAPSHOTS > > > ... > > > > > > > > > SQL> describe ALL_SNAPSHOTS > > > Name Null? Type > > > ----------------------------------------- -------- ---------------------------- > > > OWNER NOT NULL VARCHAR2(30) > > > NAME NOT NULL VARCHAR2(30) > > > TABLE_NAME NOT NULL VARCHAR2(30) > > > ... > > > > > > > > > select name from ALL_SNAPSHOTS; > > > ... > > > > > > How about: > > > > \d tablename > > \dt tablename > > > > For more information check out the "Meta-Commands" from: > > http://www.postgresql.org/docs/8.1/interactive/app-psql.html > > > > Regards, > > > > Richard Broersma Jr. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: don't forget to increase your free space map settings > > -- > Don't forget to CC the list so that everyone can reply. Here is the verbage from the PostgreSQL manual: ( pattern can be a Regular Expression ) Notice \d, it will display most everything. Is this what you were looking for? \d [ pattern ] \d+ [ pattern ] For each relation (table, view, index, or sequence) matching the pattern, show all columns, their types, the tablespace (if not the default) and any special attributes such as NOT NULL or defaults, if any. Associated indexes, constraints, rules, and triggers are also shown, as is the view definition if the relation is a view. ("Matching the pattern" is defined below.) The command form \d+ is identical, except that more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table. Note: If \d is used without a pattern argument, it is equivalent to \dtvs which will show a list of all tables, views, and sequences. This is purely a convenience measure.
cool:
template1=# Select tablename from pg_tables where tablename like '%features%';
tablename
--------------
sql_features
(1 row)
template1=# \d sql_features
Did not find any relation named "sql_features".
template1=# \dT+sql_features
List of data types
Schema | Name | Internal name | Size | Description
------------+-----------------------------+------------------+------+-------------------------------------------------------------------
pg_catalog | abstime | abstime | 4 | absolute, limited-range date and time (Unix
systemtime)
pg_catalog | aclitem | aclitem | 12 | access control list
....
On Fri, Sep 22, 2006 at 12:40:35PM -0700, Tomeh, Husam wrote:
>
> You can query pg_tables, pg_indexes, etc.
>
> Select tablename from pg_tables where tablename like 'snapshot%' ;
>
> These views are based on the pg_class system table.
>
> To describe a table, just do \d tablename.
>
> --
> Husam
> http://firstdba.googlepages.com
>
>
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ray Stell
> Sent: Friday, September 22, 2006 12:16 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] dictionary?
>
> How do you accomplish this in pg?
>
> 1. Is there a table like the Oracle dictionary?
> 2. Is there a way to describe a table?
>
> I use these all the time in oracle. When I can't remember where
> some management metric is located in the system, I sort of grep for
> it based on a keyword. For example, if I need to find out about some
> snapshot (copy of a table from another db), I do this:
>
>
> select table_name from dictionary where table_name like '%SNAPSHOT%';
>
> TABLE_NAME
> ------------------------------
> ALL_REGISTERED_SNAPSHOTS
> ALL_SNAPSHOTS
> ...
>
>
> SQL> describe ALL_SNAPSHOTS
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> OWNER NOT NULL VARCHAR2(30)
> NAME NOT NULL VARCHAR2(30)
> TABLE_NAME NOT NULL VARCHAR2(30)
> ...
>
>
> select name from ALL_SNAPSHOTS;
> ...
>
> TIA.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
> **********************************************************************
> This message contains confidential information intended only for the use of the addressee(s) named above and may
containinformation that is legally privileged. If you are not the addressee, or the person responsible for delivering
itto the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is
strictlyprohibited. If you have received this message by mistake, please immediately notify us by replying to the
messageand delete the original message immediately thereafter.
>
> Thank you.
>
> FADLD Tag
> **********************************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Ray Stell <stellr@cns.vt.edu> writes:
> template1=# \d sql_features
> Did not find any relation named "sql_features".
That's because it's not in your search_path. This would work:
regression=# \d information_schema.sql_features
Table "information_schema.sql_features"
Column | Type | Modifiers
------------------+-----------------------------------+-----------
feature_id | information_schema.character_data |
feature_name | information_schema.character_data |
sub_feature_id | information_schema.character_data |
sub_feature_name | information_schema.character_data |
is_supported | information_schema.character_data |
is_verified_by | information_schema.character_data |
comments | information_schema.character_data |
or this:
regression=# \d *.sql_features
Table "information_schema.sql_features"
Column | Type | Modifiers
------------------+-----------------------------------+-----------
feature_id | information_schema.character_data |
feature_name | information_schema.character_data |
sub_feature_id | information_schema.character_data |
sub_feature_name | information_schema.character_data |
is_supported | information_schema.character_data |
is_verified_by | information_schema.character_data |
comments | information_schema.character_data |
or add information_schema to search_path:
regression=# set search_path = "$user",public,information_schema;
SET
regression=# \d sql_features
Table "information_schema.sql_features"
Column | Type | Modifiers
------------------+----------------+-----------
feature_id | character_data |
feature_name | character_data |
sub_feature_id | character_data |
sub_feature_name | character_data |
is_supported | character_data |
is_verified_by | character_data |
comments | character_data |
> template1=# \dT+sql_features
\dt and \dT are entirely different things.
regards, tom lane
ok, how would I find out the path for any given stuff. How would I have related sql_features to information_schema having forgot that is was information_schema? THX. On Fri, Sep 22, 2006 at 04:00:17PM -0400, Tom Lane wrote: > Ray Stell <stellr@cns.vt.edu> writes: > > template1=# \d sql_features > > Did not find any relation named "sql_features". > > That's because it's not in your search_path. This would work: > > regression=# \d information_schema.sql_features > Table "information_schema.sql_features" > Column | Type | Modifiers > ------------------+-----------------------------------+----------- > feature_id | information_schema.character_data |
On Friday 22 September 2006 16:25, Ray Stell wrote: > ok, how would I find out the path for any given stuff. How would I > have related sql_features to information_schema having forgot that is > was information_schema? While it is pretty tedious, it is probably worth reading through these parts of the manuals: http://www.postgresql.org/docs/8.1/interactive/catalogs.html http://www.postgresql.org/docs/8.1/interactive/information-schema.html There is a lot of useful information in them. Sean
oh, sorry, I see: Table "information_schema.sql_features" On Fri, Sep 22, 2006 at 04:25:25PM -0400, Ray Stell wrote: > > ok, how would I find out the path for any given stuff. How would I > have related sql_features to information_schema having forgot that is > was information_schema? > > THX. > > > > On Fri, Sep 22, 2006 at 04:00:17PM -0400, Tom Lane wrote: > > Ray Stell <stellr@cns.vt.edu> writes: > > > template1=# \d sql_features > > > Did not find any relation named "sql_features". > > > > That's because it's not in your search_path. This would work: > > > > regression=# \d information_schema.sql_features > > Table "information_schema.sql_features" > > Column | Type | Modifiers > > ------------------+-----------------------------------+----------- > > feature_id | information_schema.character_data | > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend --
thanks. I'll read that. I like to know how to find stuff inside the db from inside the db. I find it handy when on the road at a command line. On Fri, Sep 22, 2006 at 04:32:11PM -0400, Sean Davis wrote: > On Friday 22 September 2006 16:25, Ray Stell wrote: > > ok, how would I find out the path for any given stuff. How would I > > have related sql_features to information_schema having forgot that is > > was information_schema? > > While it is pretty tedious, it is probably worth reading through these parts > of the manuals: > > http://www.postgresql.org/docs/8.1/interactive/catalogs.html > http://www.postgresql.org/docs/8.1/interactive/information-schema.html > > There is a lot of useful information in them. > > Sean > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings --