-1

I have output, like the following, from postgres database

datname | size ---- template1 | 6314 kB template0 | 6201 kB postgres | 7938 kB misago |
6370 kB (4 rows)

I want only these 6314, 6201, and 7938 values from output. How can I do this?

awk, grep or sed are preferable.

  • What is the SQL query? – Kusalananda Sep 1 '17 at 11:26
  • SELECT pg_database.datname,pg_database_size(pg_database.datname) AS size FROM pg_database; – Arun Sep 1 '17 at 12:15
0

With the output of the database query in db.out:

grep -oE '[0-9]+ kB' db.out

This will produce

6314 kB
6201 kB
7938 kB
6370 kB

Then strip off the ␣kB and delete the last line:

$ grep -oE '[0-9]+ kB' db.out | sed -e 's/ kB//' -e '$d'
6314
6201
7938

If you want this on one line, with three numbers on each line, pass it through paste:

$ grep -oE '[0-9]+ kB' db.out | sed -e 's/ kB//' -e '$d' | paste - - -
6314    6201    7938
0

awk solution:

Sample testfile contents:

datname | size ---- template1 | 6314 kB template0 | 6201 kB postgres | 7938 kB misago | 6370 kB
datname | size ---- template1 | 3000 kB template0 | 3001 kB postgres | 3002 kB misago | 6370 kB
datname | size ---- template1 | 4014 kB template0 | 4001 kB postgres | 4002 kB misago | 6370 kB

awk -F' *\\| *' '{ for(i=3;i<=5;i++) 
        printf "%s%s",substr($i,1,index($i," ")-1),(i==5? ORS:OFS) }' OFS=',' testfile

The output:

6314,6201,7938
3000,3001,3002
4014,4001,4002

----------

In case if the file has a fixed format (there wouldn't be any whitespaces within template fields) - use simple cut approach:

cut -d' ' --output-delimiter=',' -f7,11,15 <testfile
0
while read -r i; do
  <<< "${i}" \
  grep -P -o -e '(?<= )([0-9]*?)(?= )' |
  cut -d $'\n' -f 1-3 --output-delimiter=' '
done < 'file'

As an one-liner:

while read -r i; do <<< "${i}" grep -P -o -e '(?<= )([0-9]*?)(?= )' | cut -d $'\n' -f 1-3 --output-delimiter=' '; done < 'file'

Output:

6314 6201 7938

Change the output delimiter if you want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.