PostgreSQL provides two useful functions to aid in the safe development of dynamic SQL:
quote_identfunction accepts and returns a
texttype. Whatever text you pass into
quote_identwill be suitably escaped such that you could safely use it as an identifier within a dynamic SQL statement.
According to the documentation, you should always pass table and column identifiers into the
quote_identfunction for safety. Calling
mytable, however calling
quote_literalfunction accepts and returns a
texttype. Whatever text you pass into the
quote_literalfunction will be escaped so that you can safely use them in dynamic SQL.
You should always pass values or literals into the
quote_literalfunction. By doing so, all special characters such as quotes will be safely dealt with. Calling
Both of these functions work a treat, however there is a caveat with the
quote_ident function which isn’t well documented. When creating objects in PostgreSQL, they object names are automatically lowercased unless you create the object using double quotes. As a simple example:
CREATE TABLE MyTable (id integer, name varchar);would result in an object
CREATE TABLE "MyTable" (id integer, name varchar);would result in an object
MyTablebeing created; note the casing.
Now lets assume you wanted to create some dynamic SQL to fetch information out of the first example table above. If you issued
quote_ident('mytable'), your dynamic SQL statement will execute because the value returned from
quote_ident is lowercase which matches the table name. If you called
quote_ident('MyTable'), your dynamic SQL statement will report an error stating that it cannot find the table or relation.
Creating dynamic SQL in PostgreSQL to fetch data out of the second example above, you would run into the reverse scenario. Issuing
quote_ident('mytable') would produce an error, while
quote_ident('MyTable') would execute without error.
If you create your database objects without using double quotes, then it’s important to remember to not pass capitalised parameters into
quote_ident. The opposite is of course true as well, if you create your objects using double quotes then you must remember to pass in the same casing to
quote_ident applies double quotes (be it from capitised letters, spaces or special characters), the SQL engine within PostgreSQL will assume that an object exists with the explicit name matching the returned value of the