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
– Jim Jones, Commented Feb 19, 2018 at 17:15
Not exactly a duplicate, but this answers your question, except for the trigger part, which you can find in the manual. stackoverflow.com/questions/40589822/… — Alex
– Alex, Commented Feb 19, 2018 at 17:20
postgresql.org/docs/9.5/static/postgres-fdw.html or postgresql.org/docs/9.3/static/contrib-dblink-connect.html are options. Although I do have a preference for postgres_fdw — Jim Jones
– Jim Jones, Commented Feb 19, 2018 at 17:26

Jim Jones · Accepted Answer · 2018-02-19 18:04:18Z

0

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;

answered Feb 19, 2018 at 18:04

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

Logging records in a postgresql database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related