topic PostgreSQL function: is it a bug? in Discussions

PostgreSQL function: is it a bug?

Franck_R — Fri, 09 Jun 2023 23:41:07 GMT

Hi,

I have a problem with queries on a PostgreSQL database using the JMP query builder...
Indeed some PostgreSQL functions seem not working properly in the query builder...

For example if I want to retrieve part of a string from the position of one of the character:
this command could ben written in PostgreSQL:

SELECT substring('Thomas' from position('h' in 'Thomas') for 2) AS bug_test

But if I write it in "add computed column" in the JMP query builder with the SQL formula editor:

substring('Thomas' from position('h' in 'Thomas') for 2)

I get this error:

whereas this same command is well executed if I write it by hand in SQL Custom:

And in that case it is translated in jsl like other SQL command...

What I don't understand is that I can sometimes write complex formulas in query builder's "computed columns" that are in pure PostgreSQL syntax and everything works fine! Like:

This computed column works fine and return no error message. It is however a specific postgresql code...

Is this a known bug on some PostgreSQL functions? How to work around it?

Thanks for your help!

Franck

Re: PostgreSQL function: is it a bug?

briancorcoran — Thu, 22 Oct 2020 12:40:35 GMT

Add Computed Column has a list of some extensions that it will support for database specific SQL, but postgresql is not one of them. By default you probably are getting Generic SQL in the dropdown at the upper left of the formula editor. There are some extensions supported for Oracle, MySQL, SQL Server, SAS etc.

We will need to consider a postgresql specific extension set for a future version of JMP.

Brian Corcoran

JMP Development

Re: PostgreSQL function: is it a bug?

Franck_R — Thu, 22 Oct 2020 12:56:00 GMT

Hello Brian

Thanks for your answer

What is strange for me is that for example the "lpad" or "to char" functions are specific syntax from PostgreSQL, not some generic SQL or Oracle SQL,... and works very well:

t2.lot_id || '-' || lpad(to_char(t2.wafer_nr, 'FM999'), 2, '0')