Get all tables under a database in Postgre

[Victor Nijegorodov]

Could it help you: [sql - List all tables in postgresql information_schema - Stack Overflow](https://stackoverflow.com/questions/2276644/list-all-tables-in-postgresql-information-schema) ?

[_Flaviu]

Thank you Victor. Obviosly, I don't understand something: if I do:

create database mydb3

and then I want to create a table under that db, how can I do that ? Because any (dummy) table I create, seem to be any no database inside ... P.S. I saw (and tried) that link ... thanks anyway.

[Victor Nijegorodov]

I never used Postgre... In MS SQL Server you usually use something like:

create database mydb3
go
use mydb3
CREATE TABLE

In postgresql you should use something similar... Example: [postgresql - How to switch databases in psql? - Stack Overflow](https://stackoverflow.com/questions/3949876/how-to-switch-databases-in-psql)

[_Flaviu]

Hello Victor. As far as I understood, "use ..." is not valid command in Postgre SQL ... I have tried that.

[Victor Nijegorodov]

_Flaviu wrote:

As far as I understood, "use ..." is not valid command in Postgre SQL

I know. But did you try the

\connect

[Lost User]

Did you try the link I gave you at Re: List all tables from a specific database - Database Discussion Boards[^]?

[_Flaviu]

That command is a Postgre SQL cmd line command, I cannot run it as SQL script, I have tried that. Thank you.

[_Flaviu]

Yes, I have tried:

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
schemaname != 'information_schema';

but this script doesn't take into account a specific database. I don't understand how is relation between database and table, in Postgre SQL ...

[Lost User]

That is not what the tutorial suggests. The summary states:

Quote:

Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.

[CHill60]

If you are not using psql then how are you running your SQL?

[_Flaviu]

Is about a C++ ODBC class which can run SQL scripts only.

[Victor Nijegorodov]

Could you try to set the desired database in the connection string ([PostgreSQL ODBC Driver (psqlODBC) Connection Strings - ConnectionStrings.com](https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/))?

[_Flaviu]

I realize that all available tables are below existing connection, which is the default database. And the default database must be choosen at the connection time. But yes, all databases could be listed in any connection. Thank you for all your support.

[Member_15436648]

If you have problems with connection to database, you can try different tools. For example .net data provider for postgresql.

[Tomas PawSQL]

SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';

[Tomas PawSQL]

Get create table statements for a database under a schema.

SELECT TABLE_NAME, 'CREATE TABLE '||TABLE_NAME|| ' ('||STRING_AGG(CONCAT( C1, C2, C3, C4, C5, C6 ),', ')||')' AS QUERY FROM ( SELECT C.TABLE_NAME, '"'||C.COLUMN_NAME||'"' || ' ' || CASE WHEN DATA_TYPE='ARRAY' THEN LTRIM(UDT_NAME,'_')||'[]' ELSE DATA_TYPE END AS C1, CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' || CHARACTER_MAXIMUM_LENGTH || ')' END AS C2, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE < 1 THEN NULL END AS C3, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE > 0 THEN NULL END AS C4, CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' END AS C5, CASE WHEN COLUMN_DEFAULT IS NOT NULL AND COLUMN_DEFAULT NOT LIKE 'nextval%' THEN ' DEFAULT' END || ' ' || REPLACE(COLUMN_DEFAULT, '::CHARACTER VARYING', '') AS C6 FROM INFORMATION_SCHEMA.COLUMNS C, INFORMATION_SCHEMA.TABLES T WHERE C.TABLE_CATALOG='tpch' AND T.TABLE_CATALOG='tpch' AND T.TABLE_SCHEMA='public' AND C.TABLE_NAME=T.TABLE_NAME AND C.TABLE_SCHEMA='public' AND T.TABLE_TYPE IN ('BASE TABLE') ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION ) AS STRING_COLUMNS GROUP BY TABLE_NAME