Thread: metadata about creation and size of tables
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, tables seem to have numbers, but there doesn’t seem to be an easy mapping of those numbers to the table names.
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?
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.klaver@aklaver.com