UUID support in Multi Column Filter

[expired user #9707]'s profile image [expired user #9707] posted 8 years ago in Feature discussion Permalink

When querying a postgresql table that has a UUID column, the multi-column filter feature generates a filter that uses LIKE params. This of course doesn't work because UUID in PGSQL does not support the LIKE operator, so the column(s) in error must be removed manually. Can we exclude UUID columns from the multi-column filter?

It would be a little bit harder, but perhaps we could detect if the pasted string is a valid UUID and use the "=" operator for the UUID columns in that case :D

(Also adding my plus-1 on the Git thing -- I'd send a patch but I don't even have SVN installed on my system!)

Thanks,

Ben

ansgar's profile image ansgar posted 8 years ago Permalink

So, just for a conclusion:

  • In PostgreSQL mode, exclude UUID columns from a multi-column filter
  • ... except for when the value is detected as UUID, in which case the col LIKE '%foo%' should be col='foo' right?

Have a regular expression for detecting a valid PostgreSQL UUID string?

[expired user #9707]'s profile image [expired user #9707] posted 8 years ago Permalink

Sure, hope this helps

based on: http://www.postgresql.org/docs/8.3/static/datatype-uuid.html

I wrote this regex:

[a-f0-9]{8}(?:-?[a-f0-9]{4}){3}-?[a-f0-9]{12}|{[a-f0-9]{8}(?:-[a-f0-9]{4}){3}-[a-f0-9]{12}}

and then auto-generated this code, which I hope helps:

var
    Regex: TPerlRegEx;
    FoundMatch: Boolean;

Regex := TPerlRegEx.Create(nil);
Regex.RegEx := '\A(?:[a-f0-9]{8}(?:-?[a-f0-9]{4}){3}-?[a-f0-9]{12}|{[a-f0-9]{8}(?:-[a-f0-9]{4}){3}-[a-f0-9]{12}})\z';
Regex.Options := [preCaseless];
Regex.Subject := SubjectString;
FoundMatch := Regex.Match;
[expired user #9707]'s profile image [expired user #9707] posted 8 years ago Permalink

And posted too soon -- to answer your questions

In PostgreSQL mode, exclude UUID columns from a multi-column filter

Yes

... except for when the value is detected as UUID, in which case the col LIKE '%foo%' should be col='foo' right?

Yes

Code modification/commit from ansgarbecker, 8 years ago, revision 9.3.0.5057
Use an exact comparison in multi-column filters for some PostgreSQL data types to overcome SQL errors, e.g. UUID, INT etc. Also, prevent other errors by matching the value against a certain regular expression. If it does not match, leave this column away. Introduces the new TDataType.ValueMustMatch property. See http://www.heidisql.com/forum.php?t=20953
ansgar's profile image ansgar posted 8 years ago Permalink

r5057 introduces a regular expression per data type. I added expressions for UUID and some simple ones for INT, SMALLINT and BIGINT. The condition is left away if the value does not match the expression of the relevant data types, just like discussed above.

I can now easily extend this feature for other data types which throw SQL errors when the user enters a non-matching value. I'm not sure on which data types that makes also sense.

[expired user #9707]'s profile image [expired user #9707] posted 8 years ago Permalink

Awesome, thanks so much for helping with this!

[expired user #9707]'s profile image [expired user #9707] posted 8 years ago Permalink

P.S. if it's not too hard to add BOOL/BOOLEAN support for true|false that would help too (or exclude BOOL/BOOLEAN) columns.

[Window Title]
UC Postgres: Error

[Content]
ERROR:  syntax error at or near ""user_is21""
LINE 2: "user_is21" LIKE '%4bde2d2f%' OR "user_is_testaccount" LIKE ...
        ^

[OK]
Code modification/commit from ansgarbecker, 8 years ago, revision 9.3.0.5061
Use an exact comparison for PostgreSQL's BOOLEAN datatype. See http://www.heidisql.com/forum.php?t=20953
ansgar's profile image ansgar posted 8 years ago Permalink

r5061 now accepts only true and false for a BOOLEAN column in PostgreSQL.

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