INSERT across Databases in PostgreSQL

Question

I'm trying to do an INSERT from one database to another, eg:

//called in database 'model'
INSERT INTO login.Hospital (name, phonenumber) --EDIT, now Hospital
VALUES ('Foo Bar', '555-555-5555');

I get this error:

"null value in column "id" violates not-null constraint"

Where column id is a primary key on table People that auto-increments.

Is there a reason PG won't auto-increment id when doing an insert cross-database? Is there a 'recommended' way around this?

Thank you.

--EDIT-- For clarification: I have 2 databases, model and login each with a schema of dbo. Login has a schema called login which is for a Foreign Data Wrapper. The goal is from my database: model, call a Login database table using the schema login for tables we've imported (eg INSERT INTO login.hospitals... etc)

For all intents and purposes this Hospital table is created like this:

CREATE TABLE People(ID SERIAL NOT NULL, name TEXT, phonenumber TEXT);

I hope this clarifies any issues.

in postgres "login" willbe schema, not database. It is different from Mysql... try running \dt+ login.people in psql it will shouw you the structure of table — Vao Tsun
– Vao Tsun, Commented Jun 21, 2016 at 15:42
You're correct it would be schema, but I know the structure of the table, I need to INSERT cross-schema. — Anduril28
– Anduril28, Commented Jun 21, 2016 at 15:49
please update answer with result of my command - it will show why you have an error — Vao Tsun
– Vao Tsun, Commented Jun 21, 2016 at 15:51
This is the result of your command: i.imgur.com/2EMvgIp.jpg Yes, you will note the table name is different than the example I gave above. — Anduril28
– Anduril28, Commented Jun 21, 2016 at 16:03
Since the company I work for does not have any mysql databases, nor have I ever used mysql myself, I do not think I am running it on mysql instead of postgres. — Anduril28
– Anduril28, Commented Jun 21, 2016 at 16:32

Anduril28 · Accepted Answer · 2016-06-22 13:01:13Z

3

While searching I discovered these solutions:

https://www.postgresql.org/message-id/26654.1380145647%40sss.pgh.pa.us http://www.slideshare.net/jkatz05/developing-and-deploying-apps-with-the-postgres-fdw

These essentially involve creating a foreign table and maintaining two tables, something I wanted to avoid.

So, my solution was to create a trigger on the source DB (eg Login) for each table that is called from the calling DB (eg Model). This trigger would be fired before an INSERT and call a trigger function. The trigger function looks like this:

CREATE OR REPLACE FUNCTION dbo.tf_insert_cross_schema()
RETURNS TRIGGER AS
$$
DECLARE
    sequenceName TEXT = 'dbo.' || TG_TABLE_NAME || '_id_seq';

BEGIN
    IF (NEW.id IS NULL) THEN
        NEW.id = (SELECT nextval(sequenceName));
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE PLPGSQL VOLATILE;

Thanks!

edited Jun 22, 2016 at 13:01

answered Jun 22, 2016 at 12:52

Anduril28

3131 gold badge4 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT across Databases in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related