0

select * from elves where firstname like '%tegil%' or lastname like '%astar%'

my query above is working properly, if i change single quote with double quote at (tegil) query not working.

select * from elves where firstname like "%tegil%" or lastname like '%astar%'

There was an error with the SQL query:

PG::UndefinedColumn: ERROR: column "%tegil%" does not exist LINE 1: ...)))) as shortlist from elves where firstname like "%tegil%" ... ```

any different about single quote ('') with double quote ("") because at sqllite there are 2 working normal. but at postgresql just single quote working and the double quote not working with query.

unknownit
  • 5
  • 4
  • 2
    Why does this surprise you? In SQL, the standard delimiter for strings is the single quote. – Gordon Linoff May 08 '20 at 15:26
  • 1
    Double quotes in standard SQL, and PostgreSQL for that matter, are for names. There is no column named "%tegil%" in your table, hence the error. You shoudn't use double quotes for string literals in SQLite either, even if the DBMS lets this slip uncommented. It can cause undesired bahaviour in rare situations where the string can be interpreted both as a name and as a literal. – Thorsten Kettner May 08 '20 at 15:28
  • See the chapters [Identifiers and keywords](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) and [Constants](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS) in the manual –  May 08 '20 at 15:34
  • Oh is see, i think the double quotes and single quotes same function. So basically single quote is the best way to use in any situation. – unknownit May 08 '20 at 15:35
  • Double quotes are for names containing special characters like whitespace. E.g. `select firstname as "First name" from elves`. So, you only need them, if you design your database with such names, which I don't recommend, or if you want a query result columns have such names, which I consider unnecessary. – Thorsten Kettner May 08 '20 at 15:44

0 Answers0