Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
To keep PostgreSQL from pulling up the subquery in the CTE, you can add the keyword MATERIALIZED: WITH valid_versions AS MATERIALIZED ( SELECT DISTINCT app_version FROM table_name ...
#1: Initial revision
by
Laurenz Albe
·
2026-03-16T09:12:29Z (6 months ago)
To keep PostgreSQL from pulling up the subquery in the CTE, you can add the keyword `MATERIALIZED`:
```sql
WITH valid_versions AS MATERIALIZED (
SELECT DISTINCT app_version
FROM table_name
WHERE app_version ~ '^[0-9.]+$'
)
```
Alternatively, you can create a function to cast `text` to `integer` that doesn't error out using [`pg_input_is_valid()`][valid]:
```
CREATE FUNCTION text2integer(text) RETURNS integer
LANGUAGE plpgsql STRICT SET search_path = pg_catalog AS
$$BEGIN
IF pg_input_is_valid($1, 'integer') THEN
RETURN CAST ($1 AS integer);
END IF;
RETURN NULL;
END;$$ ;
```
[valid]: https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-VALIDITY-TABLE