clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40633 in the last week).

SELECT version();
version
PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
 hidden batch(es)


CREATE TABLE parent ( x INTEGER PRIMARY KEY, -- presumably IDENTITY or SERIAL... y TEXT NOT NULL -- other fields -- );
 hidden batch(es)


INSERT INTO parent SELECT s, s::TEXT || ' PK text' FROM GENERATE_SERIES(1, 100) AS r(s);
100 rows affected
 hidden batch(es)


SELECT * FROM parent;
x y
1 1 PK text
2 2 PK text
3 3 PK text
4 4 PK text
5 5 PK text
6 6 PK text
7 7 PK text
8 8 PK text
9 9 PK text
10 10 PK text
11 11 PK text
12 12 PK text
13 13 PK text
14 14 PK text
15 15 PK text
16 16 PK text
17 17 PK text
18 18 PK text
19 19 PK text
20 20 PK text
21 21 PK text
22 22 PK text
23 23 PK text
24 24 PK text
25 25 PK text
26 26 PK text
27 27 PK text
28 28 PK text
29 29 PK text
30 30 PK text
31 31 PK text
32 32 PK text
33 33 PK text
34 34 PK text
35 35 PK text
36 36 PK text
37 37 PK text
38 38 PK text
39 39 PK text
40 40 PK text
41 41 PK text
42 42 PK text
43 43 PK text
44 44 PK text
45 45 PK text
46 46 PK text
47 47 PK text
48 48 PK text
49 49 PK text
50 50 PK text
51 51 PK text
52 52 PK text
53 53 PK text
54 54 PK text
55 55 PK text
56 56 PK text
57 57 PK text
58 58 PK text
59 59 PK text
60 60 PK text
61 61 PK text
62 62 PK text
63 63 PK text
64 64 PK text
65 65 PK text
66 66 PK text
67 67 PK text
68 68 PK text
69 69 PK text
70 70 PK text
71 71 PK text
72 72 PK text
73 73 PK text
74 74 PK text
75 75 PK text
76 76 PK text
77 77 PK text
78 78 PK text
79 79 PK text
80 80 PK text
81 81 PK text
82 82 PK text
83 83 PK text
84 84 PK text
85 85 PK text
86 86 PK text
87 87 PK text
88 88 PK text
89 89 PK text
90 90 PK text
91 91 PK text
92 92 PK text
93 93 PK text
94 94 PK text
95 95 PK text
96 96 PK text
97 97 PK text
98 98 PK text
99 99 PK text
100 100 PK text
 hidden batch(es)


CREATE TABLE child ( b TEXT, -- -- other fields t TEXT );
 hidden batch(es)


SELECT n.t, n.t::TEXT || ' text part', GENERATE_SERIES (1, 10) FROM GENERATE_SERIES(1, 5) AS n(t);
t ?column? generate_series
1 1 text part 1
1 1 text part 2
1 1 text part 3
1 1 text part 4
1 1 text part 5
1 1 text part 6
1 1 text part 7
1 1 text part 8
1 1 text part 9
1 1 text part 10
2 2 text part 1
2 2 text part 2
2 2 text part 3
2 2 text part 4
2 2 text part 5
2 2 text part 6
2 2 text part 7
2 2 text part 8
2 2 text part 9
2 2 text part 10
3 3 text part 1
3 3 text part 2
3 3 text part 3
3 3 text part 4
3 3 text part 5
3 3 text part 6
3 3 text part 7
3 3 text part 8
3 3 text part 9
3 3 text part 10
4 4 text part 1
4 4 text part 2
4 4 text part 3
4 4 text part 4
4 4 text part 5
4 4 text part 6
4 4 text part 7
4 4 text part 8
4 4 text part 9
4 4 text part 10
5 5 text part 1
5 5 text part 2
5 5 text part 3
5 5 text part 4
5 5 text part 5
5 5 text part 6
5 5 text part 7
5 5 text part 8
5 5 text part 9
5 5 text part 10
 hidden batch(es)


SELECT GENERATE_SERIES (1, 10) AS m, n.t, n.t::TEXT || ' text part!' FROM GENERATE_SERIES(1, 5) AS n(t);
m t ?column?
1 1 1 text part!
2 1 1 text part!
3 1 1 text part!
4 1 1 text part!
5 1 1 text part!
6 1 1 text part!
7 1 1 text part!
8 1 1 text part!
9 1 1 text part!
10 1 1 text part!
1 2 2 text part!
2 2 2 text part!
3 2 2 text part!
4 2 2 text part!
5 2 2 text part!
6 2 2 text part!
7 2 2 text part!
8 2 2 text part!
9 2 2 text part!
10 2 2 text part!
1 3 3 text part!
2 3 3 text part!
3 3 3 text part!
4 3 3 text part!
5 3 3 text part!
6 3 3 text part!
7 3 3 text part!
8 3 3 text part!
9 3 3 text part!
10 3 3 text part!
1 4 4 text part!
2 4 4 text part!
3 4 4 text part!
4 4 4 text part!
5 4 4 text part!
6 4 4 text part!
7 4 4 text part!
8 4 4 text part!
9 4 4 text part!
10 4 4 text part!
1 5 5 text part!
2 5 5 text part!
3 5 5 text part!
4 5 5 text part!
5 5 5 text part!
6 5 5 text part!
7 5 5 text part!
8 5 5 text part!
9 5 5 text part!
10 5 5 text part!
 hidden batch(es)


INSERT INTO child SELECT GENERATE_SERIES (1, 100) AS m, n.t::TEXT || ' text part!' FROM GENERATE_SERIES(1, 5) AS n(t);
500 rows affected
 hidden batch(es)


SELECT * FROM child;
b t
1 1 text part!
2 1 text part!
3 1 text part!
4 1 text part!
5 1 text part!
6 1 text part!
7 1 text part!
8 1 text part!
9 1 text part!
10 1 text part!
11 1 text part!
12 1 text part!
13 1 text part!
14 1 text part!
15 1 text part!
16 1 text part!
17 1 text part!
18 1 text part!
19 1 text part!
20 1 text part!
21 1 text part!
22 1 text part!
23 1 text part!
24 1 text part!
25 1 text part!
26 1 text part!
27 1 text part!
28 1 text part!
29 1 text part!
30 1 text part!
31 1 text part!
32 1 text part!
33 1 text part!
34 1 text part!
35 1 text part!
36 1 text part!
37 1 text part!
38 1 text part!
39 1 text part!
40 1 text part!
41 1 text part!
42 1 text part!
43 1 text part!
44 1 text part!
45 1 text part!
46 1 text part!
47 1 text part!
48 1 text part!
49 1 text part!
50 1 text part!
51 1 text part!
52 1 text part!
53 1 text part!
54 1 text part!
55 1 text part!
56 1 text part!
57 1 text part!
58 1 text part!
59 1 text part!
60 1 text part!
61 1 text part!
62 1 text part!
63 1 text part!
64 1 text part!
65 1 text part!
66 1 text part!
67 1 text part!
68 1 text part!
69 1 text part!
70 1 text part!
71 1 text part!
72 1 text part!
73 1 text part!
74 1 text part!
75 1 text part!
76 1 text part!
77 1 text part!
78 1 text part!
79 1 text part!
80 1 text part!
81 1 text part!
82 1 text part!
83 1 text part!
84 1 text part!
85 1 text part!
86 1 text part!
87 1 text part!
88 1 text part!
89 1 text part!
90 1 text part!
91 1 text part!
92 1 text part!
93 1 text part!
94 1 text part!
95 1 text part!
96 1 text part!
97 1 text part!
98 1 text part!
99 1 text part!
100 1 text part!
1 2 text part!
2 2 text part!
3 2 text part!
4 2 text part!
5 2 text part!
6 2 text part!
7 2 text part!
8 2 text part!
9 2 text part!
10 2 text part!
11 2 text part!
12 2 text part!
13 2 text part!
14 2 text part!
15 2 text part!
16 2 text part!
17 2 text part!
18 2 text part!
19 2 text part!
20 2 text part!
21 2 text part!
22 2 text part!
23 2 text part!
24 2 text part!
25 2 text part!
26 2 text part!
27 2 text part!
28 2 text part!
29 2 text part!
30 2 text part!
31 2 text part!
32 2 text part!
33 2 text part!
34 2 text part!
35 2 text part!
36 2 text part!
37 2 text part!
38 2 text part!
39 2 text part!
40 2 text part!
41 2 text part!
42 2 text part!
43 2 text part!
44 2 text part!
45 2 text part!
46 2 text part!
47 2 text part!
48 2 text part!
49 2 text part!
50 2 text part!
51 2 text part!
52 2 text part!
53 2 text part!
54 2 text part!
55 2 text part!
56 2 text part!
57 2 text part!
58 2 text part!
59 2 text part!
60 2 text part!
61 2 text part!
62 2 text part!
63 2 text part!
64 2 text part!
65 2 text part!
66 2 text part!
67 2 text part!
68 2 text part!
69 2 text part!
70 2 text part!
71 2 text part!
72 2 text part!
73 2 text part!
74 2 text part!
75 2 text part!
76 2 text part!
77 2 text part!
78 2 text part!
79 2 text part!
80 2 text part!
81 2 text part!
82 2 text part!
83 2 text part!
84 2 text part!
85 2 text part!
86 2 text part!
87 2 text part!
88 2 text part!
89 2 text part!
90 2 text part!
91 2 text part!
92 2 text part!
93 2 text part!
94 2 text part!
95 2 text part!
96 2 text part!
97 2 text part!
98 2 text part!
99 2 text part!
100 2 text part!
1 3 text part!
2 3 text part!
3 3 text part!
4 3 text part!
5 3 text part!
6 3 text part!
7 3 text part!
8 3 text part!
9 3 text part!
10 3 text part!
11 3 text part!
12 3 text part!
13 3 text part!
14 3 text part!
15 3 text part!
16 3 text part!
17 3 text part!
18 3 text part!
19 3 text part!
20 3 text part!
21 3 text part!
22 3 text part!
23 3 text part!
24 3 text part!
25 3 text part!
26 3 text part!
27 3 text part!
28 3 text part!
29 3 text part!
30 3 text part!
31 3 text part!
32 3 text part!
33 3 text part!
34 3 text part!
35 3 text part!
36 3 text part!
37 3 text part!
38 3 text part!
39 3 text part!
40 3 text part!
41 3 text part!
42 3 text part!
43 3 text part!
44 3 text part!
45 3 text part!
46 3 text part!
47 3 text part!
48 3 text part!
49 3 text part!
50 3 text part!
51 3 text part!
52 3 text part!
53 3 text part!
54 3 text part!
55 3 text part!
56 3 text part!
57 3 text part!
58 3 text part!
59 3 text part!
60 3 text part!
61 3 text part!
62 3 text part!
63 3 text part!
64 3 text part!
65 3 text part!
66 3 text part!
67 3 text part!
68 3 text part!
69 3 text part!
70 3 text part!
71 3 text part!
72 3 text part!
73 3 text part!
74 3 text part!
75 3 text part!
76 3 text part!
77 3 text part!
78 3 text part!
79 3 text part!
80 3 text part!
81 3 text part!
82 3 text part!
83 3 text part!
84 3 text part!
85 3 text part!
86 3 text part!
87 3 text part!
88 3 text part!
89 3 text part!
90 3 text part!
91 3 text part!
92 3 text part!
93 3 text part!
94 3 text part!
95 3 text part!
96 3 text part!
97 3 text part!
98 3 text part!
99 3 text part!
100 3 text part!
1 4 text part!
2 4 text part!
3 4 text part!
4 4 text part!
5 4 text part!
6 4 text part!
7 4 text part!
8 4 text part!
9 4 text part!
10 4 text part!
11 4 text part!
12 4 text part!
13 4 text part!
14 4 text part!
15 4 text part!
16 4 text part!
17 4 text part!
18 4 text part!
19 4 text part!
20 4 text part!
21 4 text part!
22 4 text part!
23 4 text part!
24 4 text part!
25 4 text part!
26 4 text part!
27 4 text part!
28 4 text part!
29 4 text part!
30 4 text part!
31 4 text part!
32 4 text part!
33 4 text part!
34 4 text part!
35 4 text part!
36 4 text part!
37 4 text part!
38 4 text part!
39 4 text part!
40 4 text part!
41 4 text part!
42 4 text part!
43 4 text part!
44 4 text part!
45 4 text part!
46 4 text part!
47 4 text part!
48 4 text part!
49 4 text part!
50 4 text part!
51 4 text part!
52 4 text part!
53 4 text part!
54 4 text part!
55 4 text part!
56 4 text part!
57 4 text part!
58 4 text part!
59 4 text part!
60 4 text part!
61 4 text part!
62 4 text part!
63 4 text part!
64 4 text part!
65 4 text part!
66 4 text part!
67 4 text part!
68 4 text part!
69 4 text part!
70 4 text part!
71 4 text part!
72 4 text part!
73 4 text part!
74 4 text part!
75 4 text part!
76 4 text part!
77 4 text part!
78 4 text part!
79 4 text part!
80 4 text part!
81 4 text part!
82 4 text part!
83 4 text part!
84 4 text part!
85 4 text part!
86 4 text part!
87 4 text part!
88 4 text part!
89 4 text part!
90 4 text part!
91 4 text part!
92 4 text part!
93 4 text part!
94 4 text part!
95 4 text part!
96 4 text part!
97 4 text part!
98 4 text part!
99 4 text part!
100 4 text part!
1 5 text part!
2 5 text part!
3 5 text part!
4 5 text part!
5 5 text part!
6 5 text part!
7 5 text part!
8 5 text part!
9 5 text part!
10 5 text part!
11 5 text part!
12 5 text part!
13 5 text part!
14 5 text part!
15 5 text part!
16 5 text part!
17 5 text part!
18 5 text part!
19 5 text part!
20 5 text part!
21 5 text part!
22 5 text part!
23 5 text part!
24 5 text part!
25 5 text part!
26 5 text part!
27 5 text part!
28 5 text part!
29 5 text part!
30 5 text part!
31 5 text part!
32 5 text part!
33 5 text part!
34 5 text part!
35 5 text part!
36 5 text part!
37 5 text part!
38 5 text part!
39 5 text part!
40 5 text part!
41 5 text part!
42 5 text part!
43 5 text part!
44 5 text part!
45 5 text part!
46 5 text part!
47 5 text part!
48 5 text part!
49 5 text part!
50 5 text part!
51 5 text part!
52 5 text part!
53 5 text part!
54 5 text part!
55 5 text part!
56 5 text part!
57 5 text part!
58 5 text part!
59 5 text part!
60 5 text part!
61 5 text part!
62 5 text part!
63 5 text part!
64 5 text part!
65 5 text part!
66 5 text part!
67 5 text part!
68 5 text part!
69 5 text part!
70 5 text part!
71 5 text part!
72 5 text part!
73 5 text part!
74 5 text part!
75 5 text part!
76 5 text part!
77 5 text part!
78 5 text part!
79 5 text part!
80 5 text part!
81 5 text part!
82 5 text part!
83 5 text part!
84 5 text part!
85 5 text part!
86 5 text part!
87 5 text part!
88 5 text part!
89 5 text part!
90 5 text part!
91 5 text part!
92 5 text part!
93 5 text part!
94 5 text part!
95 5 text part!
96 5 text part!
97 5 text part!
98 5 text part!
99 5 text part!
100 5 text part!
 hidden batch(es)


SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::int WHERE pa.x = 23;
x y b t
23 23 PK text 23 1 text part!
23 23 PK text 23 2 text part!
23 23 PK text 23 3 text part!
23 23 PK text 23 4 text part!
23 23 PK text 23 5 text part!
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE pa.x = 23;
QUERY PLAN
Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1)
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (x = 23)
-> Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1)
Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23)
Rows Removed by Filter: 495
Planning Time: 0.094 ms
Execution Time: 0.203 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE REPLACE(ch.b, 'Ref:', '')::INT = 23;
QUERY PLAN
Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1)
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (x = 23)
-> Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1)
Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23)
Rows Removed by Filter: 495
Planning Time: 0.095 ms
Execution Time: 0.176 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE pa.x = 23;
QUERY PLAN
Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1)
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (x = 23)
-> Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1)
Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23)
Rows Removed by Filter: 495
Planning Time: 0.089 ms
Execution Time: 0.242 ms
 hidden batch(es)


-- -- Why so many brackets? -- CREATE INDEX ch_b_replace_ix_bis ON child (((REPLACE(b, 'Ref:', ''))::INT));
 hidden batch(es)


-- -- Fails! -- -- CREATE INDEX ch_b_replace_ix_ter ON child ((REPLACE(b, 'Ref:', ''))::INT)
 hidden batch(es)


-- -- Works! -- -- CREATE INDEX ch_b_replace_ix ON child (CAST(REPLACE(b, 'Ref:', '') AS INT));
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE pa.x = 23;
QUERY PLAN
Nested Loop (cost=4.44..15.62 rows=2 width=100) (actual rows=5 loops=1)
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (x = 23)
-> Bitmap Heap Scan on child ch (cost=4.29..7.43 rows=2 width=64) (actual rows=5 loops=1)
Recheck Cond: ((replace(b, 'Ref:'::text, ''::text))::integer = 23)
Heap Blocks: exact=3
-> Bitmap Index Scan on ch_b_replace_ix_bis (cost=0.00..4.29 rows=2 width=0) (actual rows=5 loops=1)
Index Cond: ((replace(b, 'Ref:'::text, ''::text))::integer = 23)
Planning Time: 0.202 ms
Execution Time: 0.133 ms
 hidden batch(es)