1-- 2-- NAME 3-- all inputs are silently truncated at NAMEDATALEN-1 (63) characters 4-- 5-- fixed-length by reference 6SELECT name 'name string' = name 'name string' AS "True"; 7 True 8------ 9 t 10(1 row) 11 12SELECT name 'name string' = name 'name string ' AS "False"; 13 False 14------- 15 f 16(1 row) 17 18-- 19-- 20-- 21CREATE TABLE NAME_TBL(f1 name); 22INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'); 23INSERT INTO NAME_TBL(f1) VALUES ('1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqr'); 24INSERT INTO NAME_TBL(f1) VALUES ('asdfghjkl;'); 25INSERT INTO NAME_TBL(f1) VALUES ('343f%2a'); 26INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf'); 27INSERT INTO NAME_TBL(f1) VALUES (''); 28INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'); 29SELECT '' AS seven, * FROM NAME_TBL; 30 seven | f1 31-------+----------------------------------------------------------------- 32 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 33 | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq 34 | asdfghjkl; 35 | 343f%2a 36 | d34aaasdf 37 | 38 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 39(7 rows) 40 41SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; 42 six | f1 43-----+----------------------------------------------------------------- 44 | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq 45 | asdfghjkl; 46 | 343f%2a 47 | d34aaasdf 48 | 49(5 rows) 50 51SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; 52 one | f1 53-----+----------------------------------------------------------------- 54 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 55 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 56(2 rows) 57 58SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; 59 three | f1 60-------+---- 61 | 62(1 row) 63 64SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; 65 four | f1 66------+----------------------------------------------------------------- 67 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 68 | 69 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 70(3 rows) 71 72SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; 73 three | f1 74-------+----------------------------------------------------------------- 75 | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq 76 | asdfghjkl; 77 | 343f%2a 78 | d34aaasdf 79(4 rows) 80 81SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; 82 four | f1 83------+----------------------------------------------------------------- 84 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 85 | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq 86 | asdfghjkl; 87 | 343f%2a 88 | d34aaasdf 89 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 90(6 rows) 91 92SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*'; 93 seven | f1 94-------+----------------------------------------------------------------- 95 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 96 | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq 97 | asdfghjkl; 98 | 343f%2a 99 | d34aaasdf 100 | 101 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 102(7 rows) 103 104SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*'; 105 zero | f1 106------+---- 107(0 rows) 108 109SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]'; 110 three | f1 111-------+----------------------------------------------------------------- 112 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 113 | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq 114 | 343f%2a 115 | d34aaasdf 116 | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ 117(5 rows) 118 119SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*'; 120 two | f1 121-----+------------ 122 | asdfghjkl; 123 | d34aaasdf 124(2 rows) 125 126DROP TABLE NAME_TBL; 127DO $$ 128DECLARE r text[]; 129BEGIN 130 r := parse_ident('Schemax.Tabley'); 131 RAISE NOTICE '%', format('%I.%I', r[1], r[2]); 132 r := parse_ident('"SchemaX"."TableY"'); 133 RAISE NOTICE '%', format('%I.%I', r[1], r[2]); 134END; 135$$; 136NOTICE: schemax.tabley 137NOTICE: "SchemaX"."TableY" 138SELECT parse_ident('foo.boo'); 139 parse_ident 140------------- 141 {foo,boo} 142(1 row) 143 144SELECT parse_ident('foo.boo[]'); -- should fail 145ERROR: string is not a valid identifier: "foo.boo[]" 146SELECT parse_ident('foo.boo[]', strict => false); -- ok 147 parse_ident 148------------- 149 {foo,boo} 150(1 row) 151 152-- should fail 153SELECT parse_ident(' '); 154ERROR: string is not a valid identifier: " " 155SELECT parse_ident(' .aaa'); 156ERROR: string is not a valid identifier: " .aaa" 157DETAIL: No valid identifier before ".". 158SELECT parse_ident(' aaa . '); 159ERROR: string is not a valid identifier: " aaa . " 160DETAIL: No valid identifier after ".". 161SELECT parse_ident('aaa.a%b'); 162ERROR: string is not a valid identifier: "aaa.a%b" 163SELECT parse_ident(E'X\rXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'); 164ERROR: string is not a valid identifier: "X 165XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" 166SELECT length(a[1]), length(a[2]) from parse_ident('"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx".yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy') as a ; 167 length | length 168--------+-------- 169 414 | 289 170(1 row) 171 172SELECT parse_ident(' first . " second " ." third ". " ' || repeat('x',66) || '"'); 173 parse_ident 174----------------------------------------------------------------------------------------------------------- 175 {first," second "," third "," xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"} 176(1 row) 177 178SELECT parse_ident(' first . " second " ." third ". " ' || repeat('x',66) || '"')::name[]; 179 parse_ident 180------------------------------------------------------------------------------------------------------ 181 {first," second "," third "," xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"} 182(1 row) 183 184SELECT parse_ident(E'"c".X XXXX\002XXXXXX'); 185ERROR: string is not a valid identifier: ""c".X XXXXXXXXXX" 186SELECT parse_ident('1020'); 187ERROR: string is not a valid identifier: "1020" 188SELECT parse_ident('10.20'); 189ERROR: string is not a valid identifier: "10.20" 190SELECT parse_ident('.'); 191ERROR: string is not a valid identifier: "." 192DETAIL: No valid identifier before ".". 193SELECT parse_ident('.1020'); 194ERROR: string is not a valid identifier: ".1020" 195DETAIL: No valid identifier before ".". 196SELECT parse_ident('xxx.1020'); 197ERROR: string is not a valid identifier: "xxx.1020" 198DETAIL: No valid identifier after ".". 199