Goal This query identifies derived tables (views and materialized views) containing string concatenation logic that introduces potential leading or trailing whitespace. It targets expressions using the concatenation operator (||), concat(), or format() that may inject separators (such as spaces) but lack a surrounding trim() function. This pattern often results in "dangling separators" when one of the concatenated components is null or empty, degrading data quality and presentation.
Notes In PostgreSQL NULL || 'text' => NULL. To replace NULL with an empty string one can use Coalesce function. Thus Coalesce(NULL,'') || 'text' => 'text'. Coalesce(NULL,'') || ' ' || 'text' => ' text'. We can use Trim function to get rid of the space at the beginning of the concatenation result like that: Trim(Coalesce(NULL,'') || ' ' || 'text'))=> 'text'. Similarly, Concat (NULL, ' ', 'text') )=>' text' and Trim should be used to get rid of the space at the beginning of the result.

Instead of SELECT Concat(surname, ' ', given_name) AS person_name FROM Person; write SELECT Trim(Concat(surname, ' ', given_name)) AS person_name FROM Person;
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Low (Many false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Change the subquery of the derived table. Use Trim function or use Concat_ws function to concatenate the values.

concat(NULL, ' ', 'a')=> " a"

concat_ws(' ', NULL, 'a')=> "a"
Data Source system catalog only
SQL Query 
WITH derived_tables AS (SELECT n.nspname AS table_schema, 
c.relname AS table_name,
CASE WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
END AS table_type,
pg_get_viewdef(c.oid, true) AS table_definition,
regexp_replace(pg_get_viewdef(c.oid, true),'[\r\n]','<br>','g') AS table_def
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind IN ('v','m'))
SELECT table_schema, table_name, table_type, table_def
FROM derived_tables
WHERE table_definition  ~*'(?<!([[:space:]]Trim[(].*))(concat(?!_ws)|format|\|\|)'
ORDER BY table_schema, table_name;

Categories

This query is classified under the following categories:

Derived tables

Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.

NameDescription
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.