Take the 2-minute tour ×

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.

share|improve this question
I almost succeed with ToraSQL tool, it's just that it has error in datetime data in the result sql script. Anyone know other free postgreSQL admin tool that able to do this? –  suud Oct 10 '12 at 10:45
Update: at the time of this writing, the only free GUI tool I found out that can perform this operation is SQL Workbench/J (with postgreSQL jdbc4 driver) –  suud Oct 11 '12 at 3:48

6 Answers 6

up vote 30 down vote accepted

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 = 'tokio'

$ 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.

share|improve this answer
Alright, so far your solution works. One thing missed is I need to add "-U user_name". I also almost succeed with ToraSQL tool, it's just that it has error in date-time data in the script result. If none can give GUI tool solution in 2 days, your answer will be accepted –  suud Oct 10 '12 at 9:54
Just want to share to other people, you can also use this free GUI tool: SQL Workbench/J (with postgreSQL jdbc4 driver), to do the same thing. –  suud Oct 11 '12 at 9:27

For a data-only export use COPY.
You get a file with one table row per line as plain text (not INSERT commands), it's smaller and faster:

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

To import the same to another table of the same structure anywhere with:

COPY other_tbl FROM '/path/to/file.csv';
share|improve this answer
The OP calls specifically for data as insert sql script file. I guess he is talking about insert commands, don't you? –  Clodoaldo Neto Oct 10 '12 at 17:51
@Clodoaldo: You may be right, in which case your answer would be a better fit. One could also copy the CREATE script in pgAdmin separately (as the OP mentions GUIs). –  Erwin Brandstetter Oct 10 '12 at 19:27
This was a way better fit for me... Thanks Erwin! –  Kevin Apr 22 at 20:09

You can make view of the table with specifit records and then dump sql file

SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'
share|improve this answer
Show code or make this a comment. –  Clodoaldo Neto Oct 10 '12 at 8:57
I tried it in pgAdmin III, but for View object, there is no option for dumping. –  suud Oct 10 '12 at 9:02
Try navicat. I'm using it and it has export sql script option –  Giorgi Peikrishvili Oct 10 '12 at 9:06
@Giorgi: is there freeware version? –  suud Oct 10 '12 at 9:08
Sorry it's not free –  Giorgi Peikrishvili Oct 10 '12 at 9:10

SQL Workbench has such a feature.

After running a query, right click on the query results and choose "Copy Data As SQL > SQL Insert"

share|improve this answer

have u tried in pgadmin executing query with " EXECUTE QUERY WRITE RESULT TO FILE " option

its only export the data, else try like

pg_dump -t view_name DB_name > db.sql

-t option used for ==> Dump only tables (or views or sequences) matching table, refer

share|improve this answer
This will only export a create view statement –  cdmckay Jul 2 at 3:10

Here's the complete syntax.

pg_dump -U user_name -h localhost -t table_name --data-only --column-inserts db_name > data_dump.sql
share|improve this answer
The question specifically says specific records. This only exports an entire table. –  cdmckay Jul 2 at 3:10

Your Answer


