PostgreSQL query for emails with Apostrophe

Question

I have a database table with email addresses with apostrophe such as "some.o'ne@somewhere.com".

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''ne@somewhere.com'

it doesn't find "some.o'ne@somewhere.com", 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''ne@somewhere.com'); 

SELECT emailaddress
,(emailaddress = 'some.o''ne@somewhere.com')::bool as escaped_apostrophe_single_quotes --true because it matches the value in the table
--,(emailaddress = "some.o'ne@somewhere.com")::bool as double_quotes --ERROR: column "some.o'ne@somewhere.com" does not exist
,(emailaddress = '"some.o''ne@somewhere.com"')::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'ne@somewhere.com" 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''ne@somewhere.com') ON CONFLICT DO NOTHING;