Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


CREATE TABLE t
(
  id SMALLINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  
  -- title TEXT NOT NULL,
  -- fname TEXT NOT NULL,
  
  surname  TEXT NOT NULL,
  
  -- ...
  -- ... other fields
  -- ...
  
  symbol TEXT NOT NULL  -- Not part of original table - added to help with testing, 
                        -- see below.
);

CREATE TABLE


--
-- Can workaround this using the TRANSLATE() function, but this is messy!
--
-- see https://stackoverflow.com/q/13596638/470530
--

CREATE EXTENSION "unaccent";

CREATE EXTENSION


--
-- Can workaround this using the UPPER() function, not too bad!
--

CREATE EXTENSION "citext";

CREATE EXTENSION


--
-- Works for the last three variant of Æ, but not the first two!
--

SELECT
  UNACCENT('Ǣ/ǣ, Ǽ/ǽ, Æ̀/æ̀, Æ̂/æ̂ and Æ̃/æ̃'),  -- failing chars not found in unaccent.rules
  UNACCENT('ḃ, ċ, ḋ, ḟ, ġ, ṁ, ṗ, ṡ, ṫ');   -- all found in unaccent.rules


unaccent
unaccent


Ǣ/ǣ, Ǽ/ǽ, AE/ae, AE/ae and AE/ae
b, c, d, f, g, m, p, s, t



SELECT 1


ALTER TABLE t
  ALTER COLUMN surname
    SET DATA TYPE CITEXT;

ALTER TABLE


--
-- First, make sure that puncuation symbols that are not valid for names 
-- and digits aren't allowed at all - so, in this case we disallow certain characters.
--

-- 
-- Note the `\` (backslash) before characters full stop (`.`), open and close square
-- bracket (`[]`), before open and close parentheses (`()`) and backslash itself (`\`)
-- but not `$`, `^` or `?` which are also regex meta-characters - this involved lots of
-- testing!
--

ALTER TABLE t 
  ADD CONSTRAINT no_punct_no_digit_ck
    CHECK (NOT REGEXP_LIKE(surname, $$[_#=0-9@#$.\[\]?<>^/(){}~|\\]$$));

ALTER TABLE


--
-- See other fiddle for lots of testing! https://dbfiddle.uk/rlf5zPVG
--
ALTER TABLE t DROP CONSTRAINT no_punct_no_digit_ck;

ALTER TABLE


--
-- Now ensure least two letters
--

ALTER TABLE t 
  ADD CONSTRAINT no_punct_no_digit_ck
    CHECK ((NOT REGEXP_LIKE  (surname, $$[_#=0-9@#$.\[\]?<>^/(){}~|\\]$$))   AND
          ((    REGEXP_COUNT (surname, $$[[:alpha:]]$$, 1) >= 2        )));

ALTER TABLE


--
-- A name with only one alphabetical character now fails!
--

INSERT INTO t (surname, symbol) VALUES ('!!B!!', 'Only one alphabetical character');

ERROR:  new row for relation "t" violates check constraint "no_punct_no_digit_ck"
DETAIL:  Failing row contains (1, !!B!!, Only one alphabetical character).


--
-- Two letter surname - works!
--

INSERT INTO t (surname, symbol) VALUES ('Ng', 'Two letter surname');

INSERT 0 1


INSERT INTO t (surname, symbol) VALUES  -- see https://typographyforlawyers.com/straight-and-curly-quotes.html

-- Also, see https://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql/12320729#12320729
-- for dollar quoted strings - from Erwin Brandstetter.

($$O'Brien$$,           'Straight single quote'     ), 
($$O"Brien$$,           'Straight double quote'     ),
($$O‘Brien$$,           'Opening single quote'      ),
($$O’Brien$$,           'Closing single quote'      ),
($token$O“Brien$token$, 'Opening  double quote'     ),  -- note - $token$
($$O”Brien$$,           'Closing  double quote'     ),
($$O”Br-ie-n$$,         'Hyphens'                   ),  
($$O”Br   i e  n$$,     'Arbitrary different spaces'),
($$!O!Br!i!en!!$$,      'Exclamation mark possible!'),

--
-- Sample accents - impossible to test exhaustively
--
  
($$O'Bríen$$,           'Acute accent on i'         ),
($$O'Brièn$$,           'Grave accent on i'         ),
($$O'Briuç$$,           'Cedilla on c'              );


INSERT 0 12


SELECT * FROM t;


id
surname
symbol


2
Ng
Two letter surname

3
O'Brien
Straight single quote

4
O"Brien
Straight double quote

5
O‘Brien
Opening single quote

6
O’Brien
Closing single quote

7
O“Brien
Opening  double quote

8
O”Brien
Closing  double quote

9
O”Br-ie-n
Hyphens

10
O”Br   i e  n
Arbitrary different spaces

11
!O!Br!i!en!!
Exclamation mark possible!

12
O'Bríen
Acute accent on i

13
O'Brièn
Grave accent on i

14
O'Briuç
Cedilla on c



SELECT 13


--
-- Tilde (~) is the case sensitive regex match operator - matches mixed-case
-- here because the surname field is now CITEXT!
--

SELECT *
FROM t
WHERE surname ~ '^.*BrIe.*$'; 


id
surname
symbol


3
O'Brien
Straight single quote

4
O"Brien
Straight double quote

5
O‘Brien
Opening single quote

6
O’Brien
Closing single quote

7
O“Brien
Opening  double quote

8
O”Brien
Closing  double quote



SELECT 6


--
-- Tilde (~) is the case sensitive regex match operator - matches mixed-case
-- here because the surname field is now CITEXT!
--

SELECT *
FROM t
WHERE surname ~ '^.{0,}BrIe.{0,}$'; 


id
surname
symbol


3
O'Brien
Straight single quote

4
O"Brien
Straight double quote

5
O‘Brien
Opening single quote

6
O’Brien
Closing single quote

7
O“Brien
Opening  double quote

8
O”Brien
Closing  double quote



SELECT 6


--
-- Fails - see answer
--

ALTER TABLE t
ADD COLUMN
  surname_search CITEXT   -- also CITEXT!
    GENERATED ALWAYS AS 
    (
      REGEXP_REPLACE(UNACCENT(surname), '[^[:alpha:]]', '', 'g')  -- 'g' for global!
    ) STORED;  -- has to be STORED - no VIRTUAL, i.e. calculated on the fly, columns yet!

ERROR:  generation expression is not immutable


--
-- see here: https://stackoverflow.com/questions/11005036/does-postgresql-support-accent-insensitive-collations/11007216#11007216
--

CREATE OR REPLACE FUNCTION immutable_unaccent(regdictionary, text)
  RETURNS text
  LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS
'$libdir/unaccent', 'unaccent_dict';

CREATE FUNCTION


CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN immutable_unaccent(regdictionary 'unaccent', $1);

CREATE FUNCTION


ALTER TABLE t
ADD COLUMN
  surname_search CITEXT   -- also CITEXT!
    GENERATED ALWAYS AS 
    (
      REGEXP_REPLACE(F_UNACCENT(surname), '[^[:alpha:]]', '', 'g')  -- 'g' for global!
    ) STORED;  -- has to be STORED - no VIRTUAL, i.e. calculated on the fly, columns yet!

ALTER TABLE


--
-- Picks up the accented characters as well as the apostrophes of whatever sort, does
-- NOT pickup the ç (c cedilla) because surname = O'Briuç which doesn't match 'BRIE'!

SELECT *
FROM t
WHERE surname_search ~ '^.*BrIe.*$';


id
surname
symbol
surname_search


3
O'Brien
Straight single quote
OBrien

4
O"Brien
Straight double quote
OBrien

5
O‘Brien
Opening single quote
OBrien

6
O’Brien
Closing single quote
OBrien

7
O“Brien
Opening  double quote
OBrien

8
O”Brien
Closing  double quote
OBrien

9
O”Br-ie-n
Hyphens
OBrien

10
O”Br   i e  n
Arbitrary different spaces
OBrien

11
!O!Br!i!en!!
Exclamation mark possible!
OBrien

12
O'Bríen
Acute accent on i
OBrien

13
O'Brièn
Grave accent on i
OBrien



SELECT 11


SELECT 
  * 
FROM 
  t 
WHERE surname_search LIKE ('%' || 'bri' || '%')  --  any contiguous letters contained in
ORDER BY                                        -- surname - whether accented or not!
  id;                                            


id
surname
symbol
surname_search


3
O'Brien
Straight single quote
OBrien

4
O"Brien
Straight double quote
OBrien

5
O‘Brien
Opening single quote
OBrien

6
O’Brien
Closing single quote
OBrien

7
O“Brien
Opening  double quote
OBrien

8
O”Brien
Closing  double quote
OBrien

9
O”Br-ie-n
Hyphens
OBrien

10
O”Br   i e  n
Arbitrary different spaces
OBrien

11
!O!Br!i!en!!
Exclamation mark possible!
OBrien

12
O'Bríen
Acute accent on i
OBrien

13
O'Brièn
Grave accent on i
OBrien

14
O'Briuç
Cedilla on c
OBriuc



SELECT 12

id	surname	symbol
2	Ng	Two letter surname
3	O'Brien	Straight single quote
4	O"Brien	Straight double quote
5	O‘Brien	Opening single quote
6	O’Brien	Closing single quote
7	O“Brien	Opening double quote
8	O”Brien	Closing double quote
9	O”Br-ie-n	Hyphens
10	O”Br i e n	Arbitrary different spaces
11	!O!Br!i!en!!	Exclamation mark possible!
12	O'Bríen	Acute accent on i
13	O'Brièn	Grave accent on i
14	O'Briuç	Cedilla on c

id	surname	symbol	surname_search
3	O'Brien	Straight single quote	OBrien
4	O"Brien	Straight double quote	OBrien
5	O‘Brien	Opening single quote	OBrien
6	O’Brien	Closing single quote	OBrien
7	O“Brien	Opening double quote	OBrien
8	O”Brien	Closing double quote	OBrien
9	O”Br-ie-n	Hyphens	OBrien
10	O”Br i e n	Arbitrary different spaces	OBrien
11	!O!Br!i!en!!	Exclamation mark possible!	OBrien
12	O'Bríen	Acute accent on i	OBrien
13	O'Brièn	Grave accent on i	OBrien