Cannot SELECT from UPDATE RETURNING clause in postgres

by

Before PostgreSQL 9.1 INSERT/UPDATE/DELETE could only be used as top level statements. This is why you are getting a syntax error.

Starting from 9.1 you can use data-modifying statements with common table expressions. Your example query would look like this:

WITH updated AS (UPDATE test SET description = 'test' RETURNING id)
SELECT * FROM test WHERE id IN (SELECT id FROM updated);

Be careful with selecting from the just modified table. You can get confusing results that way. Becuse the queries are executed in the same snapshot, the SELECT will not see the effects of the UPDATE statement.

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Computed / calculated / virtual / derived columns in PostgreSQL
  5. Check if value exists in Postgres array
  6. Is there a shortcut for SELECT * FROM?
  7. sql statement error: “column .. does not exist”
  8. What is the difference between single quotes and double quotes in PostgreSQL?
  9. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  10. Best way to count rows by arbitrary time intervals
  11. PostgreSQL sequence based on another column
  12. Sorting null values after all others, except special
  13. Finding similar strings with PostgreSQL quickly
  14. Reference alias in WHERE clause
  15. Sort NULL values to the end of a table
  16. How to compare the current row with next and previous row in PostgreSQL?
  17. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  18. Window Functions or Common Table Expressions: count previous rows within range
  19. Why postgres returns unordered data in select query, after updation of row?
  20. Continuing a transaction after primary key violation error
  21. Select (retrieve) all records from multiple schemas using Postgres
  22. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  23. Use email address as primary key?
  24. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  25. SQL query to find record with ID not in another table
  26. Get execution time of PostgreSQL query
  27. Postgresql tables exists, but getting “relation does not exist” when querying
  28. Parallel unnest() and sort order in PostgreSQL
  29. PostgreSQL: Full Text Search – How to search partial words?
  30. Subtract hours from the now() function

Leave a Comment