postgresql: INSERT INTO … (SELECT * …)

by

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 t(id integer, time integer)
    WHERE time > 1000;

TABLE tblA;
 id | time 
----+------
  1 | 5000
  2 | 2000
(2 rows)

PostgreSQL has record pseudo-type (only for function’s argument or result type), which allows you query data from another (unknown) table.

Edit:

You can make it as prepared statement if you want and it works as well:

PREPARE migrate_data (integer) AS
INSERT INTO tblA
    SELECT id, time
    FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
    AS t(id integer, time integer)
    WHERE time > $1;

EXECUTE migrate_data(1000);
-- DEALLOCATE migrate_data;

Edit (yeah, another):

I just saw your revised question (closed as duplicate, or just very similar to this).

If my understanding is correct (postgres has tbla and dbtest has tblb and you want remote insert with local select, not remote select with local insert as above):

psql dbtest

SELECT dblink_exec
(
    'dbname=postgres',
    'INSERT INTO tbla
        SELECT id, time
        FROM dblink
        (
            ''dbname=dbtest'',
            ''SELECT id, time FROM tblb''
        )
        AS t(id integer, time integer)
        WHERE time > 1000;'
);

I don’t like that nested dblink, but AFAIK I can’t reference to tblB in dblink_exec body. Use LIMIT to specify top 20 rows, but I think you need to sort them using ORDER BY clause first.

More Related Contents:

  1. How to use (install) dblink in PostgreSQL?
  2. PostgreSQL multi INSERT…RETURNING with multiple columns
  3. INSERT rows into multiple tables in a single query, selecting from an involved table
  4. Export specific rows from a PostgreSQL table as INSERT SQL script
  5. Bulk/batch update/upsert in PostgreSQL
  6. Postgres Error: More than one row returned by a subquery used as an expression
  7. How to bulk insert only new rows in PostreSQL
  8. SQL Populate table with random data
  9. PostgreSQL Crosstab Query
  10. PostgreSQL unnest() with element number
  11. Inserting multiple rows in a single SQL query? [duplicate]
  12. How do I query using fields inside the new PostgreSQL JSON datatype?
  13. How do you use script variables in psql?
  14. Best way to check for “empty or null value”
  15. How to avoid multiple function evals with the (func()).* syntax in a query?
  16. Time zone storage in data type “timestamp with time zone”
  17. Is there something like a zip() function in PostgreSQL that combines two arrays?
  18. PostgreSQL parameterized Order By / Limit in table function
  19. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  20. Get the default values of table columns in Postgres?
  21. Division of integers returns 0
  22. PostgreSQL: how to convert from Unix epoch to date?
  23. postgresql – sql – count of `true` values
  24. Optimize groupwise maximum query
  25. Use email address as primary key?
  26. Creating temporary tables in SQL
  27. PostgreSQL where all in array
  28. SQL query to find record with ID not in another table
  29. Get execution time of PostgreSQL query
  30. Redundant data in update statements

Leave a Comment