Обсуждение: Appended '+' in Column Value
One column in a table has values for the attribute 'stream'. Some queries
return some rows where a stream name (only identified one so far) has an
appended '+'. I cannot update the table to remove that appended character,
and I've not seen this before.
Example:
2220 | STV | 2012-07-12 | Nematoda | | |
| | Omnivore | 50 | | StarvationCrk+| Owyhee
| | | | | |
| | | | | |
2701 | STV-10 | 2013-07-10 | Nematoda | | |
| | Omnivore | 36 | | StarvationCrk | Owyhee
I'd appreciate learning where that '+' originates and how to get rid of
it. A query to count the rows with the appendage returns zero:
select count(*) from benthos where stream = 'StarvationCrk';
count
-------
204
select count(*) from benthos where stream = 'StarvationCrk+';
count
-------
0
TIA,
Rich
On Fri, Aug 22, 2014 at 02:46:46PM -0700, Rich Shepard wrote: > One column in a table has values for the attribute 'stream'. Some queries > return some rows where a stream name (only identified one so far) has an > appended '+'. I cannot update the table to remove that appended character, > and I've not seen this before. > > Example: > > 2220 | STV | 2012-07-12 | Nematoda | | | > | | Omnivore | 50 | | StarvationCrk+| Owyhee > | | | | | | > | | | | | | > 2701 | STV-10 | 2013-07-10 | Nematoda | | | > | | Omnivore | 36 | | StarvationCrk | Owyhee > > I'd appreciate learning where that '+' originates It's probably an indication of a wrapped column value inside the display column bounded by |'s. Try fiddling with \x and \pset. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 14/08/23 6:46, Rich Shepard wrote: > One column in a table has values for the attribute 'stream'. Some queries > return some rows where a stream name (only identified one so far) has an > appended '+'. I cannot update the table to remove that appended character, > and I've not seen this before. > > Example: > > 2220 | STV | 2012-07-12 | Nematoda | | | > | | Omnivore | 50 | | StarvationCrk+| Owyhee > | | | | | | > | | | | | | > 2701 | STV-10 | 2013-07-10 | Nematoda | | | > | | Omnivore | 36 | | StarvationCrk | Owyhee > > I'd appreciate learning where that '+' originates and how to get rid of > it. A query to count the rows with the appendage returns zero: > > select count(*) from benthos where stream = 'StarvationCrk'; > count ------- > 204 > > select count(*) from benthos where stream = 'StarvationCrk+'; > count ------- > 0 You have a newline character. Try: select count(*) from benthos where stream = E'StarvationCrk\n'; Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 08/22/2014 02:46 PM, Rich Shepard wrote: > One column in a table has values for the attribute 'stream'. Some > queries > return some rows where a stream name (only identified one so far) has an > appended '+'. I cannot update the table to remove that appended character, > and I've not seen this before. Is this only in psql? If so I would suspect the table formatting code. What happens if you do \x and then look at the records? > > Example: > > 2220 | STV | 2012-07-12 | Nematoda | | | > | | Omnivore | 50 | | > StarvationCrk+| Owyhee > | | | | | | > | | | | | | > 2701 | STV-10 | 2013-07-10 | Nematoda | | | > | | Omnivore | 36 | | StarvationCrk > | Owyhee > > I'd appreciate learning where that '+' originates and how to get rid of > it. A query to count the rows with the appendage returns zero: > > select count(*) from benthos where stream = 'StarvationCrk'; > count ------- > 204 > > select count(*) from benthos where stream = 'StarvationCrk+'; > count ------- > 0 > > TIA, > > Rich > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 23 Aug 2014, Ian Barwick wrote: > You have a newline character. Try: > select count(*) from benthos where stream = E'StarvationCrk\n'; Ian, Interesting; that query returned 202 of 204 rows. Thanks, Rich
On Fri, 22 Aug 2014, Adrian Klaver wrote: > Is this only in psql? Adrian, Yes. Thanks, Rich
On 08/22/2014 03:03 PM, Rich Shepard wrote: > On Sat, 23 Aug 2014, Ian Barwick wrote: > >> You have a newline character. Try: >> select count(*) from benthos where stream = E'StarvationCrk\n'; > > Ian, > > Interesting; that query returned 202 of 204 rows. Yeah, means either whoever inputted the data kept hitting Enter after each string(most of the time) or whatever program input the data added \n. > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 08/22/2014 03:03 PM, Rich Shepard wrote:
>> On Sat, 23 Aug 2014, Ian Barwick wrote:
>>> You have a newline character. Try:
>>> select count(*) from benthos where stream = E'StarvationCrk\n';
> Yeah, means either whoever inputted the data kept hitting Enter after
> each string(most of the time) or whatever program input the data added \n.
BTW, see \pset (particularly the linestyle option) in the psql man page
for documentation of this behavior and the options for changing it.
regards, tom lane