Horton
Menu

Thread

  1. a very naive question about table names in Postgres

    Martin Mueller <martinmueller@northwestern.edu> — 2021-12-31T20:27:59Z

    I have a very naïve question and don’t know where tp  look for the answer.  I use Postgres via AquaData Studio as a giant spreadsheet.  I create tables and give them names. I see the table names  and know how to manipulate them with simple SQL queries.

I know that on my Mac the tables are  kept in the  data directory /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I go there I see that internally the tables have numbers.  Somewhere in the system there must be a table that maps the name I gave to table X  to the numerical  inside the database.

Where is that mapping and how can I query it?  On what page of the documentation do I find the answer?

I much prefer Postgres to Mysql for  a variety of reasons, but mostly for its elegant string functions. But in Mysql it seems to be much easier to keep track of tables.

Martin Mueller
Professor emeritus of English and Classics
Northwestern University

  2. Re: a very naive question about table names in Postgres

    Craig McIlwee <craigm@vt.edu> — 2021-12-31T20:36:19Z

    I know that on my Mac the tables are  kept in the  data directory
> /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I go
> there I see that internally the tables have numbers.  Somewhere in the
> system there must be a table that maps the name I gave to table X  to the
> numerical  inside the database.
>

Check the pg_class table in the system catalog.  The numeric value is the
OID.

https://www.postgresql.org/docs/14/catalog-pg-class.html

Craig

>

  3. Re: a very naive question about table names in Postgres

    Gavin Flower <gavinflower@archidevsys.co.nz> — 2021-12-31T20:40:34Z

    On 1/01/22 09:27, Martin Mueller wrote:
>
> I have a very naïve question and don’t know where tp  look for the 
> answer.  I use Postgres via AquaData Studio as a giant spreadsheet.  I 
> create tables and give them names. I see the table names  and know how 
> to manipulate them with simple SQL queries.
>
> I know that on my Mac the tables are  kept in the  data directory 
> /Users/martinmueller/Library/Application Support/Postgres/var-13.  If 
> I go there I see that internally the tables have numbers.  Somewhere 
> in the system there must be a table that maps the name I gave to table 
> X  to the numerical  inside the database.
>
> Where is that mapping and how can I query it?  On what page of the 
> documentation do I find the answer?
>
> I much prefer Postgres to Mysql for  a variety of reasons, but mostly 
> for its elegant string functions. But in Mysql it seems to be much 
> easier to keep track of tables.
>
> Martin Mueller
>
> Professor emeritus of English and Classics
>
> Northwestern University
>
Hi Martin.

Don't know why you want the information you appear to be asking!

However, if you are up to learning how to use psql from the command 
line, then you have an extremely powerful tool to use to query and 
manipulate tables in PostgreSQL!  I set up SQL in text files and get 
psql to execute them (\i my_query.sql) -- you can execute SQL directly 
in psql, but that is really only good for very simply queries.  I find 
that using psql & a text editor a lot easier than using a GUI based tool.


Cheers,
Gavin

  4. Re: a very naive question about table names in Postgres

    Craig McIlwee <craigm@vt.edu> — 2021-12-31T20:41:14Z

    >
> I know that on my Mac the tables are  kept in the  data directory
>> /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I go
>> there I see that internally the tables have numbers.  Somewhere in the
>> system there must be a table that maps the name I gave to table X  to the
>> numerical  inside the database.
>>
>
> Check the pg_class table in the system catalog.  The numeric value is the
> OID.
>
> https://www.postgresql.org/docs/14/catalog-pg-class.html
>
> Craig
>

Whoops, spoke too soon.  I believe it's actually the relfilenode value that
you want.

Craig

>

  5. Re: a very naive question about table names in Postgres

    Karsten Hilbert <karsten.hilbert@gmx.net> — 2021-12-31T21:06:21Z

    Am Fri, Dec 31, 2021 at 08:27:59PM +0000 schrieb Martin Mueller:

> I much prefer Postgres to Mysql for  a variety of reasons,
> but mostly for its elegant string functions. But in Mysql it
> seems to be much easier to keep track of tables.

May I ask for the context of "keep track of tables" ?

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

  6. Re: a very naive question about table names in Postgres

    Josef Šimánek <josef.simanek@gmail.com> — 2021-12-31T21:17:14Z

    pá 31. 12. 2021 v 21:28 odesílatel Martin Mueller
<martinmueller@northwestern.edu> napsal:
>
> I have a very naïve question and don’t know where tp  look for the answer.  I use Postgres via AquaData Studio as a giant spreadsheet.  I create tables and give them names. I see the table names  and know how to manipulate them with simple SQL queries.
>
>
>
> I know that on my Mac the tables are  kept in the  data directory /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I go there I see that internally the tables have numbers.  Somewhere in the system there must be a table that maps the name I gave to table X  to the numerical  inside the database.
>
>
>
> Where is that mapping and how can I query it?  On what page of the documentation do I find the answer?

There is a nice blogpost related.
https://www.2ndquadrant.com/en/blog/postgresql-filename-to-table/

>
>
> I much prefer Postgres to Mysql for  a variety of reasons, but mostly for its elegant string functions. But in Mysql it seems to be much easier to keep track of tables.
>
>
>
> Martin Mueller
>
> Professor emeritus of English and Classics
>
> Northwestern University
>
>
>
>

  7. Re: a very naive question about table names in Postgres

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2021-12-31T21:25:20Z

    On 12/31/21 12:27 PM, Martin Mueller wrote:
> I know that on my Mac the tables are  kept in the  data directory 
> /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I 
> go there I see that internally the tables have numbers.  Somewhere in 
> the system there must be a table that maps the name I gave to table X  
> to the numerical  inside the database.
> 
> Where is that mapping and how can I query it?  On what page of the 
> documentation do I find the answer?

Dear Prof. Mueller,

This question strikes Postgres people a bit like your teenager asking 
where you keep the tallest ladder. There aren't many practical uses for 
accessing these files directly, and if you do it's likely to break your 
database. Could you speak a bit more about your goal here? Perhaps 
someone can suggest a good way to achieve it.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

  8. Re: a very naive question about table names in Postgres

    Dave Cramer <davecramer@postgres.rocks> — 2022-01-01T11:48:04Z

    On Fri, 31 Dec 2021 at 16:25, Paul Jungwirth <pj@illuminatedcomputing.com>
wrote:

> On 12/31/21 12:27 PM, Martin Mueller wrote:
> > I know that on my Mac the tables are  kept in the  data directory
> > /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I
> > go there I see that internally the tables have numbers.  Somewhere in
> > the system there must be a table that maps the name I gave to table X
> > to the numerical  inside the database.
> >
> > Where is that mapping and how can I query it?  On what page of the
> > documentation do I find the answer?
>
> Dear Prof. Mueller,
>
>

Everything you ever wanted to know is here

PostgreSQL: Documentation: 14: Chapter 52. System Catalogs
<https://www.postgresql.org/docs/14/catalogs.html>

Enjoy the journey

Dave