Backup with pgAdmin ordered by ID

Question

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!

Answer 1

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.

Answer 2

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.