Hello

I have a program using a postgres database over odbc. I will change it to use a sqlite3 database.

On the client side are statements like:

select * from table where col ilike value

This fails in sqlite3 on the missing ilike operator. Is there something I can do here?

Thank you

rene

Have you tried exchanging it for "LIKE".

The sqlite like() comparison is case insensitive (by default).

I do not have acces to the sql calling function. I was looking for a possible workaround without changing the original sql statement.

Regards Rene

Then you have a problem.

You will need to write your own ILIKE function as an extension and add this into sqlite as a loadable extension.

You will, of course, be able to reuse the existing LIKE function as this if equivalent to postgres' ILIKE.

However, you will presumably now be questioning why you are trying to change the database backend in the first place, if it is designed for a client that you cannot control or adapt.

Who knows what other database specific gotchas lurk in there that you will also not be able to work around?

Yeah, but in rene's defense, one obstacle to finding information is not suspecting that it is there in the first place. I mean, if you have never encountered a case insensitive LIKE operator, why would you read the docs for LIKE to see it this one happens to behave differently?

It is of course good advice to actively look for differences between implementations when you move from one to the other. But it is all to easy to miss some detail, and then to neglect looking in what is in retrospect the obvious place.

Anecdote time: I once got published a short paper with a new proof for a well-known theorem. Only it wasn't new at all, someone else had published the same proof a decade or two earlier – in the same journal! He was not at all pleased, and told me angrily how easy it would be to find his paper. Which is true, if you knew the title! But unfortunately, my search attempts had somehow missed his somewhat obscure title for the paper. These things happen. (With apologies for the digression if you don't think this is relevant.)

I very much agree with you in general, but very much disagree in the specific.

Any Google search for "sqlite ILIKE" will highlight all of these points (case sensitive Vs insensitive LIKE, lack of ILIKE in sqlite, potential workarounds) immediately - very unusually for Google, all of the links on the entire first page of results were relevant to this problem.

The lack of ability to change the generated code is an important extra constraint (unfortunately not specified in the OP) as well as the reason behind wanting to exchange backends in the first place (so, very high risk of this being an X/Y problem).

I'm unconvinced that this question will have a satisfying answer.

I will change it to use a sqlite3 database

Hello Rene!

Might I ask why are you planning to do this?

We use postgres to develop and run simulators. The simulator data and some more are in a sqlite db which is used by the user interface. I would like to run the simulator without postgres. It would simplify the backup, restore and installation of simulators.

Thankyou Rene.

I think by far the easiest solution would be updating the SQL being used so that it is all in the sqlite "dialect". I realise you have already addressed this as outside specifications, but it would seem logical if you are trying to standardise on one database in such a way, to be using one standard SQL dialect throughout.

Otherwise, the options would be to translate the SQL in flight somehow or instrumenting the version of SQLite that you are using. This could be either by forking the project and renaming or duplicating functions (not advisable) or adding your own compatible ILIKE function as an installable extension.

I cannot see any way that you can address this issue without one of these options. There is no "configuration" only change that you can make to a sqlite database that will allow the database software to understand your posted query.

If one of these approaches appeal, or you have an alternative route to try then do please let us know and I'm sure someone will be able to comment further about that given approach. There are hopefully enough keywords there that you can search around the documentation a little.

However, without being able to undertake one of these options I don't see how you can proceed. Unfortunately I am not aware of a sqlite fork that is designed to provide postgres compatibility (though presumably it is possible that one exists).

I think I will go the extension road. So I do not have to touch the program and have the ability to add more functionality if needed.

Thank you for your advices.

For what you want, I think that's the right course. Good luck!

According to the Postgress documentation: https://www.postgresql.org/docs/current/functions-matching.html

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

This means that ILIKE is a postress extension to standard SQL that does not exist anywhere else that does not claim bug for bug and bogon for bogon compatibility with Postgress. SQLite3 certainly does not claim to be a bug for bug and bogon for bogon compatible with Postgress. I do not think anything does.

Please note that extensions are not bogons, but made for the convenience of users.

SQLite3 does not even claim to be even close to the standard - much less than PostgreSQL anyway.

OTOH, SQLite does copy good ideas from PostgreSQL. The RETURNING clause is a quite recent example. So why shouldn't ILIKE be another one?

What can be done in most SQL engines is

UPPER(x) LIKE UPPER(y)

Yes, ilike is a bogon -- is a weird confabulation of "standared" LIKE and the pragma which controls its case sensitivity. It is a bogon. It also operates on "Code Pages", not on unicode.

As for the RETURNING clause, that was added for the kiddies who cannot fathom an imperitive command language that does not have "feedback for the children".

They fail to understand that the command to make it so results either in what you commanded be done is done, or it is not done and that absent any feedback, exactly what was commanded be done was done.

The children are uncertain nonetheless unless they are given feedback of the obvious. Perhaps this says more about the status of the educational and social systems that the children themselves.

Actually, pragmas are bogons. Good systems use settings which default to the standard ISO SQL behaviour.

And yes, PostgreSQL does exactly that - and offers something more. Plus, PostgreSQL does not "operate on 'code pages'", as the standard encoding is UTF-8 (see http://utf8everywhere.org/) which is THE encoding for Unicode.

Maybe you fail to recognize not only the convenience, but also other advantages of the RETURNING clause. It has nothing to do with feedback about whether a command was successful or not.

And the SQL standard demands that a feedback is given as the SQL state. The same is true for good old Unix/Linux systems, where feedback is in the exit status of a command. Whether a visual feedback is additionally given or not is just a matter of taste or settings (set QUIET 1 to turn visual response off in psql).

Whether a visual feedback is additionally given or not is just a matter of taste or settings (set QUIET 1 to turn visual response off in psql).

I would be quite upset if the computer hosting my Accounts (as in a Bank with millions of customers) decided to beep and flash every time a transaction was posted. The resultant cacaphony would cause me to immediately return the multi-million dollar computer to its manufacturer for repair.

Attitudes like your is why every single device must be inspected for hidden camera's, microphones, and speakers. One must wonder why it is necessary to have things beep and boop and flash and spy when they ought to simply do the function for which they were designed.

It must be the moron factor that cannot fathom something working without having some nefarious hidden mechanism totally unconnected with the primary purpose to engage the moron so that it thinks the device is working.

As for the RETURNING clause, […]

Maybe I am one of the kiddies myself, but I do find the RETURNING clause very useful for interactive use. Start a transaction, issue a command, inspect the result to see if it actually did what I intended, commit if yes, rollback and try again otherwise. But of course most database usage is not interactive, and for use in production, triggers provide a better mechanism than messing around with the output of a RETURNING clause.

On postgres, I have occassionally found it useful to do stuff like

WITH rows as (DELETE FROM somewhere WHERE condition RETURNING *)
INSERT INTO elsewhere (SELECT * FROM rows);

and variants thereof.

(Edited to addt: But it was a fine rant.)

Yes, but not only. It also serves as a way to retrieve all the values which were generated by a sequence, a current-function or triggers.

This reduces the number of round trips, which is very helpful in client server databases, not so much in SQLite where acces to the database is just a local file access away.

The generated values can be presented to the user, where all the automatically filled-in fields show on the screen: You registered at <timestamp> and you have customer number <whatever>. Nice confirmation.