PostgreSQL query for emails with Apostrophe

Question

I have a database table with email addresses with apostrophe such as "some.o'[email protected]".

I would like to:

1. Query this table, and check if this email address exist
2. insert this email address if it doesn't exist in the table

I tried:

SELECT * 
FROM EMAILTABLE 
WHERE emailaddress LIKE 'some.o''[email protected]'

it doesn't find "some.o'[email protected]", so it's treating it as it doesn't exist!

Answer 1

Double-quotes around some text indicates you are looking for an object name, unless you are embedding the double-quotes within single-quotes.

So:

DROP TABLE IF EXISTS emailtable;

CREATE TEMP TABLE emailtable (emailaddress text);

CREATE UNIQUE INDEX idx_emailtable_emailaddress ON emailtable (emailaddress);

INSERT INTO emailtable (emailaddress) VALUES ('some.o''[email protected]'); 

SELECT emailaddress
,(emailaddress = 'some.o''[email protected]')::bool as escaped_apostrophe_single_quotes --true because it matches the value in the table
--,(emailaddress = "some.o'[email protected]")::bool as double_quotes --ERROR: column "some.o'[email protected]" does not exist
,(emailaddress = '"some.o''[email protected]"')::bool as double_quotes --false because the value in the table doesn't include double-quotes
FROM emailtable;

In the second test, it's assuming that "some.o'[email protected]" is a column, because it's a name inside double-quotes.

You can see from the first and third tests that the presence of the double-quotes inside the string are saying that these strings are different. The first one is the same as the value in the table. The other one isn't.

Anyway, I think the simplest way to handle is with a unique index on the emailaddress. If you then try to insert the same value that already exists, it won't do anything.

INSERT INTO emailtable (emailaddress) VALUES ('some.o''[email protected]') ON CONFLICT DO NOTHING;

Answer 2

Thanks for the replies (just replacing single quote with two single quotes doesn't work for me as it stores the email addresses with two single quotes) This query worked fine for me:

SELECT * FROM EMAILTABLE WHERE emailaddress = (E'some.o\'[email protected]')::text;