Thread: List all tables from a specific database
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 ?
For instance, using this SQL script:
SELECT 1, datname FROM pg_database WHERE datistemplate = false
I got:
mydb1
mydb2
postgres
So far, so good. Of course, mydb1 and mydb2 have been created by me, using:
create database mydb1
create database mydb2
Now, which SQL script to use, to retrieve all tables under a database only, but, most important, using SQL script only.
For instance, something like (pseudo-script):
SELECT table_name FROM information_schema.tables WHERE database = 'mydb1'
SELECT table_name FROM information_schema.tables WHERE database = 'mydb2'
Kindly thank you.
Flaviu.
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
Le ven. 14 janv. 2022 à 12:04, Ray O'Donnell <ray@rodonnell.ie> a écrit :
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.
Hello,
You can use this SQL command to view all the standard table (known as relations):
And this one for partitioned tables (if you use them):
SELECT relname FROM pg_class WHERE relkind = 'p';
HTH,
Thomas
--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie
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.
Flaviu.
On Friday, January 14, 2022, 02:08:22 PM GMT+2, Thomas Boussekey <thomas.boussekey@gmail.com> wrote:
Le ven. 14 janv. 2022 à 12:04, Ray O'Donnell <ray@rodonnell.ie> a écrit :
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.
Hello,
You can use this SQL command to view all the standard table (known as relations):
And this one for partitioned tables (if you use them):
SELECT relname FROM pg_class WHERE relkind = 'p';
HTH,
Thomas
--
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)
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.
On Friday, January 14, 2022, 01:04:47 PM GMT+2, Ray O'Donnell <ray@rodonnell.ie> wrote:
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
> 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
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
Yes, I ran
\c mydb1
but I didn't see any SQL script underhood :(
On Friday, January 14, 2022, 02:45:52 PM GMT+2, Ray O'Donnell <ray@rodonnell.ie> wrote:
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,
> 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,
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