current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 69 down vote favorite
29

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
up vote 115 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
2  
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
    
This would be much better with create view export_view..., since the view would remain up-to-date with changes to the base table. The docs say --table=table: Dump only tables (or **views**... so I had some hope this would work, but dumping a view sadly yields no data. :P – poshest Nov 20 at 8:31
    
@poshest It works for me in 9.5. What exactly did you try? – Clodoaldo Neto Nov 20 at 10:16
    
@ClodoaldoNeto oh, OK great! I hope I can also get it working. I used pg_dump --table=my_schema.my_view --data-only --inserts my_db > data.sql, version 9.5.3, and my create statement was the same as yours except create view.... All I get in the output is the usual pg_dump comments and SET statements. Not sure where I'm going wrong. – poshest Nov 20 at 15:19
up vote 86 down vote

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';

Import the same to another table of the same structure anywhere with:

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

COPY writes and read 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 that:

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

share|improve this answer
2  
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
2  
STDIN and STDOUT can be used in place of file path, useful for small data exports. – Amir Ali Akbari Jan 6 '15 at 3:38
    
Without the --column-inserts flag, pg_dump uses a COPY from STDIN for each of the tables in the SQL code it generates. – Randall Oct 26 at 13:17
up vote 6 down vote

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
11  
The question specifically says specific records. This only exports an entire table. – cdmckay Jul 2 '14 at 3:10
up vote 5 down vote

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
1  
It works great. When you choose 'postgres' as the 'driver', It's probable that you'll have to download the JDBC drivers yourself: jdbc.postgresql.org/download.html (it's a .jar file - java binary) and add it as the 'driver' of the postgresql conneciton. The connection string (or URL as in the interface) should look like that: jdbc:postgresql://127.0.0.1:5432/db_name – mrmuggles Jul 13 at 6:30
up vote 3 down vote

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.

Click right on target table and choose "Backup"

Choose a destination path and change the format to "Plain"

Open the tab "Dump Options #2" at the bottom and check "Use Column Inserts"

You can copy the INSERT Statements from there.

share|improve this answer
up vote 1 down vote

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

CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'
share|improve this answer
2  
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
    
It is not possible using Postgres 9.1 – Hector Feb 2 '15 at 18:08
up vote 1 down vote

For my use-case I was able to simply pipe to grep.

pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql
share|improve this answer
1  
One must consider about having 'tokyo' in other field. – Buyut Joko Rivai Jan 9 at 17:22
up vote -1 down vote

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 '14 at 3:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.