On: Lesser-known Postgres features

[mixmastamyk]

This example didn't bring anything new to the table, only adds redundant extra chars:

    SELECT *
    FROM users
    WHERE email ~ ANY('{@gmail\.com$|@yahoo\.com$}')

Perhaps they were intending something similar to the following example instead. This one works but has a several potential lurking issues:

    with connection.cursor() as cursor:
        cursor.execute('''
            SELECT *
            FROM users
            WHERE email ~ ANY(ARRAY%(patterns)s)
        ''' % {
            'patterns': [
                '@gmail\.com$',
                '@yahoo\.com$',
            ],
        })

The dictionary-style interpolation is unnecessary, the pattern strings should be raw strings (the escape is ignored only due to being a period), and this could be a SQL injection site if any of this is ever changed. I don't recommend this form as given, but it could be improved.

[jrmiii → mixmastamyk]

> This example didn't bring anything new to the table, only adds redundant extra chars:

OP indicated as much saying:

> This approach is easier to work with from a host language such as Python

I'm with you on the injection - have to be sure your host language driver properly escapes things.