0

I am working on a PostgreSQL database. I would like to get the results of a SELECT query as a list of INSERT statements and have them saved into a .sql file (let's say exported_data.sql).

This is because later I would like to run the .sql file on another database and have the data "imported" in the table, so that the table gets "updated".

psql my_database -h my_host < exported_data.sql

Let's say my query is:

SELECT *
FROM params_table 
WHERE params_type = 'tkt';

From this thread I see that one way to do it is to:

  1. Create a temporary table filled with the results of the query

     CREATE TABLE temporary_table 
 AS 
     SELECT *
     FROM params_table
     WHERE params_type  = 'tkt';

  2. Export the table as a list of INSERT statements into a .sql file

     pg_dump --table=temporary_table --data-only --column-inserts my_database -h my_host > exported_data.sql

  3. Remove the table, to keep the database clean.

     DROP TABLE temporary_table;

Is there a way to do it in one shot?

I am asking just because I would not like to create and then remove a temporary table every time I need to export some data.

I saw from this thread that for a MySQL database it is possible to do it one-shot:

mysqldump -u root -p my_database -h my_host params_table --where="params_type  = 'tkt'" > exported_data.sql

So I have read the Postgresql documentation for pg_dump, but I cannot find any equivalent for --where clause.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Tms91
  • 3,456
  • 6
  • 40
  • 74
  • 1
    A TEMP table is automatically removed at end of session. And there is `copy` / `\cpy`, too – wildplasser Feb 10 '22 at 16:16
  • 1
    @wildplasser, except Tms91 is not actually using a `temporary` table just calling it that. An actual `temporary` table would not work in any case as they are session specific and the `pg_dump` session would not see it. `COPY` and/or `\copy` do not output `INSERT`s. – Adrian Klaver Feb 10 '22 at 16:44
  • Is it always the same table, and how many columns are you interested in? – codeulike Feb 10 '22 at 17:18
  • @codeulike I would like to know the most general way to do it, so it would be great if someone could point me a way to generate a list-of-insert-.sql-file containing `INSERT`s into different tables and updating more than one column. But any help is appreciated. – Tms91 Feb 11 '22 at 09:24

0 Answers0