HeideSQL with PostgreSQL DB - finding correct reference for SQL syntax

PaulKraemer's profile image PaulKraemer posted 3 months ago in General Permalink


I just started using HeideSQL with a PostgreSQL database that was setup by someone else. I have no need or intention to make changes to this database, but I am trying to learn how to pull information out of it when needed. I have experience with SQL on other platforms, but this is my first time using HeideSQL and PostgreSQL. I've already been successful in creating some simple queries, and I'd like to start learning how to build more complex queries. One thing that isn't clear to me is whether the SQL syntax I can use is governed by HeideSQL or by PostgreSQL?

From what I've seen so far, it seems that I am required to enclose table names and field names in double-quotes, whereas I don't see this being a requirement in some PostgreSQL documentation I've found. For example, the following query works..

SELECT * FROM "Associate" WHERE "TenantTag" = 'HPMile'

...whereas the following query results in an error...

SELECT * FROM Associate WHERE TenantTag = 'HPMile'

Within HeideSQL, when I go to Help | SQL Help, I get a message that says "SQL Help not available. HELP <keyword> requires MySQL 4.1 or newer"

If anyone can point me towards the correct reference I should use as I am trying to learn how to write queries on this platform, I'd greatly appreciate it.

Thanks in advance, Paul

ansgar's profile image ansgar posted 3 months ago Permalink

The SQL syntax is predefined by the server, not the client. The client/HeidiSQL passes your SQL query to the server, and if the server accepts the syntax, HeidiSQL retrieves a result from the server and shows it to you in a grid. If the server thinks your query has an error, the error is returned and HeidiSQL shows this in a dialog.

Regarding the quoted identifiers, I am nearly sure I did the same as you and got no error with non-quoted table names. Perhaps when the table name is also a reserved word or a name of a function, you should get an error.

Yes, the SQL help is only available on MySQL and MariaDB. HeidiSQL was supporting only MySQL for long years. PostgreSQL and some other server types came in later, and support for these is still very incomplete. So the SQL help is one of these features from old times, and not supported on PG.

Please login to leave a reply, or register at first.