Get only the data when i execute a query in PostgreSQL

Question

When I execute my query in PostgreSQL:

SELECT names 
from user;

I obtain the following result:

      names
--------------
Anna
Julius
Perico

(3 rows)

What I want to get is the following output:

Anna Julius Perico

I need this because is a bash script ant I need to save it in a variable.

Not sure what the difference between the two alternatives is, but you can also use psql -A -t to get rid of the column header and the (3 rows) feedback. — user330315
– user330315, Commented Mar 22, 2019 at 11:04
Thank you that's exactly what I want. The final command i have used is psql -A -t -d users -c \" select names from user \"" postgres — Marco
– Marco, Commented Mar 22, 2019 at 11:38

Laurenz Albe · Accepted Answer · 2019-03-22 12:55:51Z

3

If you want to use it in a shell script, this would be the best way:

myvar=`psql --no-align --quiet --tuples-only --command='SELECT name FROM users'`

No need to have them in one line; the shell accepts a line feed as field separator as well:

for n in $myvar; do
    echo "name: $n"
done

name: Anna
name: Julius
name: Perico

answered Mar 22, 2019 at 12:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2019-03-22 11:05:10Z

1

The string_agg function might be what you want here:

select string_agg(names, ' ' order by names) from user;

I said "might" because this assumes that the names column itself could be used to generate the order in the single string output. If you want a different order, then you would need another column.

edited Mar 22, 2019 at 11:05

answered Mar 22, 2019 at 10:41

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

@a_horse_with_no_name These multiple edits are really AGGrevating for me :-(

Collectives™ on Stack Overflow

Get only the data when i execute a query in PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related