If I have a table with column names like that:
name,
name_raw,
item,
item_raw,
message,
message_raw
...
... etc.
How can I select every column that does not end with _raw dynamically?
Is something like this:
SELECT
[SOME REGEX LOGIC HERE]
FROM
mytable
or similar possible?
If you have so many columns that you cannot write a (static) query, you probably have to change the schema of your db.
If you cannot change the schema, here's a quick and dirty solution that utilizes postgresql's JSON capabilities. It does not return a traditional table with multiple columns, instead it returns a single column that contains json objects which contain all the columns from the original table whose name ends with _raw.
SELECT (
SELECT json_object_agg(key,value)
FROM json_each(to_json(t))
WHERE key ~ 'raw$'
) FROM mytable;
The idea is convert every row from mytable to a JSON object and then use JSON functions to filter the members of the object.
Use the information schema e.g.
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'my_schema'
AND table_name = 'mytable'
AND NOT column_name LIKE '%\_raw'
note because the underscore itself is a query wildcard it needs to be "escaped"
