Thread

1. metadata about creation and size of tables

   Martin Mueller <martinmueller@northwestern.edu> — 2018-10-02T20:38:36Z

   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?
   
   
   

2. Re: metadata about creation and size of tables

   Adrian Klaver <adrian.klaver@aklaver.com> — 2018-10-02T21:41:16Z

   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