Re: [SQL] How would I get rid of trailing blank line?

Hi Everybody,

I am using postgres 8.3.4 on linux.
I often use a line like:
psql -tf query.sql mydatabase > query.out

-t option gets rid of the heading and count
report at the bottom. There is a blank line
at the bottom, however. Is there any way to
have psql not give me that blank line?

Thank you for your help.

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

"Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu> writes:
> I often use a line like:
> psql -tf query.sql mydatabase > query.out

> -t option gets rid of the heading and count
> report at the bottom. There is a blank line
> at the bottom, however. Is there any way to
> have psql not give me that blank line?

Doesn't look like it --- the final fputc('\n', fout); seems to be
done unconditionally in all the output formats. I wonder if we should
change that? I'm afraid it might break programs that are used to it :-(

regards, tom lane

Hi Tom,

I am a bit surprised to hear that that '\n'
is there unconditionally. But I am sure
there are more pressing things for you to
work on. It's something I can live with.

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thu 4/2/2009 4:01 PM
To: Tena Sakai
Cc: pgsql-sql(at)postgresql(dot)org; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [SQL] How would I get rid of trailing blank line?

"Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu> writes:
> I often use a line like:
> psql -tf query.sql mydatabase > query.out

> -t option gets rid of the heading and count
> report at the bottom. There is a blank line
> at the bottom, however. Is there any way to
> have psql not give me that blank line?

Doesn't look like it --- the final fputc('\n', fout); seems to be
done unconditionally in all the output formats. I wonder if we should
change that? I'm afraid it might break programs that are used to it :-(

regards, tom lane

Tom Lane wrote:
> "Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu> writes:
>
>> I often use a line like:
>> psql -tf query.sql mydatabase > query.out
>>
>
>
>> -t option gets rid of the heading and count
>> report at the bottom. There is a blank line
>> at the bottom, however. Is there any way to
>> have psql not give me that blank line?
>>
>
> Doesn't look like it --- the final fputc('\n', fout); seems to be
> done unconditionally in all the output formats. I wonder if we should
> change that? I'm afraid it might break programs that are used to it :-(
>
>
>

Right. There's a simple pipeline way to get rid of it:

psql -t -f query.sql | sed -e '$d' > query.out

cheers

andrew

On Thu, Apr 2, 2009 at 3:33 PM, Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)edu> wrote:
> Hi Everybody,
>
> I am using postgres 8.3.4 on linux.
> I often use a line like:
>   psql -tf query.sql mydatabase > query.out
>
> -t option gets rid of the heading and count
> report at the bottom.  There is a blank line
> at the bottom, however.  Is there any way to
> have psql not give me that blank line?

Tired of those blank lines in your text files? Grep them away:

psql -tf query.sql mydatabase | grep -v "^$" > query.out

>
> Thank you for your help.
>
> Regards,
>
> Tena Sakai
> tsakai(at)gallo(dot)ucsf(dot)edu
>

--
When fascism comes to America, it will be the intolerant selling it as
diversity.

Hi Andrew,

> Right. There's a simple pipeline way to get rid of it:
> psql -t -f query.sql | sed -e '$d' > query.out

Hi Scott,

> Tired of those blank lines in your text files? Grep them away:
> psql -tf query.sql mydatabase | grep -v "^$" > query.out

Thank you Both.

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Andrew Dunstan [mailto:andrew(at)dunslane(dot)net]
Sent: Thu 4/2/2009 6:34 PM
To: Tom Lane
Cc: Tena Sakai; pgsql-sql(at)postgresql(dot)org; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] [SQL] How would I get rid of trailing blank line?

Tom Lane wrote:
> "Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu> writes:
>
>> I often use a line like:
>> psql -tf query.sql mydatabase > query.out
>>
>
>
>> -t option gets rid of the heading and count
>> report at the bottom. There is a blank line
>> at the bottom, however. Is there any way to
>> have psql not give me that blank line?
>>
>
> Doesn't look like it --- the final fputc('\n', fout); seems to be
> done unconditionally in all the output formats. I wonder if we should
> change that? I'm afraid it might break programs that are used to it :-(
>
>
>

Right. There's a simple pipeline way to get rid of it:

psql -t -f query.sql | sed -e '$d' > query.out

cheers

andrew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> report at the bottom. There is a blank line
> at the bottom, however. Is there any way to
> have psql not give me that blank line?

Now that my presenation on psql is over :), I'll share my solution:

psql -AX -qt -c "SELECT ..." | perl -pe 's/^\n// if $.<2'

This strips a newline from the first line only of the output, and
only if the line consists of nothing else. Highly recommended
for cron.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200904052221
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAknZZ0YACgkQvJuQZxSWSsiDAwCglJS9/juQLe8asY3sG9fagbeo
2V4An0p5U6UHGI1KXoe2qQvURX5E5BZo
=Yy0J
-----END PGP SIGNATURE-----

On 2009-04-02, Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)edu> wrote:

> I am using postgres 8.3.4 on linux.
> I often use a line like:
> psql -tf query.sql mydatabase > query.out
>
> -t option gets rid of the heading and count
> report at the bottom. There is a blank line
> at the bottom, however. Is there any way to
> have psql not give me that blank line?

I ask postgres to format it for me instead of relying on psql

psql db_name -c "copy (select * from foo ) to stdout;"

this gives me postgres style tab separated values, but I can
have CSV (or any otther format COPY can do) if I want.
I usually use a more complex subquery with a list of columns and
expressions, where, order by clauses, etc... )

I use this in a script that pulls data from one database and inserts
in into another.

Requires postgres 8.2 or later

The sed -e '$d' is nice but it has a downside - it will mask the return code
from psql in the case when there is an error

--
View this message in context: http://postgresql.nabble.com/How-would-I-get-rid-of-trailing-blank-line-tp2153908p5873931.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.