How to query from one database to another

Question

Using postgres I have wrote a query to list the tables inside a database

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');

Query returns results from the wrong database. It automatically selects the first database from the list of databases in postgres.

How do I specify the database to query? 'j220190_data' being the database to query

I've tried things like:

    SELECT table_name
FROM information_schema.tables
WHERE Databases = 'j220190_data'
AND table_schema NOT IN
('pg_catalog', 'information_schema');

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND
WHERE Databases = 'j220190_data'
AND table_schema NOT IN
('pg_catalog', 'information_schema');

"*How do I specify the database to query*" - you can't you need to first _connect_ to the other database. Postgres does not support cross-database queries — a_horse_with_no_name, Nov 28 '16 at 10:44
"*It automatically selects the first database from the list of databases in postgres*" - that's wrong. That query returns the information of the database you are currently connected to. — a_horse_with_no_name, Nov 28 '16 at 10:45
I tried connecting to the database first using \connect 'j220190_data' but this didnt work either — John, Nov 28 '16 at 10:46
see http://stackoverflow.com/questions/46324/possible-to-perform-cross-database-queries-with-postgres — Balan, Nov 28 '16 at 10:49
How you connect to a different database depends on the SQL client you are using. — a_horse_with_no_name, Nov 28 '16 at 10:57

score 0 · Answer 1 · answered Nov 28 '16 at 10:55

0

When you connect to postgres select database;
for example:

psql mydb

and after you post the select, because when you try whit this select, postgres checks on default database.

answered Nov 28 '16 at 10:55

el fuser

556
1
5
10

1

@John: [psql](https://www.postgresql.org/docs/current/static/reference-client.html) is a **command line** program, not a SQL statement. – a_horse_with_no_name Nov 28 '16 at 10:59
when u connect at postgres db, you can connect at db that you want, you try with psql j220190_data, if your database is this. You connect from shell obviously – el fuser Nov 28 '16 at 11:00

score 0 · Answer 2 · answered Nov 28 '16 at 12:58

If I understood correctly you can query another database using dblink().

Create extension CREATE EXTENSION dblink;

SELECT tbl.*
FROM dblink('dbname=DB1 port=5432   
            host=localhost user=usr password=123', 'SELECT table_name
FROM information_schema.tables
WHERE table_type = ''BASE TABLE'' 
AND table_schema NOT IN(''pg_catalog'', ''information_schema'');') 
AS tbl(table_name varchar(30));

How to query from one database to another

2 Answers2