Postgres Pro
Downloads
Home   >   mailing lists

Thread: UPDATE table: Syntax to Remove Terminal '\n'

UPDATE table: Syntax to Remove Terminal '\n'

From
Rich Shepard
Date:
   I have some rows in a table where a column attribute has a newline (\n)
appended to the string. How do I represent that newline character in a SQL
statement using psql?

   I've tried adding E'\n' to the end of the string but that doesn't work.

   Here's what I see when I select distinct for that column:

StarvationCrk+


   That's a blank line below the name.

TIA,

Rich

Re: UPDATE table: Syntax to Remove Terminal '\n'

From
Ian Barwick
Date:
On 14/08/28 7:31, Rich Shepard wrote:
>   I have some rows in a table where a column attribute has a newline (\n)
> appended to the string. How do I represent that newline character in a SQL
> statement using psql?
>
>   I've tried adding E'\n' to the end of the string but that doesn't work.
>
>   Here's what I see when I select distinct for that column:
>
> StarvationCrk+
>
>
>   That's a blank line below the name.
>
> TIA,

Not sure what you mean by "doesn't work"; if you want a more precise
rendering of the newline character, the unicode linestyle (suggested
by Tom Lane in the previous thread) should do the trick:

    postgres=> \pset linestyle unicode
    Line style (linestyle) is unicode.
    postgres=> SELECT E'foo\n';
     ?column?
    ──────────
     foo     ↵

    (1 row)


Regards

Ian Barwick

--
 Ian Barwick                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: UPDATE table: Syntax to Remove Terminal '\n'

From
Ian Barwick
Date:
On 14/08/28 8:04, Ian Barwick wrote:
> On 14/08/28 7:31, Rich Shepard wrote:
>>   I have some rows in a table where a column attribute has a newline (\n)
>> appended to the string. How do I represent that newline character in a SQL
>> statement using psql?
>>
>>   I've tried adding E'\n' to the end of the string but that doesn't work.
>>
>>   Here's what I see when I select distinct for that column:
>>
>> StarvationCrk+
>>
>>
>>   That's a blank line below the name.
>>
>> TIA,
>
> Not sure what you mean by "doesn't work"; if you want a more precise
> rendering of the newline character, the unicode linestyle (suggested
> by Tom Lane in the previous thread) should do the trick:
>
>     postgres=> \pset linestyle unicode
>     Line style (linestyle) is unicode.
>     postgres=> SELECT E'foo\n';
>      ?column?
>     ──────────
>      foo     ↵
>
>     (1 row)

And to remove the newline character you can do something like this:

    postgres=> SELECT regexp_replace(E'foo\n', E'\n$','');
     regexp_replace
    ────────────────
     foo
    (1 row)


Regards

Ian Barwick

--
 Ian Barwick                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: UPDATE table: Syntax to Remove Terminal '\n' [RESOLVED]

From
Rich Shepard
Date:
On Wed, 27 Aug 2014, Jeff Ross wrote:

> You want the E in front of the entire string, not just before the \n.
> select
> 'Use Discover: ' || E'\t' || 'Yes'  || E'\n'

Jeff,

   That did the trick. Turns out that 202 of 204 rows had the newline! The
syntax that worked:

update benthos set stream = 'StarvationCrk' where stream = E'StarvationCrk\n';

Much appreciated,

Rich