Export specific rows from a PostgreSQL table as INSERT SQL script

Create a table with the set you want to export and then use the command line utility pg_dump to export to a file:

create table export_table as select id, name, cityfrom nyummy.cimorywhere city = 'tokyo'

$ pg_dump --table=export_table --data-only --column-inserts my_database > data.sql

--column-inserts will dump as insert commands with column names.

--data-only do not dump schema.

As commented below, creating a view in instead of a table will obviate the table creation whenever a new export is necessary.

To export data only use COPY:

COPY (SELECT * FROM nyummy.cimory WHERE city = 'tokio') TO '/path/to/file.csv';

You can export a whole table, only selected columns, or the result of a query as demonstrated. No need to create a table explicitly.

You get a file with one table row per line as plain text (not INSERT commands). Smaller and faster than INSERT commands.

To import the same to another Postgres table of matching structure anywhere (columns in same order, data types compatible!):

COPY other_tbl FROM '/path/to/file.csv';

COPY writes and reads files local to the server, unlike client programs like pg_dump or psql which read and write files local to the client. If both run on the same machine, it doesn't matter much, but it does for remote connections.

There is also the \copy command of psql:

Performs a frontend (client) copy. This is an operation that runs anSQL COPY command, but instead of the server reading orwriting the specified file, psql reads or writes the file and routesthe data between the server and the local file system. This means thatfile accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

Same syntax as above. Just replace COPY with \copy.

This is an easy and fast way to export a table to a script with pgAdmin manually without extra installations:

1. Right click on target table and select "Backup".
2. Select a file path to store the backup. As Format choose "Plain".
3. Open the tab "Dump Options #2" at the bottom and check "Use Column Inserts".
4. Click the Backup-button.
5. If you open the resulting file with a text reader (e.g. notepad++) you get a script to create the whole table. From there you can simply copy the generated INSERT-Statements.

This method also works with the technique of making an export_table as demonstrated in @Clodoaldo Neto's answer.