I'd like to protect the email field of the following table:
CREATE TABLE users (
email VARCHAR(255) NULL CHECK (email ~* '\A[^@]+@[^@]+\Z')
);
What I would like to do is to allow strings such as:
bob@example
But I would like to avoid strings such as:
bob@example\nfuu
I heard that the \Z constraint allows any chars after another line (with \n).
According to best practices in regex, the \z is better than \Z as it allow only one line, but it seems to be not supported by PostgreSQL. And the $ is not better.
Am I true?
Edit:
I tested this:
CREATE TABLE users (
email VARCHAR(255) NULL CHECK (email ~* '\A[^@\n]+@[^@\n]+\Z')
);
CREATE UNIQUE INDEX users__lower_case__email ON users(lower(email));
--
INSERT INTO users (email) VALUES ('\nfoo\n@\nbar\n');
Apparently the constraint didn't work: the wrong email was added in the table.
Note that negated character classes match any characters but those defined in the set. So, [^@] matches any chars but @, including newline symbols. To exclude a newline, just add it to the class.
Use
email ~* '\A[^@\n]+@[^@\n]+\Z'
As \Z only matches only at the end of the string there is no way this regex could allow a newline in the input.
I would highly suggest you review my post for the right way to store an E-Mail address in PostgreSQL.
Here is some example code,
CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );
SELECT 'asdf@foobar.com'::email;
For your table,
CREATE TABLE users (
user_email email
);