Обсуждение: List all tables from a specific database

On 14/01/2022 10:39, Flaviu2 wrote:
> I work on a project that use Postgre SQL, and I have 0 experience in 
> this regard. I hope to get help here. I need two SQL scripts for Postgre:
> 
> 1. Get all databases, which I aquired already:
> 
> *SELECT datname FROM pg_database WHERE datistemplate = false*
> 
> This one is functional, it's ok.
> 
> 2. Now, I need to find all tables *under a specific* database. This one 
> I don't know how to achieve it. Can you help me here ? It is possible ?

If it's any help, running psql with the -E switch will show you the SQL 
which psql generates... so then (within psql) issuing the \dt command to 
list tables will show you the SQL used.

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Hi,

On Fri, Jan 14, 2022 at 12:19:28PM +0000, Flaviu2 wrote:
>  Thanks a lot.
> Maybe I am not far from a solving solution. So, if I create a database, lets say (SQL script):
> CREATE database mydb3;
> How can I create a table under mydb3 ? Because, if I run:
> SELECT relname FROM pg_class WHERE relkind = 'r';
> 
> Got me all tables, but I don't know the database under were created.

On postgres all relations, functions and so on are specific to a specific
database, and only accessible when connected on that specific database.

So if you want to create a table in mydb3, or see the list of tables in that
database, you need to connect to mydb3.

If needed, you can get the current database with the current_database()
function, e.g.:

=# SELECT current_database();
 current_database
------------------
 postgres
(1 row)

On 14/01/2022 12:42, Flaviu2 wrote:
> Yes, I started psql with -E, and now I see the SQL underhood. Thank you.
> 
> Now I need to find how to get and how to select a specific database, I 
> have no experience in Postgre SQL.

Well, connecting from the command line with psql, you just specify it:

   psql [...options...] my_database

Or if you're already in psql, use the \c command:

    \c my_database

The Postgres manual is excellent, if enormous:

     https://www.postgresql.org/docs/current/index.html

A good place to start is the "Tutorial" section.

HTH,

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 14/01/2022 12:51, Flaviu2 wrote:
> Yes, I ran
> 
> *\c mydb1*
> 
> but I didn't see any SQL script underhood :(

Ah, OK. As I understand it, connecting to a database isn't something you 
can do in SQL: it's a function of the client, and how you do it depends 
on the client. - So in psql, it's a command-line argument; connecting 
from PHP with PDO, you use a connection string; and so on.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie