Copy table from another db in postgres

 This one is somehow a followup on "Duplicate row in plain sql". What I needed in this case was to copy 

Both dbs live in the same physical postgres, and the main user has rights over everything, so the process was painless, and took 5 minutes to set it up (accounting for reading the docs).

The trick is to use the postgres_fdw and create a schema "dev" inside db_test, so I can access the tables in db_dev.

	create extension postgres_fdw ;
	create server dev foreign data wrapper postgres_fdw options (host 'localhost', dbname 'db_dev');
	select user;
	create user mapping for my_user server dev options (user 'my_user' );
	create schema dev;
	import foreign schema public limit to ( table_to_copy ) from server dev into dev;
	select * from dev.table_to_copy;

view raw postgres_fdw.sql hosted with ❤ by GitHub

Now you can easily do your  `insert into table_to_copy select * from dev.table_to_copy;`