Postgresql ошибка ссылки между базами не реализованы - Не ошибается лишь тот, кто ничего не делает!

I am trying to convert SQL inner join query into PostgreSQL inner join query. In this inner join query which tables are using that all tables are not present in one database. we separated tables into two databases i.e. application db and security db

users and permission table are present in security db
userrolemapping and department are present in application db

I tried like below but I am getting following error

Error

ERROR:  cross-database references are not implemented: "Rockefeller_ApplicationDb.public.userrolemapping"
LINE 4:         INNER JOIN "Rockefeller_ApplicationDb".public.userro..

SQL Stored Function

SELECT   Department.nDeptID 
    FROM Users INNER JOIN Permission 
         ON Users.nUserID = Permission.nUserID INNER JOIN UserRoleMapping
         ON Users.nUserID = UserRoleMapping.nUserID INNER JOIN Department
         ON Permission.nDeptInst = Department.nInstID
         AND  Department.nInstID = 60
    WHERE     
         Users.nUserID = 3;

PostgreSQL Stored Function

SELECT dep.ndept_id 
        FROM "Rockefeller_SecurityDb".public.users as  u 
        INNER JOIN  "Rockefeller_SecurityDb".public.permissions p ON u.nuser_id = p.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.userrolemapping as urm ON u.nuser_id = urm.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.department dep ON p.ndept_inst = dep.ninst_id
           AND  dep.ninst_id = 60
                        WHERE     
                            u.nuser_id = 3;

asked Aug 10, 2018 at 10:52

You cannot join tables from different databases.

Databases are logically separated in PostgreSQL by design.

If you want to join the tables, you should put them into different schemas in one database rather than into different databases.

Note that what is called “database” in MySQL is called a “schema” in standard SQL.

If you really need to join tables from different databases, you need to use a foreign data wrapper.

answered Aug 10, 2018 at 12:43

Laurenz AlbeLaurenz Albe

202k17 gold badges190 silver badges247 bronze badges

For future searchs, you can to use dblink to connect to other database.

Follow commands:

create extension dblink;

SELECT dblink_connect('otherdb','host=localhost port=5432 dbname=otherdb user=postgres password=???? options=-csearch_path=');

SELECT * FROM dblink('otherdb', 'select field1, field2 from public.tablex')
AS t(field1 text, field2 text);

answered Jul 22, 2020 at 11:58

New to postrgreSQL and I had the same requirement. FOREIGN DATA WRAPPER did the job.

IMPORT FOREIGN SCHEMA — import table definitions from a foreign server

But first I had to:

enable the fdw extension
define the foreign server (which was the locahost in this case!)
create a mapping between the local user and the foreign user.

CREATE EXTENSION postgres_fdw;

CREATE SERVER localsrv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'otherdb', port '5432');

CREATE USER MAPPING FOR <local_user>
SERVER localsrv
OPTIONS (user 'ohterdb_user', password 'ohterdb_user_password');

IMPORT FOREIGN SCHEMA public
FROM SERVER localsrv 
INTO public;

After that I could use the foreign tables as if they were local. I did not notice any performance cost.

Jeremy Caney

7,00461 gold badges48 silver badges76 bronze badges

answered Dec 6, 2021 at 20:40

In my case, I changed my query from:

SELECT * FROM myDB.public.person

to this:

SELECT * FROM "myDB".public.cats

and it worked.

You can read more at mathworks.com.

answered Feb 20, 2022 at 9:57

BehnamBehnam

9912 gold badges14 silver badges38 bronze badges

Источник

Есть таблица artist и user_artist

artist:
- id (PK)
- name
user_artist:
- id (PK)
- userId(FK)
- artistId(FK)
- added

Следующий запрос возвращает ошибку

ссылки между базами не реализованы: user_artist.added

return db.query(`
                SELECT artist.name  AS "artistName", artist.id AS "artistId", user_artist.added
                FROM user_artist
                INNER JOIN artist
                ON artist.id = user_artist."artistId"
                WHERE user_artist."userId" = $(userId) 
                AND user_artist.added::timestamp < to_timestamp($(index) / 1000)
                ORDER BY user_artist.added $(order)
                LIMIT $(limit);`, obj);

Если убрать ORDER BY user_artist.added $(order) то ошибка исчезает.
Вот точная структура таблица user_artist

create table user_artist
(
  "id" serial primary key,
  "userId" serial REFERENCES user (id) on delete cascade,
  "artistId" serial REFERENCES artist (id) on delete cascade,
  "added" timestamp,
  unique ("userId", "artistId")
);

Что не так?

Источник

Здравствуйте, такая проблема, у меня есть задание :В базе данных basa1 имеется таблица Книга. Из базы данных basa2 необходимо выполнить действие по увеличению цены всех книг на 10%. В какой базе данных должна быть создана процедура и как можно ее выполнить?
Под это дело я написала нечто такое:

SQL

CREATE PROCEDURE my_proc4() AS $$
BEGIN
UPDATE basa1.dbl.Книга SET basa1.dbl.Книга.Цена =basa1.dbl.Книга.Цена*1.1 ;
END
$$ LANGUAGE 'plpgsql'

процедура создается, но когда пытаюсь ее вызвать пишет «ссылки между базами не реализованы: «basa1.dbl.Книга»»
как можно это исправить?
p.s. данный вариант составления процедуры не первый, и если раньше он эту процедуру даже заметить ее, то сейчас хоть понятно в чем проблема
p.s. p.s. Надеюсь код я занесла в правильные скобки

Источник

Вы получаете эту ошибку, потому что вы не подключены к базе данных real_estate.

Я предполагаю, что вы подключены к postgres, потому что это то, что вы обычно делаете для запуска CREATE DATABASE.

Вам нужно будет разорвать существующее соединение с базой данных, а затем запустить его для вновь созданной базы данных. Только тогда вам разрешено создавать объекты в базе данных.

Это особенность, что разные базы данных строго разделены, и любой запрос на изменение, который будет отклонен.

Как это лучше всего сделать, зависит от того, как вы запускаете сценарий. Если вы запустите его с psql, подойдет простой c real_estate.

Источник

Я пытаюсь преобразовать запрос внутреннего соединения SQL в запрос внутреннего соединения PostgreSQL. В этом запросе внутреннего соединения, какие таблицы используют, не все таблицы присутствуют в одной базе данных. мы разделили таблицы на две базы данных, то есть базу данных приложений и базу данных безопасности.

пользователи и таблица разрешений присутствуют в базе данных безопасности
сопоставление ролей пользователя и отдел присутствуют в базе данных приложения

Я пробовал, как показано ниже, но получаю следующую ошибку

Ошибка

ERROR:  cross-database references are not implemented: "Rockefeller_ApplicationDb.public.userrolemapping"
LINE 4:         INNER JOIN "Rockefeller_ApplicationDb".public.userro..

Сохраненная функция SQL

SELECT   Department.nDeptID 
    FROM Users INNER JOIN Permission 
         ON Users.nUserID = Permission.nUserID INNER JOIN UserRoleMapping
         ON Users.nUserID = UserRoleMapping.nUserID INNER JOIN Department
         ON Permission.nDeptInst = Department.nInstID
         AND  Department.nInstID = 60
    WHERE     
         Users.nUserID = 3;

Сохраненная функция PostgreSQL

SELECT dep.ndept_id 
        FROM "Rockefeller_SecurityDb".public.users as  u 
        INNER JOIN  "Rockefeller_SecurityDb".public.permissions p ON u.nuser_id = p.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.userrolemapping as urm ON u.nuser_id = urm.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.department dep ON p.ndept_inst = dep.ninst_id
           AND  dep.ninst_id = 60
                        WHERE     
                            u.nuser_id = 3;

4 ответа

Вы не можете соединять таблицы из разных баз данных.

Базы данных логически разделены в PostgreSQL.

Если вы хотите соединить таблицы, вы должны поместить их в разные схемы в одной базе данных, а не в разные базы данных.

Обратите внимание, что то, что называется «базой данных» в MySQL, называется «схемой» в стандартном SQL.

Если вам действительно нужно объединить таблицы из разных баз данных, вам нужно использовать стороннюю оболочку данных.

Laurenz Albe
8 Янв 2019 в 10:09

Для будущих поисков вы можете использовать dblink для подключения к другой базе данных.

Следуйте командам:

create extension dblink;

SELECT dblink_connect('otherdb','host=localhost port=5432 dbname=otherdb user=postgres password=???? options=-csearch_path=');

SELECT * FROM dblink('otherdb', 'select field1, field2 from public.tablex')
AS t(field1 text, field2 text);

rafaelnaskar
22 Июл 2020 в 14:58

У меня была такая же проблема с Postgres и JpaRepo, и я просто удалил dbname.public из запроса.

Krizsán Balazs
19 Авг 2021 в 19:43

Новичок в postrgreSQL, и у меня было такое же требование. FOREIGN DATA WRAPPER сделал свое дело.

IMPORT FOREIGN SCHEMA — импорт определений таблиц с внешнего сервера.

Но сначала мне нужно было:

включить расширение fdw
определить внешний сервер (который в данном случае был лока-хостом!)
создать сопоставление между локальным пользователем и внешним пользователем.

CREATE EXTENSION postgres_fdw;

CREATE SERVER localsrv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'otherdb', port '5432');

CREATE USER MAPPING FOR <local_user>
SERVER localsrv
OPTIONS (user 'ohterdb_user', password 'ohterdb_user_password');

IMPORT FOREIGN SCHEMA public
FROM SERVER localsrv 
INTO public;

После этого я мог использовать чужие таблицы, как если бы они были локальными. Затрат на производительность я не заметил.

Jeremy Caney
7 Дек 2021 в 03:33

Источник