What is the difference running a SQL statement on psql and administration tools?

Question

There is a discrepancy when I run the following SQL statement on different clients:

SELECT 13323::REGCLASS;

OID 13323 in my database belongs to sql_sizing. When I run the query in psql the output is:

           regclass
-------------------------------
 information_schema.sql_sizing

Yet, when I run on the HeidiSQL:

sql_sizing

As you can see in the psql it comes with schema identifier. The problem is thrown for only information_schema. Other schema objects(actually tables, I am working on tables now) do not cause any problem.

The search_path is also same for both of my clients(I am using the same user, postgres):

   search_path
-----------------
 "$user", public

Should it be that HeidiSQL parsing itself or is there a client parameter that I can set the equalize them? What other reasons could cause this problem?

Postgresql version is 15.3.

HeidiSQL version is 12.5.0.6677.

Note: dbfiddle.uk is also reacts as psql

Thanks!

Then I doubt HeidiSQL is messing with the value itself, rather they must use a different session setting. `search_path` would've been my first guess as well, if it's not that, I dunno. — Bergi, May 27 '23 at 14:16
1) `psql` is an administrative tool and I would believe it over other tools. 2) HeidiSQL is MySQL-centric so I would take that into account. 3) This is something that should be filed here [HeidiSQL Issues](https://github.com/HeidiSQL/HeidiSQL/issues). — Adrian Klaver, May 27 '23 at 14:37
On the HeidiSQL' s left side dock information_schema was already clicked but on the query tab when I run `show search_path` it was giving me `"$user", public`. I disconnected and reconnected from/to database again. Afterwards, I clicked the information schema and run `show search_path` and got the result, `information_schema, "$user", public`. Sorry people, it needed a reconnection again :S. — Umut TEKİN, May 27 '23 at 14:51
@UmutTEKİN Ah then it makes sense. You might want to either delete your question or [answer it yourself](/help/self-answer) — Bergi, May 27 '23 at 19:37

score 0 · Accepted Answer · answered May 28 '23 at 11:49

On the HeidiSQL' s left side dock information_schema was already clicked but on the query tab when I run show search_path it was giving me "$user", public. I disconnected and reconnected from/to database again. Afterwards, I clicked the information schema and run show search_path and got the result, information_schema, "$user", public. It needed a reconnection again.

What is the difference running a SQL statement on psql and administration tools?

1 Answers1