PostgreSQL query to concatenate all row values with comma delimiter

Data and Databases
1

I need help creating a PostgreSQL query that will take all the values from each row and join them together with commas between each value.

My situation:
I have a database table called employee_data with columns like id, full_name, and contact_number.

SELECT * FROM employee_data WHERE id IN (1, 2)

What I want to achieve:
Instead of getting separate columns, I want the output to look like this:

1,piyush,1111111
2,john,2222222

The challenge:
My tables are generated dynamically so I cannot hardcode specific column names or count how many columns there will be. The solution needs to work with any table structure.

Any suggestions on how to accomplish this in PostgreSQL would be really helpful!

2

Here’s what works: query the system catalogs to grab column names, then build a concatenation statement dynamically. I’ve done this exact thing migrating old reports - PostgreSQL’s string_agg with information_schema.columns does the trick. First, pull all column names for your table, then use EXECUTE with a dynamic CONCAT_WS statement. It’s two steps: query the metadata to build your column list, then run the constructed SQL. This handles any table structure without knowing the columns beforehand. Just make sure you’ve got permissions for the information schema, and watch out for data types - some need casting to text for clean concatenation.

3

you can use concat_ws along with a dynamic sql approach. start by getting the column names from information_schema.columns and then construct your concat_ws(‘,’, col1, col2…) query. it’s tricky but it gets the job done without hardcoding columns.

4

Interesting challenge! Have you tried PostgreSQL’s row_to_json() function with some string manipulation? Quick question though - are these dynamically generated tables temp tables or views? I’m curious about your specific use case since most reporting tools already handle column formatting automatically.