Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to backup a postgres table with pgAdmin and have the dump ordered by ID (or any other column). How do I do that?

[EDIT] The dump should contain INSERT queries, not the pure data! [/EDIT]

What I tested so far:

1) I checked pg_dump too and did not find any solution.

2) Another solution would be to export the results of a SELECT statement which is not possible too with pgAdmin.

3) With psql the only solution I found was psql -h localhost -U myuser -W -c 'copy (SELECT * FROM mytable ORDER BY id) to stdout' which only prints the data as csv.

Can anyone help me out? Thanks!

share|improve this question

migrated from stackoverflow.com Sep 5 '13 at 14:48

This question came from our site for professional and enthusiast programmers.

    
What do you mean by 2? I can export data from query results without any problem in pgAdmin. –  Tomas Greif Sep 5 '13 at 12:08
    
Sorry, edited my post. I want the INSERT queries. –  4thfloorstudios Sep 5 '13 at 14:38
1  
What advantage will you have if you have a zillion INSERTs compared to a single one? Or a COPY statement? –  dezso Sep 5 '13 at 15:50
    
@dezso: I simply need INSERT statements because they will be part of another sql script which uses INSERTs too. I see the advantage of a COPY but I simply need the INSERTs. –  4thfloorstudios Sep 5 '13 at 19:52

2 Answers 2

I use pgAdmin 1.14.2 on Ubuntu, but I imagine this will work anywhere else as well.

In pgAdmin, go to Tools -> Backup..., where you'll get a dialog with a few tabs. On the first one (File Options) give a filename and choose the 'Plain' format from the dropdown list. On the next tab (Dump Options #1) tick the 'Use Insert commands' option at the bottom and the 'Only data' option at the top (and don't tick the 'Use Column Inserts' one). On the Objects tab you can choose whichever tables you need then press the Done button.

Now you'll get a file with the INSERT statements for every row - and some other commands as well. You can remove the latter with your favourite editor. Then the list can be sorted - if you want to sort by the first column in the table (that is, more often than not the ID) then you can use a simple sort. If you need a different sorting then you have to apply some magic in an editor.

share|improve this answer
    
Thanks. I will check the solution of Thomas because I want this to be done automatically. I just wondered because in phpMyAdmin / phpPgAdmin you can enter a query, see the results and then export it to any format you like so I expected the same for pgAdmin or pg_dump... –  4thfloorstudios Sep 6 '13 at 13:42
    
Now I am really confused. You commented Hi, Thomas, I think you misunderstood me (and I was not explicit enough): I want a dump as dumped by pg_dump which means with SQL INSERT queries, not the pure data. - but what you are talking about is exactly the same what you didn't want above. –  dezso Sep 6 '13 at 14:20
    
Sorry for confusing you... I do not get the data sorted by your method. I think that's why you wrote If you need a different sorting then you have to apply some magic in an editor. –  4thfloorstudios Sep 6 '13 at 17:32

In pgAdmin

  1. Open query window
  2. Write your query, e.g. select * from blah order by id
  3. Click on "Execute query, write results to file" button
  4. Set required options and click OK.

enter image description here

share|improve this answer
    
Hi, Thomas, I think you misunderstood me (and I was not explicit enough): I want a dump as dumped by pg_dump which means with SQL INSERT queries, not the pure data. –  4thfloorstudios Sep 5 '13 at 14:37
    
OK, then I do not know correct answer. You can try to return entire row as correctly formatted insert string, see for example this question. Anyway, why do you need such strange thing? Maybe you are just asking wrong question. –  Tomas Greif Sep 5 '13 at 18:04
    
Thanks for the link. This looks complicated but interesting :) –  4thfloorstudios Sep 5 '13 at 19:53

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.