How to insert a row in postgreSQL pgAdmin?

Question

I am new to postgreSQL. Is there any way to insert row in postgreSQL pgAdmin without using SQL Editor (SQL query)?

I really don't recommend using PgAdmin-III as a data editor. Use something like OpenOffice Base, which can be connected to PostgreSQL. — Craig Ringer, Mar 31 '14 at 07:37

score 57 · Answer 1 · edited Nov 10 '19 at 09:21

57

The accepted answer is related to PgAdmin 3 which is outdated and not supported. For PgAdmin 4 and above, the application is running in the browser.

After you create your table, you have to make sure that your table has a primary key otherwise you couldn't edit the data as mentioned in the official documentation.

To modify the content of a table, each row in the table must be uniquely identifiable. If the table definition does not include an OID or a primary key, the displayed data is read only. Note that views cannot be edited; updatable views (using rules) are not supported.

1- Add primary key

Expand your table properties by clicking on it in the pgAdmin4 legend. Right-click on 'Constraints', select 'Create' --> 'Primary Key'to define a primary key column.

2- View the data in excel like format

Browser view, right-click on your table --> select View/Edit Data --> All Rows

3- Add new row / Edit data

On the Data Output tab at the bottom of the table below the last row, there will be an empty row where you can enter new data in an excel-like manner. If you want to make updates you can also double click on any cell and change its value.

4- Save the changes

Click on the 'Save' button on the menu bar near the top of the data window.

edited Nov 10 '19 at 09:21

Shady Mohamed Sherif

15,003
4
45
54

answered Aug 05 '17 at 21:06

cm1

709
1
6
9

8

No such row for an empty table. – Tvaroh Nov 06 '17 at 07:51
10

you need to insert primary key first, otherwise it does not work – Raymond Seger Jul 30 '18 at 03:23
1

The step-4 says, need to click on the Save button. But the highlighted icon doesn't look like Save button. Nevertheless, this solution worked for me. I used the Save button, to the left of the highlighted button here. – Manoj Seelan Aug 26 '20 at 02:28
1

@ManojSeelan There are two _Save_ buttons, the _(ancient) 3½ floppy disc _ one (⌘/Ctrl+S) is to **Save** the script you have in the query editor into a **File** and the _Grid-with-downward-arrow_ (F6) is to **Save Data Changes**. Once you hoover over those icons it will become clear – msciwoj Feb 19 '21 at 06:47
1

Just duplicating my comment from the older answer with German text. When there is no row or field for you to click after doing right-clicking the table and selecting `View/Edit Data`, you are not allowed to add records to the table. It might be because of a constraint. For me, all tables were empty and this table's fields were all foreign keys with constraints. I had to add rows to the referenced tables before adding data. – Joachim Rives Sep 30 '21 at 04:10

score 42 · Answer 2 · answered Aug 07 '15 at 08:20

42

I think some answers don't provide an answer to the original question, some of them insert records but with SQL statements and the OP clearly said WITHOUT, so I post the right answer: (Step by Step)

answered Aug 07 '15 at 08:20

Gödel77

839
3
15
27

OK can be done in PGAdmin4 as per @niaomingjian but I needed PK set – Jay Killeen Mar 29 '17 at 11:56
9

Even though it was in German, it was still better than the accepted answer – Janac Meena Dec 18 '18 at 15:39
1

It doesn't look like the step to hit the Enter key was necessary. In fact, hitting the Enter key did nothing in my pgAdmin. I just double clicked on the blank row at the bottom of the table, and populated a row and hit the save button. – Big Pumpkin Mar 12 '19 at 22:22
2

I should be the selected answer, even if we don't read German, we can follow the cascade in the right-click menu. – mins May 08 '19 at 13:20
1

@Pere It's only "not very useful" if you don't understand German!! I also don't, but at least this guy took the time and effort to provide a good answer. It will be "very useful" to you if you use Google Translate, or put forth a fraction of the effort he did :) – Reversed Engineer Jan 21 '20 at 16:23
In pgAdmin, when there is no row or field for you to click after doing right-clicking the table and selecting `View/Edit Data`, you are not allowed to add records to the table. It might be because of a constraint. For me, all tables were empty and this table's fields were all foreign keys with constraints. I had to add rows to the referenced tables before adding data. – Joachim Rives Sep 30 '21 at 04:09
Can you update in English? I think you're selecting "All Rows" in "View/Edit Data"? pgAdmin 4 menu does not seem to line up. – NightMICU Jun 04 '22 at 20:16

score 23 · Answer 3 · answered Nov 10 '17 at 19:47

23

Alternatively you can use the query tool:

INSERT INTO public.table01( name, age) VALUES (?, ?);

use the lightning icon to execute.

answered Nov 10 '17 at 19:47

Rutger Hofste

4,073
3
33
44

score 18 · Accepted Answer · answered Mar 31 '14 at 07:01

18

You can do that without the SQL editor, but it's better to do this by queries.

Although, in pgAdmin, there is an option which you can click to have an excel-like window where you can add and update data in a table without using SQL language. Please select a table which you want to add a row first and click on the next icon here below.

enter image description here

answered Mar 31 '14 at 07:01

KarelG

5,176
4
33
49

14

But you can't insert. Only update there. At least my pgadmin doesn't have a button or anything to insert new line there. – jPO Feb 17 '15 at 15:10
2

GUI view data not avialable in mac pgadmin v4 – Ratan Uday Kumar Aug 22 '18 at 06:38

score 17 · Answer 5 · answered Sep 07 '16 at 03:55

17

Editing table data without primary key is forbidden
If your tables don't have a primary key or OIDs, you can view the data only.
Inserting new rows and changing existing rows isn't possible for the Edit Data tool without primary key.

answered Sep 07 '16 at 03:55

niaomingjian

3,472
8
43
78

4

I have to really emphasize how important this answer is. If you haven't designated a column in the table as a primary key, you will not be given any kind of table edit option as documented in other answers. – Tim Holt Nov 06 '17 at 22:21
1

Very useful answer! Do you know why is that the case? – gdrt Apr 26 '18 at 09:17

score 5 · Answer 6 · answered Mar 31 '14 at 06:55

5

Use INSERT:

INSERT INTO tablename (field1, field2) values ('value1', 2);

answered Mar 31 '14 at 06:55

8

I'm pretty sure he knows how INSERT work, he's asking specifically using pgAdmin – luisfer Mar 14 '19 at 00:17

score 3 · Answer 7 · answered Jul 13 '21 at 17:25

3

on pgAdmin 4, right-click on the table and use the item like below. You can also use that script in the background.

Finally, to watch the inserted data do like below. You can also use that script in the background.

answered Jul 13 '21 at 17:25

OmiD_D

71
2

score 2 · Answer 8 · answered Sep 03 '20 at 05:51

2

All the above are correct answers. I just want to add that : When u create a table, make sure u have atleast one column as PRIMARY_KEY. Then, just follow the GUI : View/Edit data. U can add row as the last row of the table

answered Sep 03 '20 at 05:51

xenowits

329
2
7

score 1 · Answer 9 · answered Sep 30 '21 at 04:29

As an update, the icon for the save button is different in pgAdmin 4.

This is how the menu should look after right-clicking on the table you want to insert into and hovering over "View/Edit Data".

After adding rows, either press F6 (on Ubuntu) or click the icon that looks like a stack of discs (database icon) with a lock on it.

Zoomed in:

Wide View:

How to insert a row in postgreSQL pgAdmin?

9 Answers9

Linked