PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15474
PG Version11.0
OSAny
Opened2018-10-31 16:25:06+00
Reported byMicheal Taylor
StatusOpen

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15474
Logged by:          Micheal Taylor
Email address:      (redacted)
PostgreSQL version: 11.0
Operating system:   Any
Description:        

When looking for postgresql documentation on characters that need to be
escaped, and how to escape them, I consistently get to this page:

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-SPECIAL-CHARS

That page goes over high levels of escaping numerous things, but in the
special characters and operators, it doesn't clearly show how to escape the
operators or special characters within a more complicated query structure. 
For example, if I have the following table:

            CREATE TABLE IF NOT EXISTS {tablename}
            (
                time        TIMESTAMP,
                case_number VARCHAR(25),
                jira        VARCHAR(25),
                status      VARCHAR(25),
                fqdn        VARCHAR(255),
                subject     TEXT,
                description TEXT,
                comment     TEXT
            )

And the following insertion information:

    INSERT INTO orphans(time, case_number, fqdn, status, subject,
description)
    SELECT '{timestamp}', '{case_number}', '{fqdn}', 'new', '{subject}',
'{description}'
    WHERE
        NOT EXISTS (
            SELECT case_number, status FROM orphans
            WHERE case_number = '{case_number}'
        )

Where all columns inputs are strings, if any of those inputs have special
characters like % or ', it is not clear by quick inspection of the
documentation how to escape those characters.  A simple table elaborating on
the escapes for each special character would be incredibly helpful at
determining how to translate those escapes for cleaning strings prior to
insertion so those of us using postgresql can quickly write cleaning
functions for data.

Messages

DateAuthorSubject
2018-10-31 16:25:06+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15474: Special character escape sequences need better documentation, or more easily found documentation
2018-10-31 16:35:51+00"David G(dot) Johnston"Re: BUG #15474: Special character escape sequences need better documentation, or more easily found documentation
2018-10-31 16:40:59+00"David G(dot) Johnston"Re: BUG #15474: Special character escape sequences need better documentation, or more easily found documentation
2018-10-31 16:51:43+00Andrew GierthRe: BUG #15474: Special character escape sequences need better documentation, or more easily found documentation
2018-10-31 17:23:03+00Mike TaylorRe: BUG #15474: Special character escape sequences need better documentation, or more easily found documentation
2018-10-31 17:23:21+00Mike TaylorRe: BUG #15474: Special character escape sequences need better documentation, or more easily found documentation
2018-10-31 20:34:18+00"David G(dot) Johnston"Re: BUG #15474: Special character escape sequences need better documentation, or more easily found documentation
2018-11-01 07:13:02+00Heikki LinnakangasRe: BUG #15474: Special character escape sequences need better documentation, or more easily found documentation