INSERT INTO PostgreSQL

问题:

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

回答1:

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;

回答2:

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

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:

回答3:

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.

回答4:

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.