[SOLVED] Export specific rows from a PostgreSQL table as INSERT SQL script

Export specific rows from a PostgreSQL table as INSERT SQL script

I have a database schema named: nyummy and a table named cimory:

create table nyummy.cimory (
  id numeric(10,0) not null,
  name character varying(60) not null,
  city character varying(50) not null,
  CONSTRAINT cimory_pkey PRIMARY KEY (id)
);

I want to export the cimory table's data as insert SQL script file. However, I only want to export records/data where the city is equal to 'tokyo' (assume city data are all lowercase).

How to do it?

It doesn't matter whether the solution is in freeware GUI tools or command line (although GUI tools solution is better). I had tried pgAdmin III, but I can't find an option to do this.

Solution

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, city
from nyummy.cimory
where 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.