@laoneo Please do the same changes in the update sql script postgresql/4.0.0-2018-07-29.sql, too.

@laoneo Regarding your testing instructions: Here with PostgreSQL 11 installation doesn't fail without this PR, it just gives a lot of warnings in the log file of the postgresql server.

@alikon What do you prefer for PostgreSQL? Escape E'i\'m' or 2 single quotes 'i''m' for string literal containing a single quote?

@laoneo P.S. regarding update sql script postgresql/4.0.0-2018-07-29.sql: We can safely update it here because we don't support updates of 4 versions before beta.

Update file done.

well if it is a matter of what i prefer than my preference goes to 'i''m'
i think is more easily readble

Has both its advantages and disadvantages when doing a comparison between the joomla.sql files of the two database types to check if data is the same. Either you see an "E" at the beginning of a string or the additional single quote instead of a backslash in the middle of a string in the differences.

I've just verified that we don't have the single quotes within strings elsewhere in sql, so we are free in our choice and don't need to be consistent with elsewhere, and I can confirm that this PR covers all occurrences.

I have tested this item ✅ successfully on 59791d6

Hint for other testers: Different to what is described in section "Actual result", the installation worked here on PostgreSQL for me without the PR.

But there were lots of log entries like WARNING: nonstandard use of \' in a string literal at character ... in the postgresql server log file, and these disappear when having this PR applied.

There are many log entries like WARNING: nonstandard use of \\ in a string literal at character ... left in the postgresql server log file, so it seems we have elsewhere a problem with quoted backslashes in our sql. I would assume it is class paths in extensions table ;-) @laoneo Could you check that and if possible correct with another PR?

@laoneo For the other issue with quoting backslash which I've mentioned in my testing result: Do you want to make a PR for that, or shall I do that?

Can you?

I'll try, be patient.

@laoneo It seems fixing the double backslash warnings would require to use the E'...' syntax. To be consistent then this PR would be better if using that syntax, too. But I am not 100% sure about that yet.

Forget my previous post, this PR here is good as it is.

@laoneo add change for finder.sql file see Digital-Peak#14

I have tested this item ✅ successfully on 00e8113

tested on postgresql 11

we still have warning for the double/triple backslash, but matter for another pr

I have tested this item ✅ successfully on 00e8113

Tested on PotsgreSQL 11.

The last change (file administrator/components/com_finder/sql/install.postgresql.sql) I've tested by review.

From my point of view this file is not really needed. Or is there a scenario in which com_finder has to be (re-)installed (normal install or discovery)? @wilsonge what do you think?

RTC

@alikon For the (double) backslashes I have prepared a branch for a PR, using the E'...' syntax for those strings which contain \/ or \\. You could see the changes here: 4.0-dev...richard67:4.0-dev-fix-double-backslash-warnings-postgresql.

That removes almost all of the warnings from the postgresql-11.log, but a few remain of which I haven't found the source yet. Maybe these come from database actions in PHP? If so, we would have a problem if we don't want to have database-specific statements (what would be a mess). So maybe we should set a session variable standard_conforming_strings=on? The changes in this PR here will still be correct then, but for the other backslashes we might be ok then without any other change? Can you check that? If necessary we can talk on Glip in the evenings or on weekend.

i was playing on the same toy as you 😄
and i've the same conclusion as you

but a few remain of which I haven't found the source yet.

Yeah better to chat on Glip ......

Thanks!