Postgres Pro
Downloads
Home   >   mailing lists

Thread: Space Stalker in SQL Output

Space Stalker in SQL Output

From
Susan Hurst
Date:
Why would a psql statement insert a leading space into the output, which 
is a single integer value?

The leading space caused my job call to fail elsewhere in the same shell 
script as the psql call.  Here is the anonymized version of the psql 
call to assign a value to a shell script variable:

IDz=`psql -d proddb -U produser -h 10.9.999.99 -p 99900 -t < 
last_id.sql`

The output is simply a max(id) value, which is defined as an integer 
data type in the source table column.  The output looked like this 
(notice the leading space before the integer value):

echo “IDz =${IDz}
IDz =’ 100’

The last_id.sql itself is simply:  select max(id) from prodtable;

I'm using:

PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 
20120313 (Red Hat 4.4.7-16), 64-bit

I fixed the output in the shell script with a tr command but why should 
that be necessary?  What is causing the space to be prepended to integer 
value?


ID=`echo ${IDz} | tr -d ''`
IDz =’100’

Knowing the root cause of the space stalker would be most helpful.  
Thanks for your help!

Sue


-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

Re: Space Stalker in SQL Output

From
Jerry Sievers
Date:
Susan Hurst <susan.hurst@brookhurstdata.com> writes:

> Why would a psql statement insert a leading space into the output,
> which is a single integer value?
>
> The leading space caused my job call to fail elsewhere in the same
> shell script as the psql call.  Here is the anonymized version of the
> psql call to assign a value to a shell script variable:
>
> IDz=`psql -d proddb -U produser -h 10.9.999.99 -p 99900 -t <
> last_id.sql`

Get in the habit of including -A which gets rid of alignment padding in
psql output.

As in...

shellvar=`psql -Atqc 'select froboz;'` $db

HTH

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Re: Space Stalker in SQL Output

From
Susan Hurst
Date:
Wow!  The -A option worked perfectly!

Thanks for the syntax lesson Steve and Jerry!

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 2018-06-27 14:38, Jerry Sievers wrote:
> Susan Hurst <susan.hurst@brookhurstdata.com> writes:
> 
>> Why would a psql statement insert a leading space into the output,
>> which is a single integer value?
>> 
>> The leading space caused my job call to fail elsewhere in the same
>> shell script as the psql call.  Here is the anonymized version of the
>> psql call to assign a value to a shell script variable:
>> 
>> IDz=`psql -d proddb -U produser -h 10.9.999.99 -p 99900 -t <
>> last_id.sql`
> 
> Get in the habit of including -A which gets rid of alignment padding in
> psql output.
> 
> As in...
> 
> shellvar=`psql -Atqc 'select froboz;'` $db
> 
> HTH

Re: Space Stalker in SQL Output

From
Joe Conway
Date:
On 06/27/2018 12:45 PM, Susan Hurst wrote:
> Wow!  The -A option worked perfectly!
> 
> Thanks for the syntax lesson Steve and Jerry!

If you are going to be doing lots of scripting with Postgres, you might
want to take a look here: https://github.com/jconway/shebang

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development