INSERT INTO PostgreSQL

Question

I have got a little problem with SQL. I'm trying to insert 2 values into my table.

that's my query: INSERT INTO tableinfo (table,date) VALUES ('Sell','24 August'); But it doesnt work. I've got something like that:

SQL error:
ERROR:  syntax near "INTO"
LINE 1: SELECT COUNT(*) AS total FROM (INSERT INTO tableinfo (table,...
                                              ^
In statement::
SELECT COUNT(*) AS total FROM (INSERT INTO tableinfo (table,date) VALUES ('Sell','24 August')) AS sub

It's pretty basic so I don't know why it doesnt work :( PostgreSQL 9.2.4

You cannot combine an insert and a SELECT like that. What are you trying to achieve? (And you might want to switch psql to english error messages so that everybody here can read them). — , Aug 24 '13 at 16:39
Btw: the value for `count(*)` will always be exactly 1 (one) because you are trying to insert a single row - so I don't understand what the `count()` is inteded for. — , Aug 24 '13 at 16:44

Roman Pekar · Answer 1 · 2013-12-06T17:27:16.607

5

I've installed phpPgAdmin to try to reproduce your error. I got it right away when tried to create a test table:

enter image description here

So looks like phpPgAdmin wraping your query into select count(*) as total from (...). I've found that it happens only when checkbox "Paginate results" on query page is set to on (obviously phpPgAdmin trying to count how many rows it will get and then show it page by page). Uncheck it and your query will work fine:

enter image description here

edited Dec 06 '13 at 17:27

answered Aug 24 '13 at 17:50

Roman Pekar

107,110
28
195
197

I unchecked the paginate results and all works fine. – Yuri Zolotarev Sep 18 '18 at 15:11

bma · Accepted Answer · 2013-08-24T17:27:22.080

4

It's not the INSERT that is the problem, it is the invalid SQL that you are trying to issue. Try your insert first then a separate count(*) query, or if you are using PostgreSQL 9.1+ you can use Common Table Expressions and RETURNING

WITH ins AS (
     insert into tableinfo ("table","date") 
     values ('Sell','24 August') RETURNING "table"
)
select count(*) 
from ins;

edited Aug 24 '13 at 17:27

answered Aug 24 '13 at 16:42

bma

9,424
2
33
22

1

[Data-modifying CTEs a.k.a. writable CTEs](http://www.postgresql.org/docs/9.1/static/queries-with.html#QUERIES-WITH-MODIFYING) are available since Postgres 9.1. – Erwin Brandstetter Aug 24 '13 at 17:06
Oh right, I always forget that -- I skipped from 9.0 to 9.2 in production. Thanks, I'll update my answer to reflect that. – bma Aug 24 '13 at 17:26

score 0 · Answer 3 · answered Aug 24 '13 at 16:46

0

The error message is not understandable. But as far as it is visible, You cannot select from an operation performed (INSERT). SELECT statement only displays after selecting from a relation. An alternative for your case would be to either execute the 2 queries separately,or use a transaction, if you are allowed a single execution.

answered Aug 24 '13 at 16:46

Rijul

515
7
19

You *can* select from the result of an `INSERT` statement. See bma's answer. But it's totally unclear (at least to me) what the OP is actually trying to do. – Aug 24 '13 at 16:47

score 0 · Answer 4 · answered Aug 24 '13 at 16:48

As far as I know your the input to your select is not the underlying table where the data was inserted via the insert statement, but the return value of the insert statement (e.g. RETURNING) which is missing here.

Have a look at the (excellent) Postgres documentation especially the with_query_name section where inserts can be used.

INSERT INTO PostgreSQL

4 Answers4

Linked