I have tested this item ✅ successfully on 1a71629

Tested by code review:

1. The PR covers all occurrences of '0000-00-00' or '0000-00-00 00:00:00' in joomla.sql or any schema update sql for PostgreSQL.
2. The PR covers all occurrences of "after" for columns in DDL statements in joomla.sql or any schema update sql for PostgreSQL.
3. There is no occurrence of "before" for columns in DDL statements in joomla.sql or any schema update sql for PostgreSQL to be corrected, too.

I think the errors corrected by this PR were the reason (or some of the reasons) why currently installation of 4.0 with PostgreSQL fails. I will test soon if it works with this PR applied.

i'm afraid this doesn't cover all the glitch with postgresql... but a start

Yes, it solves many problems. What is remaining then is an issue with double backslash in string values, mainly in values for column namespace of table #__extensions. Not sure if this column still has to be filled by joomla.sql or schema updates because I remember there was a PR merged which fetches the namespace column value from the extensions manifest XML.
As far as I could read in docs, strings with double backslash have to be prepended by an "E" for marking it as escape string. I've tried to fix that here for the extensions table but there are still errors about double backslash reported in the postgresql log file.

Ah I just see that pr for the namespace from manifest file is not merged yet: PR #21766 .
So for now we would have to fix those double backslashes in strings, and then postgresql installation should work.
Do you want to do it with this PR here? Or a new one?

I would not mix things. Better to do one issue in one pr, instead of fixing many things in the same pr. Especially for the namespacing column. I would wait what comes out in #21766, before putting much effort into updating the namespaces in the installer file.

Yes, I agree. I just see there are more errors with installation on PostgreSQL, mainly from workflow stuff, so we should get this good PR here merged for coming forward.

Thanks for this one.