How can I UNION two tables in different PostgeSQL databases?

Question

I'm running PostgreSQL 11.8 in a Docker container. I have two databases: website_db and testdb.

website_db has a products table with id, product_name, colour, product_size columns

testdb has a table called users with id, username, password

I'm using website_db and I want to UNION columns from the users table in the testdb database. I can get this to work in MySQL but am struggling with Postgres. Here's my attempt:

SELECT * FROM products WHERE product_name = 'doesntexist' OR 1=1 UNION SELECT null,username,password,null FROM testdb.users;

I get this error back:

ERROR:  relation "testdb.users" does not exist
LINE 1: ...1=1 UNION SELECT null,username,password,null FROM testdb.use...

Does anyone know what I have to do to fix my query?

Relevant? https://stackoverflow.com/questions/46324/possible-to-perform-cross-database-queries-with-postgresql — shawnt00, Jul 19 '20 at 03:56
@TimBiegeleisen Yes the db/table exists. Here's screenshots: https://imgur.com/a/hK9nbHt — RandomDisplayName45463, Jul 19 '20 at 04:09
Echoing shawnt00's answer. postgres_fdw is the way to go. You can't simply query another database without setting that up. — sudo, Jul 19 '20 at 07:11
"*I can get this to work in MySQL*" - what MySQL calls a "database" is in reality a schema. So you should simply use separate schemas in Postgres, not separate databases - then you don't have those problems any more. — a_horse_with_no_name, Jul 20 '20 at 15:15

score 0 · Answer 1 · answered Jul 20 '20 at 15:13

You can do it using dblink:

create database first;
create database second;
\c first;

create table products
(
    id serial not null
        constraint products_pk
            primary key,
    product_name varchar(50) not null
);


INSERT INTO public.products (id, product_name) VALUES (1, 'first_db');

\c second;

create table products
(
    id serial not null
        constraint products_pk
            primary key,
    product_name varchar(50) not null
);


INSERT INTO public.products (id, product_name) VALUES (1, 'sec_db');

-- dblink -- executes a query in a remote database 
create extension dblink;

-- change queries and creds
SELECT id, product_name FROM products
UNION ALL
SELECT * FROM dblink('dbname=first user=root password=root', 'SELECT id, product_name FROM products') AS tb2(id int, product_name text);

How can I UNION two tables in different PostgeSQL databases?

1 Answers1