Insert text with single quotes in PostgreSQL

The art of leveraging helper functions

Using quote_literal and quote_nullable

Trust the PG helper functions, they have got your back. quote_literal and quote_nullable will handle your string literals safely.

INSERT INTO table_name(column_name) VALUES(quote_literal('John''s apple'));
-- John's apple is getting first class treatment


When null is the unsolicited guest, quote_nullable ensures that everything is under control.

Embracing parametrization

Stay classy and secure. Use parameterized queries with (PREPARE and EXECUTE) to guard against SQL injection.

PREPARE insert_text(text) AS INSERT INTO table_name(column_name) VALUES($1);
EXECUTE insert_text('John''s apple');
-- John's apple is doing the SQL equivalent of a moonwalk


Using format like a pro

format function is your handy multi-tool for dynamic query construction.

INSERT INTO table_name(column_name) VALUES(format('John%s apple', '%L'));
-- John's apple pulling off a costume change on the fly


That %L format specifier has got your back when quotes try to act smart.

Ironing out special scenarios

Taming non-standard conforming strings

Extraordinary times call for extraordinary measures. When standard_conforming_strings is off, backslashes (\) need to bring a partner.

INSERT INTO table_name(column_name) VALUES(E'John\\'s apple');
-- John's apple walking with a clone to confuse PostgreSQL 


Playing fair with identifiers

The rules of SQL demand using quote_ident for identifiers. This averts syntax errors or possible injection attempts.

INSERT INTO table_name(quote_ident(column_name)) VALUES('Value');
-- Quotes doing the heavy lifting so that we can chill.


Impeccable PostgreSQL etiquette

Avoiding nasty surprises

Avoid the temptation of manual string concatenation. Keep it professional and error-free.

Settings - your backstage heroes

Get a first-hand understanding of PostgreSQL configuration options. Deliver reliable SQL queries that are perfectly compatible.

Safety first

Nope, escaping quotes won't save you from SQL injection. Don't believe the hype. Embrace parameterized queries, especially when dealing with user inputs.