# Strict format for SQL queries?

**URL:** <https://forum.inductiveautomation.com/t/strict-format-for-sql-queries/12410>\
**Category:** Ignition\
**Created:** [November 28, 2016, 1:58pm UTC](https://forum.inductiveautomation.com/t/strict-format-for-sql-queries/12410 "2016-11-28T13:58:59Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sg7](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@sg7](https://forum.inductiveautomation.com/u/sg7)\
**Post date:** [November 28, 2016, 1:58pm UTC](https://forum.inductiveautomation.com/t/strict-format-for-sql-queries/12410/1 "2016-11-28T13:58:59Z")

</div>

It looks like SQL query for SQL query tag has to have very specific format:

1. OK:  
SELECT “value\_int” FROM “w\_0\_5” WHERE object\_key=‘0\_5\_lamp’

2. OK:  
SELECT value\_int FROM w\_0\_5 WHERE object\_key=‘0\_5\_lamp’

3. WRONG:  
SELECT value\_int FROM w\_0\_5 WHERE object\_key=“0\_5\_lamp”

4. WRONG:  
SELECT ‘value\_int’ FROM w\_0\_5 WHERE object\_key=‘0\_5\_lamp’

5. WRONG:  
SELECT value\_int FROM ‘w\_0\_5’ WHERE object\_key=‘0\_5\_lamp’

Which style is recommended?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 28, 2016, 2:20pm UTC](https://forum.inductiveautomation.com/t/strict-format-for-sql-queries/12410/2 "2016-11-28T14:20:46Z")

</div>

It varies with database type. The SQL standard says double quotes are used with identifiers (eg. table and column names) while single quotes are used with string literals.  
Unquoted identifiers are upper-cased by some backends, lower-cased by a few, and treated as case-insensitive by others. Form #1 is the canonical SQL standard format, where the table and column names really are lower case. I try to use form #1 so my customers can switch backends if they need to.  
String literals must use single quotes. Some string literals will be converted to other types as needed, like dates or times, but in a backend-specific manner.

Bottom line: read the SQL syntax rules for your SQL backend.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 28, 2016, 2:26pm UTC](https://forum.inductiveautomation.com/t/strict-format-for-sql-queries/12410/3 "2016-11-28T14:26:09Z")

</div>

Let me add: my preferred backend is PostgreSQL, which tries very hard to follow SQL standard syntax. Which means you can follow [this guide](https://www.postgresql.org/docs/9.5/static/sql-syntax.html) and be in pretty good shape for other backends. (Modulo unquoted identifiers.)