ERROR: function dblink(unknown, unknown) does not exist
You need to install an extension dblink create extension dblink;
dblink
Postgres Error: More than one row returned by a subquery used as an expression
Technically, to remove the error, add LIMIT 1 to the subquery to return at most 1 row. The statement would still be nonsense. … ‘SELECT store_key FROM store LIMIT 1’ … Practically, you want to match rows somehow instead of picking an arbitrary row from the remote table store to update every row of your … Read more
PostgreSQL: Query has no destination for result data
The stored procedure won’t just return the result of the last SELECT. You need to actually return the value: CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$ BEGIN — …. RETURN(SELECT dblink_disconnect()); END $$ LANGUAGE plpgsql; You’re getting the error because Postgres expects the function to return something of type text, but your function … Read more
How to use (install) dblink in PostgreSQL?
Since PostgreSQL 9.1, installation of additional modules is simple. Registered extensions like dblink can be installed with CREATE EXTENSION: CREATE EXTENSION dblink; Installs into your default schema, which is public by default. Make sure your search_path is set properly before you run the command. The schema must be visible to all roles who have to … Read more
How do I do large non-blocking updates in PostgreSQL?
Column / Row … I don’t need the transactional integrity to be maintained across the entire operation, because I know that the column I’m changing is not going to be written to or read during the update. Any UPDATE in PostgreSQL’s MVCC model writes a new version of the whole row. If concurrent transactions change … Read more
postgresql: INSERT INTO … (SELECT * …)
As Henrik wrote you can use dblink to connect remote database and fetch result. For example: psql dbtest CREATE TABLE tblB (id serial, time integer); INSERT INTO tblB (time) VALUES (5000), (2000); psql postgres CREATE TABLE tblA (id serial, time integer); INSERT INTO tblA SELECT id, time FROM dblink(‘dbname=dbtest’, ‘SELECT id, time FROM tblB’) AS … Read more