Menu
B) Using STRING_SPLIT() function to split a comma-separated string in a column. Sometimes, database tables are not normalized. A typical example of this is when a column can store multiple values separated by a comma (,). The STRING_SPLIT() can help normalize the data by splitting these multi-valued columns.
What is PostgreSQL Cross Join? The PostgreSQL Cross Join is used to combine all possibilities of the multiple tables and returns the output, which contain each row from all the selected tables. The CROSS JOIN, further known as CARTESIAN JOIN that allows us to produce the Cartesian product of all related tables.
The PostgreSQL BETWEEN condition will return the records where expression is within the range of value1 and value2 (inclusive).
SELECT string_agg(id::text, ',') FROM table
Requires PostgreSQL 9.0 but that's not a problem.
You can use the array() and array_to_string() functions togetter with your query.
With SELECT array( SELECT id FROM table ); you will get a result like: {1,2,3,4,5,6}
Then, if you wish to remove the {} signs, you can just use the array_to_string() function and use comma as separator, so: SELECT array_to_string( array( SELECT id FROM table ), ',' ) will get a result like: 1,2,3,4,5,6
You can generate a CSV from any SQL query using psql:
$ psql
> \o myfile.csv
> \f ','
> \a
> SELECT col1 AS column1, col2 AS column2 ... FROM ...
The resulting myfile.csv will have the SQL resultset column names as CSV column headers, and the query tuples as CSV rows.
h/t http://pookey.co.uk/wordpress/archives/51-outputting-from-postgres-to-csv
use array_to_string() & array() function for the same.
select array_to_string(array(select column_name from table_name where id=5), ', ');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With