# SQL Query Builder in Report Data Source is producing an invalid query **URL:** \ **Category:** Ignition\ **Tags:** ignition80, database\ **Created:** [April 20, 2020, 7:27pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056 "2020-04-20T19:27:24Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Ali.Etezadkhah](https://avatars.discourse-cdn.com/v4/letter/a/b19c9b/32.png) [@Ali.Etezadkhah](https://forum.inductiveautomation.com/u/Ali.Etezadkhah)\ **Post date:** [April 20, 2020, 7:27pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/1 "2020-04-20T19:27:24Z")
Hi, I’m trying to create a test report using data from a transaction group. When I use the SQL Query Builder and select the entire table, the query works fine. ![1](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/f9bf509d213e1fae657bc21dcf7ef8a2ae419aef.png) When I select specific columns using the Builder, I get an error message about columns not existing. The columns do exist and there is no chance of a typo since the Builder created the query. ![2](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/40e426f1dad832a0b212d7b672e3e21d779b6484.png) ![3](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2fe78e875a2a98ea41c961068bae5872772b17ef.png) WARN: An error occurred while executing Query SELECT group\_table.N7\_0, group\_table.N7\_1, group\_table.N7\_2, group\_table.N7\_3, group\_table.N7\_4, group\_table.t\_stamp FROM group\_table-- ERROR: column group\_table.n7\_0 does not exist Hint: Perhaps you meant to reference the column “group\_table.N7\_0”. Position: 8 What is the problem here? I don’t have much experience with sql queries, but it looks very simple and correct. Thank you, Ali ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [April 20, 2020, 7:33pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/2 "2020-04-20T19:33:53Z")
Is this a Postgres database by chance? ---
**Author:** ![Ali.Etezadkhah](https://avatars.discourse-cdn.com/v4/letter/a/b19c9b/32.png) [@Ali.Etezadkhah](https://forum.inductiveautomation.com/u/Ali.Etezadkhah)\ **Post date:** [April 20, 2020, 7:41pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/3 "2020-04-20T19:41:55Z")
Yes. ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [April 20, 2020, 8:05pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/4 "2020-04-20T20:05:51Z")
Postgres is very snarky about column names that start with upper case, and in queries treats them like lower case unless you quote the column name. Your solutions would be to always use the column quote character or to create every column using lower case names. ---
**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:** [April 20, 2020, 9:48pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/5 "2020-04-20T21:48:21Z")
> [@KathyApplebaum](#): > > snarky { Trolling me, Kathy? } The only database that follows the SQL Standard (unquoted == uppercase, and comparison is otherwise case sensitive) on this topic is Oracle. Postgres adopted "unquoted == lowercase" prior to the adoption of the standard, but otherwise follows it. /: MS SQL Server selected case insensitivity and non-standard quoting (square brackets), also before the standard was adopted. MySQL also has non-standard quoting, and inconsistent case sensitivity (based on the filesystem used), but has no backwards-compatibility excuse: it didn't exist when the standard was published (1992). Personally, Oracle annoys the [expletive] out of me. PostgreSQL's consistency and focus on following the standard (except in this case) are a joy to this obsessive-compulsive greybeard coder. It is the _One True Database_™. ---
**Author:** ![Ali.Etezadkhah](https://avatars.discourse-cdn.com/v4/letter/a/b19c9b/32.png) [@Ali.Etezadkhah](https://forum.inductiveautomation.com/u/Ali.Etezadkhah)\ **Post date:** [April 20, 2020, 10:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/6 "2020-04-20T22:15:25Z")
I changed the transaction group and added int\_ to the beginning of the column names. None of the columns start with a capital letter, but I’m getting the same error. Is it possible that Postgres requires all lower case column names? WARN: An error occurred while executing Query SELECT group\_table.int\_N7\_0, group\_table.int\_N7\_1, group\_table.int\_N7\_2, group\_table.int\_N7\_3, group\_table.int\_N7\_4, group\_table.t\_stamp FROM group\_table-- ERROR: column group\_table.int\_n7\_0 does not exist Hint: Perhaps you meant to reference the column “group\_table.int\_N7\_0”. Position: 8 ![1](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/139c7bcee6dc96040de3afcef15e36369977b4da.png) ![2](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/3caba7f0a88a7be7f40e24b7b971f675df6632e1.png) ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [April 20, 2020, 10:19pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/7 "2020-04-20T22:19:00Z")
Yes, take a look at your error message. It converted the entire column name to lower case. ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [April 20, 2020, 10:21pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/8 "2020-04-20T22:21:13Z")
> [@pturmel](#): > > { Trolling me, Kathy? } I live to troll you, Phil. 😉 ---
**Author:** ![Ali.Etezadkhah](https://avatars.discourse-cdn.com/v4/letter/a/b19c9b/32.png) [@Ali.Etezadkhah](https://forum.inductiveautomation.com/u/Ali.Etezadkhah)\ **Post date:** [April 20, 2020, 10:48pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/9 "2020-04-20T22:48:11Z")
This test only has a few tags. If I were designing a real report, that would mean I would have to go through all the tags and make sure they all have lowercase column names. 1. Is there another workaround that would be less time consuming on a real project? 2. Sounds like this is caused by Postgres’s behavior. Would it be possible to have an option in the transaction group to automatically change the column names to all lowercase? And maybe append an under score, ie ambientTemp to ambient\_temp? 3. I’m just learning Ignition. Do you recommend I use a different database? I was trying to avoid using MySQL after watching this video. Thanks, Ali ---
**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:** [April 21, 2020, 12:34am UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/10 "2020-04-21T00:34:39Z")
Your problem is that you created the columns with a tool that quoted them for you. Add identifier quotes (double quotes) to the column names and the capital N will work. ---
**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:** [April 21, 2020, 12:40am UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/11 "2020-04-21T00:40:59Z")
Ooooh! Did dragging those column names from the query designer column list dialog to your query text fail to quote them? That would be a bug in the query designer. And not just for PostgreSQL. ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [April 21, 2020, 2:04pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/12 "2020-04-21T14:04:37Z")
> [@pturmel](#): > > Did dragging those column names from the query designer column list dialog to your query text fail to quote them? That would be a bug in the query designer. Good point (more lack of feature than a bug. PoTAYto, PoTAHto. 🙂) For technical reasons, I don't see it changing soon, but I'll put in a ticket. ---
**Author:** ![Ali.Etezadkhah](https://avatars.discourse-cdn.com/v4/letter/a/b19c9b/32.png) [@Ali.Etezadkhah](https://forum.inductiveautomation.com/u/Ali.Etezadkhah)\ **Post date:** [April 21, 2020, 5:13pm UTC](https://forum.inductiveautomation.com/t/sql-query-builder-in-report-data-source-is-producing-an-invalid-query/34056/13 "2020-04-21T17:13:25Z")
Hi Kathy, I have to disagree with you here. When a function does not work correctly, it is a bug, not a lack of feature. The only function of the SQL Builder is to create a valid Query. If it can’t do that, there is a bug. Postgres is one of the databases that are officially supported by Ignition. If there is a requirement for the database columns names to be all lowercase or enclosed in double-quotes, the Builder needs to provide that. The Database Query Manager does this. When I select a single column, it adds double-quotes to the column name. ![1](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2f6d5a96bddf9e4cd1d77856a58e35a7f5884dee.png)