Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

CREATE TABLE s
(
  num TEXT,
  
  new_num INTEGER GENERATED ALWAYS AS
    (NULLIF(TRANSLATE(num, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ. ', ''), '')::INTEGER) STORED
);

CREATE TABLE

CREATE INDEX ON s ((NULLIF(TRANSLATE(num, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ. ', ''), '')::INTEGER));

CREATE INDEX

CREATE TABLE t 
(
  num TEXT,
  
  new_num INTEGER GENERATED ALWAYS AS
    (NULLIF(regexp_replace(num, '\D','','g'), '')::INTEGER) STORED
);

CREATE TABLE

CREATE INDEX ON t ((NULLIF(regexp_replace(num, '\D','','g'), '')::INTEGER));

CREATE INDEX

INSERT INTO s VALUES ('2'), (''), (NULL), (' ');
INSERT INTO t VALUES ('2'), (''), (NULL), (' ');
SELECT * FROM s;
SELECT * FROM t;

INSERT 0 4

INSERT 0 4

num
new_num

2
2

null

null
null

null

SELECT 4

num
new_num

2
2

null

null
null

null

SELECT 4

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
INSERT INTO t 
with symbols(characters) as 
(
  VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')
)
select string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), '')
from symbols
join generate_series(1,10) as word(chr_idx) on 1 = 1 -- word length
join generate_series(1,10000) as words(idx) on 1 = 1 -- # of words
group by idx;

QUERY PLAN

Insert on public.t  (cost=3750.11..3754.61 rows=200 width=36) (actual time=267.606..267.608 rows=0 loops=1)

  Buffers: shared hit=29637 read=2 dirtied=77 written=77, temp read=281 written=632

  ->  Subquery Scan on "*SELECT*"  (cost=3750.11..3754.61 rows=200 width=36) (actual time=67.904..119.536 rows=10000 loops=1)

        Output: "*SELECT*".string_agg, NULL::integer

        Buffers: temp read=281 written=632

        ->  HashAggregate  (cost=3750.11..3752.61 rows=200 width=36) (actual time=67.903..117.699 rows=10000 loops=1)

              Output: string_agg(substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'::text, (((random() * '36'::double precision) + '1'::double precision))::integer, 1), ''::text), words.idx

              Group Key: words.idx

              Batches: 5  Memory Usage: 2953kB  Disk Usage: 3432kB

              Buffers: temp read=281 written=632

              ->  Nested Loop  (cost=0.01..2000.11 rows=100000 width=4) (actual time=1.261..18.299 rows=100000 loops=1)

                    Output: words.idx

                    ->  Function Scan on pg_catalog.generate_series word  (cost=0.00..0.10 rows=10 width=0) (actual time=0.014..0.016 rows=10 loops=1)

                          Output: word.chr_idx

                          Function Call: generate_series(1, 10)

                    ->  Function Scan on pg_catalog.generate_series words  (cost=0.00..100.00 rows=10000 width=4) (actual time=0.124..0.773 rows=10000 loops=10)

                          Output: words.idx

                          Function Call: generate_series(1, 10000)

Planning:

  Buffers: shared hit=17 read=6

Planning Time: 2.685 ms

Execution Time: 268.779 ms

EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
INSERT INTO s
with symbols(characters) as 
(
  VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')
)
select string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), '')
from symbols
join generate_series(1,10) as word(chr_idx) on 1 = 1 -- word length
join generate_series(1,10000) as words(idx) on 1 = 1 -- # of words
group by idx

QUERY PLAN

Insert on public.s  (cost=3750.11..3754.61 rows=200 width=36) (actual time=200.829..200.832 rows=0 loops=1)

  Buffers: shared hit=29661 read=2 dirtied=78 written=78, temp read=281 written=632

  ->  Subquery Scan on "*SELECT*"  (cost=3750.11..3754.61 rows=200 width=36) (actual time=63.874..115.495 rows=10000 loops=1)

        Output: "*SELECT*".string_agg, NULL::integer

        Buffers: temp read=281 written=632

        ->  HashAggregate  (cost=3750.11..3752.61 rows=200 width=36) (actual time=63.873..112.913 rows=10000 loops=1)

              Output: string_agg(substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'::text, (((random() * '36'::double precision) + '1'::double precision))::integer, 1), ''::text), words.idx

              Group Key: words.idx

              Batches: 5  Memory Usage: 2953kB  Disk Usage: 3432kB

              Buffers: temp read=281 written=632

              ->  Nested Loop  (cost=0.01..2000.11 rows=100000 width=4) (actual time=0.555..17.283 rows=100000 loops=1)

                    Output: words.idx

                    ->  Function Scan on pg_catalog.generate_series word  (cost=0.00..0.10 rows=10 width=0) (actual time=0.007..0.009 rows=10 loops=1)

                          Output: word.chr_idx

                          Function Call: generate_series(1, 10)

                    ->  Function Scan on pg_catalog.generate_series words  (cost=0.00..100.00 rows=10000 width=4) (actual time=0.055..0.685 rows=10000 loops=10)

                          Output: words.idx

                          Function Call: generate_series(1, 10000)

Planning Time: 0.155 ms

Execution Time: 201.519 ms

EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  NULLIF(TRANSLATE(num, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ. ', ''), '')::INTEGER
FROM s;

QUERY PLAN

Seq Scan on public.s  (cost=0.00..194.70 rows=6985 width=4) (actual time=0.025..13.154 rows=10004 loops=1)

  Output: (NULLIF(translate(num, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ. '::text, ''::text), ''::text))::integer

  Buffers: shared hit=55

Planning Time: 0.056 ms

Execution Time: 13.589 ms

EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT 
  NULLIF(regexp_replace(num, '\D', '', 'g'), '')::INTEGER AS result 
FROM t;

QUERY PLAN

Seq Scan on public.t  (cost=0.00..194.70 rows=6985 width=4) (actual time=0.021..44.429 rows=10004 loops=1)

  Output: (NULLIF(regexp_replace(num, '\D'::text, ''::text, 'g'::text), ''::text))::integer

  Buffers: shared hit=55

Planning Time: 0.027 ms

Execution Time: 44.924 ms

EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  num, new_num
FROM s;

QUERY PLAN

Seq Scan on public.s  (cost=0.00..124.85 rows=6985 width=36) (actual time=0.009..0.744 rows=10004 loops=1)

  Output: num, new_num

  Buffers: shared hit=55

Planning Time: 0.036 ms

Execution Time: 1.151 ms

EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  num, new_num
FROM t;

QUERY PLAN

Seq Scan on public.t  (cost=0.00..124.85 rows=6985 width=36) (actual time=0.008..0.739 rows=10004 loops=1)

  Output: num, new_num

  Buffers: shared hit=55

Planning Time: 0.022 ms

Execution Time: 1.144 ms

EXPLAIN
num	new_num
2	2
	null
null	null
	null
num	new_num
2	2
	null
null	null
	null