3 ms·
> Then it is a lot of engineering work to update existing SQL queries. SQLite (similar to MySQL) dialect uses the question mark parameters while PostgreSQL uses
by sebazzz 5y ago
> Then it is a lot of engineering work to update existing SQL queries. SQLite (similar to MySQL) dialect uses the question mark parameters while PostgreSQL uses positioned parameters for prepared statements.
Many database engines, including SQL Server for instance, also support question marks. Surely PostgreSQL also supports this?
- packetlost 5y agoNo, it doesn't. Using a '?' will cause a syntax error.
- masklinn 5y agoInterestingly, there is one "platform" which supports ? as placeholder: ECPG (where SQL is directly embedded in C code, and post-processed to generate straight C you can feed to a compiler). In ecpg, prepared statements use ?: https://www.postgresql.org/docs/current/ecpg-commands.html#ECPG-PREPARED https://www.postgresql.org/docs/current/ecpg-commands.html#E...
- mjw1007 5y ago? in PostgreSQL is available for use as a user-defined operator. I suppose they made that decision a long time ago.
- nikeee 5y agoSQLite also supports referencing by parameter number (and name): https://www.sqlite.org/lang_expr.html#varparam https://www.sqlite.org/lang_expr.html#varparam So, changing "?" to "?1" could be done in SQLite before the actual migration, leaving just the change from ? to $ when migrating to Postgres.
- uhoh-itsmaciek 5y agoAs some other replies noted, Postgres does not support that. Some drivers or ORMs do their own pre-processing on query text, and add their own support for that. E.g., in ActiveRecord, you can do: User.where("status = ?", "active").all ActiveRecord itself sanitizes the input here and the query that's sent to Postgres does not use parameter markers at all.