SQL Query Builder in Report Data Source is producing an invalid query

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.

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.


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

Is this a Postgres database by chance?

Yes.

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.

{ 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™.

3 Likes

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

2

Yes, take a look at your error message. It converted the entire column name to lower case.

I live to troll you, Phil. :wink:

1 Like

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

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.

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.

Good point (more lack of feature than a bug. PoTAYto, PoTAHto. :slightly_smiling_face:)

For technical reasons, I don't see it changing soon, but I'll put in a ticket.

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.