From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Martin Mueller <martinmueller(at)northwestern(dot)edu>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: metadata about creation and size of tables |
Date: | 2018-10-02 21:41:16 |
Message-ID: | b2c1bc92-b29d-0e82-00e4-da29ec22d83f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/2/18 1:38 PM, Martin Mueller wrote:
> I’ve looked at the documentation to find where to find some data that
> are very to find Mysql: the creation and modification data of a table
> and the size of particular tables.
>
> Where do I find an answer to the question “which is the last table I
> created” or “when did I last modify this table?” In the data directory,
Postgres does not track these times. You can search the list archives
for past discussions on the pros and cons.
> tables seem to have numbers, but there doesn’t seem to be an easy
> mapping of those numbers to the table names.
From:
https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT
select pg_relation_filepath('id_test'), pg_relation_filenode('id_test');
pg_relation_filepath | pg_relation_filenode
----------------------+----------------------
base/733941/2976140 | 2976140
>
> I thumbed through the documentation, but didn’t see any heading that was
> likely to have that information. Is there some where a “table of
> tables” that lets you look up various metadata?
https://www.postgresql.org/docs/10/static/catalogs.html
For tables in particular:
https://www.postgresql.org/docs/10/static/catalog-pg-class.html
There is also:
https://www.postgresql.org/docs/10/static/information-schema.html
And in psql there are the \ commands:
https://www.postgresql.org/docs/10/static/app-psql.html
In psql \? will get you a list of these with descriptions
As an example:
test_(aklaver)> \d id_test
Table "public.id_test"
Column | Type | Collation | Nullable |
Default
--------+-------------------+-----------+----------+-------------------------------------
id | integer | | not null |
nextval('id_test_id_seq'::regclass)
fld_1 | character varying | | |
fld_2 | boolean | | |
test_(aklaver)> \d+ id_test
Table "public.id_test"
Column | Type | Collation | Nullable |
Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
id | integer | | not null |
nextval('id_test_id_seq'::regclass) | plain | |
fld_1 | character varying | | |
| extended | |
fld_2 | boolean | | |
| plain | |
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-10-02 23:27:46 | Re: FTS trigger works 1 at a time, but fails with bulk insert script |
Previous Message | Adrian Klaver | 2018-10-02 21:27:52 | Re: How can I get and handle the status of sql statements that run in plpgsql ? |