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). — user330315
– user330315, Commented Aug 24, 2013 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. — user330315
– user330315, Commented Aug 24, 2013 at 16:44

roman · Accepted Answer · 2013-12-06 17:27:16Z

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 6, 2013 at 17:27

answered Aug 24, 2013 at 17:50

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yuri Zolotarev Over a year ago

I unchecked the paginate results and all works fine.

bma · Accepted Answer · 2013-08-24 17:27:22Z

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, 2013 at 17:27

answered Aug 24, 2013 at 16:42

bma

9,8562 gold badges35 silver badges22 bronze badges

2 Comments

Erwin Brandstetter Over a year ago

Data-modifying CTEs a.k.a. writable CTEs are available since Postgres 9.1.

bma Over a year ago

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.

Rijul · Accepted Answer · 2013-08-24 16:46:03Z

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, 2013 at 16:46

Rijul

5157 silver badges19 bronze badges

1 Comment

user330315 Over a year ago

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.

Jost · Accepted Answer · 2013-08-24 16:48:53Z

0

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.

answered Aug 24, 2013 at 16:48

Jost

1,53212 silver badges18 bronze badges

Collectives™ on Stack Overflow

INSERT INTO PostgreSQL

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related