?
Solved

How to dump a single record?

Posted on 2009-04-23
14
Medium Priority
?
462 Views
Last Modified: 2012-05-06
I want to dump a single record from the postgresql DB.
I can only dump whole table currenly...
pg_dump fooddb -d -t recipe > fooddb04.out

Open in new window

0
Comment
Question by:mawingpui
9 Comments
 
LVL 15

Expert Comment

by:Naser Gabaj
ID: 24213666
Here is a PAQ to the similar Question, Try it and let me know.

http://www.experts-exchange.com/Database/Oracle/Q_22025842.html

Good Luck,

Naser
0
 
LVL 16

Expert Comment

by:ai_ja_nai
ID: 24213888
to dump a single record, you have to put the query in a plain text file and pass it like an input to psql

psql foodb <filewithqueryinside

The query result will be printed onscreen and you can redirect it whenever you like (in an output file, for example)
0
 

Author Comment

by:mawingpui
ID: 24221463
I would like to dump a record that contains a SQL insert statement,
so that I can restore it later...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Expert Comment

by:ai_ja_nai
ID: 24224506
I understand. You are looking for the equivalent of --where switch in mysqldump. Sorry, but this feature is not present in pg_dump
0
 
LVL 16

Expert Comment

by:ai_ja_nai
ID: 24466331
I gave an anwer in http:#24224506
0
 
LVL 16

Expert Comment

by:ai_ja_nai
ID: 24467017
can you post your solution, so that we can learn? :) I'm curious now
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24467434
you create a view over the table that returns the 1 row to be dumped, and use the pg_dump command to export that view instead of the table.
the doc of pg_dump does not explicitly tell you can use a view, but it (seems to) work, according to plenty of pages I found on the net.
this matches with what I know for other databases' export tools, including oracle, sql server, mysql ...
the "trick" is that when you don't specify a table, the tool queries the system catalog to list all the tables (it won't dump views). given a dedicated table, it just will run that export, without double-checking if it is actually a table ...
0
 
LVL 16

Expert Comment

by:ai_ja_nai
ID: 24467921
why don't you award yourself the points, as it is the correct answer?
0
 
LVL 16

Expert Comment

by:ai_ja_nai
ID: 24467927
I suggest http:#24467434
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question