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
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.
I am pretty close. And what SQL command should I use to select the database, and after that to call SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' ?
I have tried:
but seem to not work:
ERROR: syntax error at or near "USE";
Error while executing the query
You know, I need to all this programatically, using SQL commands, not using Postgre tools.
Also, other options from that menu are not working either: PHPMyAdmin, etc. What I should do to use MySQL database ? In fact, I only need to setup a ODBC source for MySQL database from WampServer, that's all.
The name has been changed to "sqlite_schema" as of version 3.33.0. The older name "sqlite_master" is still accepted as an alias for backwards compatibility. The FAQ is for version 3.33.0. You must be using an older version of SQLite.
Assuming you're not using 3.33.0 or later, try using the sqlite_master table instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer