\unset ECHO clickhouse_raw_query ---------------------- (1 row) clickhouse_raw_query ---------------------- (1 row) clickhouse_raw_query ---------------------- (1 row) clickhouse_raw_query ---------------------- (1 row) NOTICE: regexp_like PUSHED DOWN: t NOTICE: (val1) NOTICE: (val2) NOTICE: case-insensitive regexp_like PUSHED DOWN: t NOTICE: (val1) NOTICE: (val2) NOTICE: regexp_like with si PUSHED DOWN: t NOTICE: (val1) NOTICE: (val2) NOTICE: regexp_like with unsupported flag NOT PUSHED DOWN: t NOTICE: (val1) NOTICE: (val2) NOTICE: regexp_like with t flag PUSHED DOWN: t NOTICE: (val1) NOTICE: (val2) -- Check regexp_split_to_array. EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM strings WHERE regexp_split_to_array(val, ',') = '{a,b,c}'::text[]; QUERY PLAN ---------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: val Remote SQL: SELECT val FROM regex_test.strings WHERE ((splitByRegexp(',', val) = ['a','b','c'])) (3 rows) SELECT val FROM strings WHERE regexp_split_to_array(val, ',') = '{a,b,c}'::text[]; val ------- a,b,c (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM strings WHERE regexp_split_to_array(val, '\s+') = '{sleep,no,more}'::text[]; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: val Remote SQL: SELECT val FROM regex_test.strings WHERE ((splitByRegexp('\\s+', val) = ['sleep','no','more'])) (3 rows) SELECT val FROM strings WHERE regexp_split_to_array(val, '\s+') = '{sleep,no,more}'::text[]; val ------------------- sleep no more (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM strings WHERE regexp_split_to_array(val, '-t-', 'i') = '{aa,bb,cc}'::text[]; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: val Remote SQL: SELECT val FROM regex_test.strings WHERE ((splitByRegexp(concat('(?i)', '-t-'), val) = ['aa','bb','cc'])) (3 rows) SELECT val FROM strings WHERE regexp_split_to_array(val, '-t-', 'i') = '{aa,bb,cc}'::text[]; val -------------- aa-T-bb-t-cc (1 row) -- No pushdown when the regex is not a constant. EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM strings WHERE regexp_split_to_array(val, val) = '{}'::text[]; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.strings Output: val Filter: (regexp_split_to_array(strings.val, strings.val) = '{}'::text[]) Remote SQL: SELECT val FROM regex_test.strings (4 rows) -- Check regexp_replace(). EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, '[0,1]$', '_xyz') = 'val_xyz'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Remote SQL: SELECT id, val FROM regex_test.strings WHERE ((replaceRegexpOne(val, '[0,1]$', '_xyz') = 'val_xyz')) (3 rows) SELECT * FROM strings WHERE regexp_replace(val, '[0,1]$', '_xyz') = 'val_xyz'; id | val ----+------ 1 | val1 (1 row) -- No replace returns unmodified string. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, '^x', 'y') = 'val2'; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Remote SQL: SELECT id, val FROM regex_test.strings WHERE ((replaceRegexpOne(val, '^x', 'y') = 'val2')) (3 rows) SELECT * FROM strings WHERE regexp_replace(val, '^x', 'y') = 'val2'; id | val ----+------ 2 | val2 (1 row) -- Case-insensitive, refer to capture. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, 'VAL([0,1])$', 'x-\1', 'i') = 'x-1'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Remote SQL: SELECT id, val FROM regex_test.strings WHERE ((replaceRegexpOne(val, concat('(?i)', 'VAL([0,1])$'), 'x-\\1') = 'x-1')) (3 rows) SELECT * FROM strings WHERE regexp_replace(val, 'VAL([0,1])$', 'x-\1', 'i') = 'x-1'; id | val ----+------ 1 | val1 (1 row) -- Case-insensitive. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, '[VL]', 'x', 'i') = 'xal1'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Remote SQL: SELECT id, val FROM regex_test.strings WHERE ((replaceRegexpOne(val, concat('(?i)', '[VL]'), 'x') = 'xal1')) (3 rows) SELECT * FROM strings WHERE regexp_replace(val, '[VL]', 'x', 'i') = 'xal1'; id | val ----+------ 1 | val1 (1 row) -- Replace all case-insensitive. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, '[VL]', 'x', 'gig') = 'xax1'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Remote SQL: SELECT id, val FROM regex_test.strings WHERE ((replaceRegexpAll(val, concat('(?ii)', '[VL]'), 'x') = 'xax1')) (3 rows) SELECT * FROM strings WHERE regexp_replace(val, '[VL]', 'x', 'gig') = 'xax1'; id | val ----+------ 1 | val1 (1 row) -- Refer to full match. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, '^val', 'x-\0', 'i') = 'x-val1'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Remote SQL: SELECT id, val FROM regex_test.strings WHERE ((replaceRegexpOne(val, concat('(?i)', '^val'), 'x-\\0') = 'x-val1')) (3 rows) SELECT * FROM strings WHERE regexp_replace(val, '^val', 'x-\0', 'i') = 'x-val1'; id | val ----+------ 1 | val1 (1 row) -- No pushdown when the regex is not a constant. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, val, '') = ''; QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Filter: (regexp_replace(strings.val, strings.val, ''::text) = ''::text) Remote SQL: SELECT id, val FROM regex_test.strings (4 rows) -- Check regexp_match(). EXPLAIN (VERBOSE, COSTS OFF) SELECT regexp_match(val, '[01]$') FROM strings WHERE regexp_match(val, '[01]$') = '{1}'::text[]; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: regexp_match(val, '[01]$'::text) Remote SQL: SELECT val FROM regex_test.strings WHERE ((arraySlice(extractAll(val, '[01]$'), 1, 1) = ['1'])) (3 rows) SELECT regexp_match(val, '[01]$') FROM strings WHERE regexp_match(val, '[01]$') = '{1}'::text[]; regexp_match -------------- {1} (1 row) -- No match. EXPLAIN (VERBOSE, COSTS OFF) SELECT id, regexp_match(val, '[34]$') FROM strings WHERE regexp_match(val, '[34]$') = '{}'::text[]; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, regexp_match(val, '[34]$'::text) Remote SQL: SELECT id, val FROM regex_test.strings WHERE ((arraySlice(extractAll(val, '[34]$'), 1, 1) = [])) (3 rows) SELECT id, regexp_match(val, '[34]$') FROM strings WHERE regexp_match(val, '[34]$') = '{}'::text[]; id | regexp_match ----+-------------- 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | (8 rows) -- Multiple matches. EXPLAIN (VERBOSE, COSTS OFF) SELECT regexp_match(val, 'ba') FROM strings WHERE regexp_match(val, 'ba') = '{ba}'::text[]; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: regexp_match(val, 'ba'::text) Remote SQL: SELECT val FROM regex_test.strings WHERE ((arraySlice(extractAll(val, 'ba'), 1, 1) = ['ba'])) (3 rows) SELECT regexp_match(val, 'ba') FROM strings WHERE regexp_match(val, 'ba') = '{ba}'::text[]; regexp_match -------------- {ba} (1 row) -- Capturing groups. EXPLAIN (VERBOSE, COSTS OFF) SELECT regexp_match(val, '([a-zA-Z0-9]+)@') FROM strings WHERE regexp_match(val, '([a-zA-Z0-9]+)@') = '{test}'::text[]; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: regexp_match(val, '([a-zA-Z0-9]+)@'::text) Remote SQL: SELECT val FROM regex_test.strings WHERE ((extractGroups(val, '([a-zA-Z0-9]+)@') = ['test'])) (3 rows) SELECT regexp_match(val, '([a-zA-Z0-9]+)@') FROM strings WHERE regexp_match(val, '([a-zA-Z0-9]+)@') = '{test}'::text[]; regexp_match -------------- {test} (1 row) -- Case-insensitive. EXPLAIN (VERBOSE, COSTS OFF) SELECT regexp_match(val, 'VAL[01]$', 'i') FROM strings WHERE regexp_match(val, 'VAL[01]$', 'i') = '{val1}'::text[]; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.strings Output: regexp_match(val, 'VAL[01]$'::text, 'i'::text) Remote SQL: SELECT val FROM regex_test.strings WHERE ((arraySlice(extractAll(val, concat('(?i)', 'VAL[01]$')), 1, 1) = ['val1'])) (3 rows) SELECT regexp_match(val, 'VAL[01]$', 'i') FROM strings WHERE regexp_match(val, 'VAL[01]$', 'i') = '{val1}'::text[]; regexp_match -------------- {val1} (1 row) -- No pushdown when the regex is not a constant. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_match(val, val) = '{1}'::text[]; QUERY PLAN -------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Filter: (regexp_match(strings.val, strings.val) = '{1}'::text[]) Remote SQL: SELECT id, val FROM regex_test.strings (4 rows) -- Compare consistency of `.` matching a newline. SELECT regexp_match(val, 'line.') FROM strings WHERE regexp_match(val, 'line.') = E'{"line\n"}'::text[]; regexp_match -------------- {"line + "} (1 row) SELECT regexp_match(val, 'line.', 's') FROM strings WHERE regexp_match(val, 'line.', 's') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) SELECT regexp_match(val, 'line.', 'm'), id FROM strings WHERE regexp_match(val, 'line.', 'm') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, 'line.', 'n'), id FROM strings WHERE regexp_match(val, 'line.', 'n') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, 'line.', 'p'), id FROM strings WHERE regexp_match(val, 'line.', 'p') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, 'line.', 'w') FROM strings WHERE regexp_match(val, 'line.', 'w') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) -- Last flag overrides. SELECT regexp_match(val, 'line.', 'ms') FROM strings WHERE regexp_match(val, 'line.', 'ms') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) SELECT regexp_match(val, 'line.', 'sm'), id FROM strings WHERE regexp_match(val, 'line.', 'sm') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, 'line.', 'mspw') FROM strings WHERE regexp_match(val, 'line.', 'mspw') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) -- Compare consistency of `[^x]` matching a newline. SELECT regexp_match(val, 'line[^x]') FROM strings WHERE regexp_match(val, 'line[^x]') = E'{"line\n"}'::text[]; regexp_match -------------- {"line + "} (1 row) SELECT regexp_match(val, 'line[^x]', 's') FROM strings WHERE regexp_match(val, 'line[^x]', 's') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) -- m, n match in CH but not PG SELECT regexp_match(val, 'line[^x]', 'm') FROM strings WHERE regexp_match(val, 'line[^x]', 'm') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) SELECT regexp_match(val, 'line[^x]', 'n') FROM strings WHERE regexp_match(val, 'line[^x]', 'n') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) SELECT regexp_match(val, 'line[^x]', 'p') FROM strings WHERE regexp_match(val, 'line[^x]', 'p') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) -- w matches in both. SELECT regexp_match(val, 'line[^x]', 'w') FROM strings WHERE regexp_match(val, 'line[^x]', 'w') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) -- Last flag overrides. SELECT regexp_match(val, 'line[^x]', 'msw') FROM strings WHERE regexp_match(val, 'line[^x]', 'msw') = E'{"line\n"}'::text[]; regexp_match -------------- (0 rows) -- Compare consistency of `$` end of line. SELECT regexp_match(val, 'line$'), id FROM strings WHERE regexp_match(val, 'line$') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, 'line$', 'm') FROM strings WHERE regexp_match(val, 'line$', 'm') = E'{line}'::text[]; regexp_match -------------- (0 rows) SELECT regexp_match(val, 'line$', 'n') FROM strings WHERE regexp_match(val, 'line$', 'n') = E'{line}'::text[]; regexp_match -------------- (0 rows) SELECT regexp_match(val, 'line$', 's'), id FROM strings WHERE regexp_match(val, 'line$', 's') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) -- p matches in CH but not PG. SELECT regexp_match(val, 'line$', 'p'), id FROM strings WHERE regexp_match(val, 'line$', 'p') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) -- w matches in both. SELECT regexp_match(val, 'line$', 'w') FROM strings WHERE regexp_match(val, 'line$', 'w') = E'{line}'::text[]; regexp_match -------------- (0 rows) -- Last flag overrides. SELECT regexp_match(val, 'line$', 'ms'), id FROM strings WHERE regexp_match(val, 'line$', 'ms') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, 'line$', 'sm') FROM strings WHERE regexp_match(val, 'line$', 'sm') = E'{line}'::text[]; regexp_match -------------- (0 rows) SELECT regexp_match(val, 'line$', 'smw') FROM strings WHERE regexp_match(val, 'line$', 'smw') = E'{line}'::text[]; regexp_match -------------- (0 rows) -- Compare consistency of `^` start of line. SELECT regexp_match(val, '^target'), id FROM strings WHERE regexp_match(val, '^target') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, '^target', 'm') FROM strings WHERE regexp_match(val, '^target', 'm') = E'{target}'::text[]; regexp_match -------------- {target} (1 row) SELECT regexp_match(val, '^target', 'n') FROM strings WHERE regexp_match(val, '^target', 'n') = E'{target}'::text[]; regexp_match -------------- {target} (1 row) SELECT regexp_match(val, '^target', 's'), id FROM strings WHERE regexp_match(val, '^target', 's') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) -- p matches in CH but not PG. SELECT regexp_match(val, '^target', 'p'), id FROM strings WHERE regexp_match(val, '^target', 'p') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) -- w matches in both. SELECT regexp_match(val, '^target', 'w') FROM strings WHERE regexp_match(val, '^target', 'w') = E'{target}'::text[]; regexp_match -------------- {target} (1 row) -- Last flag overrides. SELECT regexp_match(val, '^target', 'ms'), id FROM strings WHERE regexp_match(val, '^target', 'ms') = '{}'::text[] ORDER BY id; regexp_match | id --------------+---- | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 (8 rows) SELECT regexp_match(val, '^target', 'sm') FROM strings WHERE regexp_match(val, '^target', 'sm') = E'{target}'::text[]; regexp_match -------------- {target} (1 row) SELECT regexp_match(val, '^target', 'msw') FROM strings WHERE regexp_match(val, '^target', 'msw') = E'{target}'::text[]; regexp_match -------------- {target} (1 row) -- Ensure no pushdown when we disable it. SET pg_clickhouse.pushdown_regex = 'false'; EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM strings WHERE regexp_split_to_array(val, ',') = '{a,b,c}'::text[]; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on public.strings Output: val Filter: (regexp_split_to_array(strings.val, ','::text) = '{a,b,c}'::text[]) Remote SQL: SELECT val FROM regex_test.strings (4 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_replace(val, '^x', 'y') = 'val2'; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on public.strings Output: id, val Filter: (regexp_replace(strings.val, '^x'::text, 'y'::text) = 'val2'::text) Remote SQL: SELECT id, val FROM regex_test.strings (4 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM strings WHERE regexp_match(val, '.') = '{1}'::text[]; QUERY PLAN ------------------------------------------------------------------ Foreign Scan on public.strings Output: id, val Filter: (regexp_match(strings.val, '.'::text) = '{1}'::text[]) Remote SQL: SELECT id, val FROM regex_test.strings (4 rows) \unset ECHO NOTICE: regexp_like: SELECT val FROM regex_test.strings DROP USER MAPPING FOR CURRENT_USER SERVER regex_loopback; SELECT clickhouse_raw_query('DROP DATABASE regex_test'); clickhouse_raw_query ---------------------- (1 row) DROP SERVER regex_loopback CASCADE; NOTICE: drop cascades to foreign table strings