Export Postgresql table data using pgAdmin

Question

I am using pgAdmin version 1.14.3. PostgreSQL database version is 9.1.

I got all Db script for table creation but unable to export all data inside tables. Could not find any option to export data in db script form.

If anyone want for whole DB then look [Export and Import of PostgresSQL with pgAdmin III](http://stackoverflow.com/a/39167526/1045444) — Somnath Muluk, Aug 26 '16 at 13:30
You can try in online demo http://choose.tools/tool?id=pgAdmin — Michael Podgortsev, Jan 22 '20 at 19:27

score 224 · Answer 1 · edited Jun 03 '22 at 10:04

224

Right-click on your table and pick option Backup..
On File Options, set Filepath/Filename and pick PLAIN for Format
Ignore Dump Options #1 tab
In Dump Options #2 tab, check USE INSERT COMMANDS
In Dump Options #2 tab, check Use Column Inserts if you want column names in your inserts.
Hit Backup button

Edit: In case you are using pgAdmin on a remote server: Once the UI announces the backup was successful, you might want to download the data. For this, follow these steps:

Click on Tools (in the menu)
Select Storage Manager
Select the file that was just created
Click on the small "Download File" button

edited Jun 03 '22 at 10:04

Sebu

4,671
5
16
29

answered Mar 25 '15 at 02:53

Yusuf

2,459
1
12
5

13

where this backup went? – StealthTrails Dec 24 '18 at 08:11
3

Maybe this work if your DB is in local. In my case, there is a message announcing the victory, but the file disappears on the server. I want to DOWNLOAD it on my computer. :/ – fresko Feb 19 '19 at 13:38
9

pgAdmin is most ridiculous IT tools I've ever used. There's anything like UX in pgAdmin. – cadavre Jan 21 '20 at 09:33
3

I couldn't find the file that was generated, so I ended up using the command that pgAdmin used (click on "More Details" in pgAdmin after running Backup), and opening a shell on the server and running the same command, then I could copy the file. – ccallendar Jul 15 '20 at 20:07
for File Name give a file with a full path (eg: /home/xxx/yyy/backup.sql"), Then if it gives an error saying cannot find that message has the default path it tries to save. – Janaka Peiris Jun 24 '21 at 16:29
@ChaudhryWaqas backup file is created under Documents folder in Windows OS. – TeachMeJava Jan 13 '22 at 09:53
@fresko see my edit on how to download the backup from a server – Sebu Jun 03 '22 at 10:05
try to create an empty text file and change the extension of it to .sql. Select the file – cristian9804 Aug 23 '23 at 10:55

score 166 · Accepted Answer · edited Nov 25 '16 at 15:06

166

Just right click on a table and select "backup". The popup will show various options, including "Format", select "plain" and you get plain SQL.

pgAdmin is just using pg_dump to create the dump, also when you want plain SQL.

It uses something like this:

pg_dump --user user --password --format=plain --table=tablename --inserts --attribute-inserts etc.

edited Nov 25 '16 at 15:06

Alfonso Tienda

3,442
1
19
34

answered Jun 29 '12 at 07:33

Frank Heikens

117,544
24
142
135

11

it generates dump having table data script starts with COPY command not INSERT. Anyway to generate script with INSERT commands. – Muhammad Imran Tariq Jun 29 '12 at 07:42
8

That's why you have to create plain SQL. When using the dump option INSERT, you get ordinary INSERT queries – Frank Heikens Jun 29 '12 at 07:56
Can you tell where to use this dump option INSERT – Muhammad Imran Tariq Jun 29 '12 at 08:43
"Dump Option #1" => "Use INSERT ..." – Frank Heikens Jun 29 '12 at 09:10
On the command line, pg_dump is a command line tool. Or use pg_dump and click on the correct tabs to select the options you need. – Frank Heikens Jun 29 '12 at 09:39
This creates an INSERT command for each row, as opposed to one INSERT with all the row values (which has better performance). I couldn't find an option to change it but a simple find and replace worked for me. – Pixel Elephant Nov 13 '12 at 18:06
1

@Pixel Elephant: If performance matters, you should use COPY. It's much faster. – Frank Heikens Nov 14 '12 at 07:01
24

In pgAdmin III 1.16.1, "Use Insert commands" is under Dump Option #2. – jpmc26 Aug 18 '13 at 01:31
2

In pgAdmin III 1.18.1, Select Plain for Format under File Option and than check "Use Insert commands" under Dump Option #2. – bhdrkn Dec 29 '14 at 15:11
Maybe this work if your DB is in local. In my case, there is a message announcing the victory, but the file disappears on the server. I want to DOWNLOAD it on my computer. :/ – fresko Feb 19 '19 at 13:39

score 24 · Answer 3 · answered Feb 02 '18 at 00:42

24

In the pgAdmin4, Right click on table select backup like this

After that into the backup dialog there is Dump options tab into that there is section queries you can select Use Insert Commands which include all insert queries as well in the backup.

answered Feb 02 '18 at 00:42

Keyur Lakhani

4,321
1
24
35

Export Postgresql table data using pgAdmin

3 Answers3

Linked