Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

POSTGRE : using ILIKE instead of LIKE

napool's profile image napool posted 1 week ago in General Permalink

Hello,

For POSTGRE DB, it would be better to use "ILIKE" command for any request auto generated with multi-column filters, than LIKE (which is a SQL command).

Thanks

ansgar's profile image ansgar posted 1 week ago Permalink

Could you please explain why ILIKE is better than LIKE? I'd say both of them are SQL commands.

napool's profile image napool posted 1 week ago Permalink

Because of this : Description

We won't have this error if HeidiSQL is using ILIKE command instead of LIKE

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 1 week ago Permalink

Ok, then what's the general difference between both?

napool's profile image napool posted 1 week ago Permalink

'name' LIKE "%paris%" doesn't work whereas 'name' ILIKE "%paris%" is working

ILIKE accepts "%"

ansgar's profile image ansgar posted 1 week ago Permalink

According to this tutorial and the official docs, ILIKE is just the case insensitive version of LIKE. But if you say ILIKE in contrast to LIKE also works for double precision columns, the docs must be incomplete here.

ansgar's profile image ansgar posted 1 week ago Permalink

With ILIKE, I am now getting

Operator existiert nicht: timestamp with time zone ~~* unknown

Same with LIKE, so I will have to cast TIMESTAMP columns as text.

Code modification/commit 7abc1d1 from Ansgar Becker <anse@heidisql.com>, 1 week ago, revision 6028
Prefer ILIKE over LIKE operator on PostgreSQL servers, for auto-generated queries, to overcome errors with double columns. See https://www.heidisql.com/forum.php?t=36500
ansgar's profile image ansgar posted 1 week ago Permalink

I modified internal queries for that multi column filter, the right-click quick filters, and the "Find text on server" logic.

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




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.