PostgreSQL export csv from shell

I needed to get stats per minute for a SMS TV vote from 50 tables – one table per vote:

solution 1:

#!/bin/sh
op=2
op=3
ip="10.10.10.157"

# NO work - version 8.0.4 ...
# op=1
# ip="10.10.10.5"

dbname="vote_2012_01"
for num in `seq 1 50`
do
	if (( ${num} < 10 ))
	then
		num="0${num}"
	fi
	
echo "copy (
select 
'Pesen ${num}' as pesen
,operator_id as op
, count(*) as cntr 
, date_trunc('minute',intime) as minutes
from  vote_201201_${num}
where true 
and operator_id = ${op} 
group by minutes, op 
order by minutes, op
)
to stdout  with csv header
force quote pesen,minutes
;
" | psql -U me -h ${ip} ${dbname} -F , --no-align  > data.op.${op}.pesen.${num}.csv

done

==============================================================
solution 2 – copy(SQL) did not work at psql -v 8.0.4 on me

#!/bin/sh
# done by dragkh
op=2
op=3
ip="10.10.10.157"

#work - version 8.0.4 ...
op=1
ip="10.10.10.5"

dbname="vote_2012_01"

for num in `seq 1 50`
do
	if (( ${num} < 10 ))
	then
		num="0${num}"
	fi
	
echo "select
'\"Pesen ${num}\"' as pesen
,operator_id as op
, count(*) as cntr 
, '\"' || date_trunc('minute',intime) || '\"' as minutes
from  vote_201201_${num}
where true 
and operator_id = ${op} 
group by minutes, op 
order by minutes, op
;
" | psql -U me -h ${ip} ${dbname} -F , --no-align  > data.op.${op}.pesen.${num}.csv

done

Advertisement