Prepared Statement on Postgresql in Rails

by

If you want to use prepare like that then you’ll need to make a couple changes:

  1. The PostgreSQL driver wants to see numbered placeholders ($1, $2, …) not question marks and you need to give your prepared statement a name:

     ActiveRecord::Base.connection.raw_connection.prepare('some_name', "DELETE FROM my_table WHERE id = $1")

  2. The calling sequence is prepare followed by exec_prepared:

    connection = ActiveRecord::Base.connection.raw_connection
connection.prepare('some_name', "DELETE FROM my_table WHERE id = $1")
st = connection.exec_prepared('some_name', [ id ])

The above approach works for me with ActiveRecord and PostgreSQL, your PG::Connection.open version should work if you’re connecting properly.

Another way is to do the quoting yourself:

conn = ActiveRecord::Base.connection
conn.execute(%Q{
    delete from my_table
    where id = #{conn.quote(id)}
})

That’s the sort of thing that ActiveRecord is usually doing behind your back.

Directly interacting with the database tends to be a bit of a mess with Rails since the Rails people don’t think you should ever do it.

If you really are just trying to delete a row without interference, you could use delete:

delete()

[…]

The row is simply removed with an SQL DELETE statement on the record’s primary key, and no callbacks are executed.

So you can just say this:

MyTable.delete(id)

and you’ll send a simple delete from my_tables where id = ... into the database.

More Related Contents:

  1. How to use ANY instead of IN in a WHERE clause?
  2. PostgreSQL GROUP BY different from MySQL?
  3. Best way to count rows by arbitrary time intervals
  4. Perform this hours of operation query in PostgreSQL
  5. PostgreSQL where all in array
  6. SQL where joined set must contain all values but may contain more
  7. Find records where join doesn’t exist
  8. Rails 4 LIKE query – ActiveRecord adds quotes
  9. Find all records which have a count of an association greater than zero
  10. Sort by column ASC, but NULL values first?
  11. Using an Alias column in the where clause in Postgresql
  12. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  13. Optimize query with OFFSET on large table
  14. How to concatenate columns in a Postgres SELECT?
  15. updating table rows in postgres using subquery
  16. Find overlapping date ranges in PostgreSQL
  17. PostgreSQL multi INSERT…RETURNING with multiple columns
  18. How do you do date math that ignores the year?
  19. “Operator does not exist: integer =?” when using Postgres
  20. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  21. Oracle equivalent of Postgres’ DISTINCT ON?
  22. postgresql generate sequence with no gap
  23. Installing Postgres on windows for use with Ruby-on-Rails
  24. PostgreSQL: Case insensitive string comparison
  25. Grant privileges for a particular database in PostgreSQL
  26. Functions with variable number of input parameters
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects
  29. How to detect query which holds the lock in Postgres?
  30. PostgreSQL: top n entries per item in same table

Leave a Comment