Due to security reasons, postgres (in contrast to mysql) does not allow a user to specify the password on the command line. However, there is a quick and hacky workaround which allow a psql command to be run without the password prompt.
From the command line, simply type:
export PGPASSWORD=yourpassword
From there you can now run:
psql -U username database
Postgres always surprises me with its robustness and cutting edge developments and extensions. postgis was years ahead of the competition. I’ve recently discovered that PostgreSQL offers the rather sweet feature of aggregating multiple rows into an array, and then the implode-like feature of array_to_string to complete the coolness.
The problem for me was simply finding it again. I’d made a number of searches, including:
The last one was the key which lead me to stack overflow, and was just what I was looking for.
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
Once I’ve created that function, I can now do this to turn my rows into a comma separated list:
SELECT array_to_string(array_accum(engine_name), ', ')
FROM search_engine
which turns this:
Bing
Google
into this:
Bing, Google