Get all tables under a database in Postgre
-
I need to get all database names, using SQL script only. I have tried:
SELECT datname FROM pg_database WHERE datistemplate = false
and seem to work. I got:
mydb1
mydb2
postgresmybd1 and mydb2 have been created by me. So far, so good. Now, I need to find all tables under every database, using SQL script only. Is it possible ?
-
I need to get all database names, using SQL script only. I have tried:
SELECT datname FROM pg_database WHERE datistemplate = false
and seem to work. I got:
mydb1
mydb2
postgresmybd1 and mydb2 have been created by me. So far, so good. Now, I need to find all tables under every database, using SQL script only. Is it possible ?
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) ?
-
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) ?
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.
-
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.
I never used Postgre... In MS SQL Server you usually use something like:
create database mydb3
go
use mydb3
CREATE TABLEIn 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)
-
I never used Postgre... In MS SQL Server you usually use something like:
create database mydb3
go
use mydb3
CREATE TABLEIn 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)
-
Hello Victor. As far as I understood,
"use ..."is not valid command in Postgre SQL ... I have tried that._Flaviu wrote:
As far as I understood, "use ..." is not valid command in Postgre SQL
I know. But did you try the
\connect
-
I need to get all database names, using SQL script only. I have tried:
SELECT datname FROM pg_database WHERE datistemplate = false
and seem to work. I got:
mydb1
mydb2
postgresmybd1 and mydb2 have been created by me. So far, so good. Now, I need to find all tables under every database, using SQL script only. Is it possible ?
-
_Flaviu wrote:
As far as I understood, "use ..." is not valid command in Postgre SQL
I know. But did you try the
\connect
-
Did you try the link I gave you at Re: List all tables from a specific database - Database Discussion Boards[^]?
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 ...
-
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 ...
-
That command is a Postgre SQL cmd line command, I cannot run it as SQL script, I have tried that. Thank you.
-
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/))?
-
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.
-
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.
If you have problems with connection to database, you can try different tools. For example .net data provider for postgresql.
-
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) ?
SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';
-
SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';
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