Incorrect PostgreSQL escaping for schema name exclusion in query

[amillet]

Hello,

I've encountered an issue related to PostgreSQL escaping within a query that is intended to exclude certain schema names (for example pgmain), specifically those starting with pg_. The query in question does not correctly escape the underscore character. Here's the problematic query:

SELECT schema_name
FROM   information_schema.schemata
WHERE  schema_name NOT LIKE 'pg\_%'
AND    schema_name != 'information_schema'

According to the PostgreSQL documentation (https://www.postgresql.org/docs/current/ddl-schemas.html), schema names beginning with pg_ are reserved for system. The documentation implies that to correctly escape the underscore in SQL queries, a double backslash (\\) should be used instead of a single backslash (\).

Therefore, the corrected version of the query should be:

SELECT schema_name
FROM   information_schema.schemata
WHERE  schema_name NOT LIKE 'pg\\_%'
AND    schema_name != 'information_schema'

Do you think we can modify the impacted code? I believe there are several queries involved.

[greg0ire]

Yes. Please send a PR, with tests demonstrating the bug.