Logging records in a postgresql database

Question

I am having trouble thinking of a way to copy three fields out of a database into and append them to another table along with the current date. Basically what I want to do is: DB-A: ID (N9), Name (C69), Phone (N15) {and a list of other fields I dont care about} DB-B: Date (Todays date/time), Nane, Address, Phone (as above)

Would be great is this was a trigger in the DB on add or update of DB-A.

Greg

Hey Greg, can you post the create table statements, so that we can better visualize the problem? — Jim Jones, Feb 19 '18 at 17:15
Not exactly a duplicate, but this answers your question, except for the trigger part, which you can find in the manual. https://stackoverflow.com/questions/40589822/how-would-i-formulate-an-insert-query-into-another-database-using-postgres-fdw — Alex, Feb 19 '18 at 17:20
https://www.postgresql.org/docs/9.5/static/postgres-fdw.html or https://www.postgresql.org/docs/9.3/static/contrib-dblink-connect.html are options. Although I do have a preference for `postgres_fdw` — Jim Jones, Feb 19 '18 at 17:26

score 0 · Answer 1 · answered Feb 19 '18 at 18:04

Quick and dirty using postgres_fdw

CREATE EXTENSION IF NOT EXISTS postgres_fdw ;
CREATE SERVER extern_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foreignserver.co.uk', port '5432', dbname 'mydb');
CREATE USER MAPPING FOR myuser SERVER extern_server OPTIONS (user 'anotheruser');

-- Creating a foreign table based on table t1 at the server described above
CREATE FOREIGN TABLE foreign_t1 (
  dba INT,
  name VARCHAR(9),
  phone VARCHAR(15) 
)
SERVER extern_server OPTIONS (schema_name 'public', table_name 't1');

--Inserting data to a new table + date
INSERT INTO t2 SELECT dba,name,phone,CURRENT_DATE FROM foreign_t1;
-- Or just retrieving what you need placing the current date as a column
SELECT dba,name,phone,CURRENT_DATE FROM foreign_t1;

Logging records in a postgresql database

1 Answers1