pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Return trimmed string from the query
10+ messages / 6 participants
[nested] [flat]

* Return trimmed string from the query
@ 2026-11-01 07:17 Igor Korot <ikorot01@gmail.com>
  2026-11-01 07:02 ` Re: Return trimmed string from the query Pavel Stehule <pavel.stehule@gmail.com>
  2026-11-01 15:13 ` Re: Return trimmed string from the query Ron Johnson <ronljohnsonjr@gmail.com>
  2026-11-02 12:10 ` Re: Return trimmed string from the query hubert depesz lubaczewski <depesz@depesz.com>
  0 siblings, 3 replies; 10+ messages in thread

From: Igor Korot @ 2026-11-01 07:17 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

hi, ALL,
Is there an option to return a trimmed string from the query?

If I try to retrieve the char(10) from the table and the field only have
"abc", how to prevent the value to be returned as "abc     "?

Thank you.






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
@ 2026-11-01 07:02 ` Pavel Stehule <pavel.stehule@gmail.com>
  2026-11-01 15:13   ` Fwd: Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2026-11-05 02:56   ` Re: Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2 siblings, 2 replies; 10+ messages in thread

From: Pavel Stehule @ 2026-11-01 07:02 UTC (permalink / raw)
  To: Igor Korot <ikorot01@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi

ne 1. 11. 2026 v 7:43 odesílatel Igor Korot <ikorot01@gmail.com> napsal:

> hi, ALL,
> Is there an option to return a trimmed string from the query?
>
> If I try to retrieve the char(10) from the table and the field only have
> "abc", how to prevent the value to be returned as "abc     "?
>

no, there is not any option, you need to cast result value to varchar;

(2026-11-01 07:57:03) postgres=# create table foo(a char(10));
CREATE TABLE
(2026-11-01 08:01:49) postgres=# insert into foo values('abc');
INSERT 0 1
(2026-11-01 08:02:03) postgres=# select a, a::varchar from foo;
┌────────────┬─────┐
│     a      │  a  │
╞════════════╪═════╡
│ abc        │ abc │
└────────────┴─────┘
(1 row)

regards

Pavel


>
> Thank you.
>
>
>


^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Fwd: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2026-11-01 07:02 ` Re: Return trimmed string from the query Pavel Stehule <pavel.stehule@gmail.com>
@ 2026-11-01 15:13   ` Igor Korot <ikorot01@gmail.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Igor Korot @ 2026-11-01 15:13 UTC (permalink / raw)
  To: PostgreSQL ODBC list <pgsql-odbc@postgresql.org>

Hi,
Is there an option in the DM to do the same?

If not - how to do it with ODBC?

Thx.

---------- Forwarded message ---------
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sun, Nov 1, 2026 at 12:03 AM
Subject: Re: Return trimmed string from the query
To: Igor Korot <ikorot01@gmail.com>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>


Hi

ne 1. 11. 2026 v 7:43 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
>
> hi, ALL,
> Is there an option to return a trimmed string from the query?
>
> If I try to retrieve the char(10) from the table and the field only have
> "abc", how to prevent the value to be returned as "abc     "?


no, there is not any option, you need to cast result value to varchar;

(2026-11-01 07:57:03) postgres=# create table foo(a char(10));
CREATE TABLE
(2026-11-01 08:01:49) postgres=# insert into foo values('abc');
INSERT 0 1
(2026-11-01 08:02:03) postgres=# select a, a::varchar from foo;
┌────────────┬─────┐
│     a      │  a  │
╞════════════╪═════╡
│ abc        │ abc │
└────────────┴─────┘
(1 row)

regards

Pavel

>
>
> Thank you.
>
>





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2026-11-01 07:02 ` Re: Return trimmed string from the query Pavel Stehule <pavel.stehule@gmail.com>
@ 2026-11-05 02:56   ` Igor Korot <ikorot01@gmail.com>
  2026-11-05 04:48     ` Re: Return trimmed string from the query Rob Sargent <robjsargent@gmail.com>
  2026-11-05 04:49     ` Re: Return trimmed string from the query Pavel Stehule <pavel.stehule@gmail.com>
  2026-11-05 04:50     ` Re: Return trimmed string from the query Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 3 replies; 10+ messages in thread

From: Igor Korot @ 2026-11-05 02:56 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi,

On Sun, Nov 1, 2026 at 12:03 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> ne 1. 11. 2026 v 7:43 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
>>
>> hi, ALL,
>> Is there an option to return a trimmed string from the query?
>>
>> If I try to retrieve the char(10) from the table and the field only have
>> "abc", how to prevent the value to be returned as "abc     "?
>
>
> no, there is not any option, you need to cast result value to varchar;
>
> (2026-11-01 07:57:03) postgres=# create table foo(a char(10));
> CREATE TABLE
> (2026-11-01 08:01:49) postgres=# insert into foo values('abc');
> INSERT 0 1
> (2026-11-01 08:02:03) postgres=# select a, a::varchar from foo;
> ┌────────────┬─────┐
> │     a      │  a  │
> ╞════════════╪═════╡
> │ abc        │ abc │
> └────────────┴─────┘

Sorry for a late reply.

So does this mean that if I use varchar instead of char I will not
have this problem?

Thank you,

> (1 row)
>
> regards
>
> Pavel
>
>>
>>
>> Thank you.
>>
>>






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2026-11-01 07:02 ` Re: Return trimmed string from the query Pavel Stehule <pavel.stehule@gmail.com>
  2026-11-05 02:56   ` Re: Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
@ 2026-11-05 04:48     ` Rob Sargent <robjsargent@gmail.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Rob Sargent @ 2026-11-05 04:48 UTC (permalink / raw)
  To: Igor Korot <ikorot01@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>



> Sorry for a late reply.
> 
> So does this mean that if I use varchar instead of char I will not
> have this problem?
> 
> Thank you,

Yes. If you use text rather char(n) you will not get space-padded values. 

If you have char(n) fields already you have work to do to irradiate them. It will be effort well spent though. 


> 
>> (1 row)
>> 
>> regards
>> 
>> Pavel
>> 
>>> 
>>> 
>>> Thank you.
>>> 
>>> 
> 
> 





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2026-11-01 07:02 ` Re: Return trimmed string from the query Pavel Stehule <pavel.stehule@gmail.com>
  2026-11-05 02:56   ` Re: Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
@ 2026-11-05 04:49     ` Pavel Stehule <pavel.stehule@gmail.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Pavel Stehule @ 2026-11-05 04:49 UTC (permalink / raw)
  To: Igor Korot <ikorot01@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

čt 5. 11. 2026 v 3:56 odesílatel Igor Korot <ikorot01@gmail.com> napsal:

> Hi,
>
> On Sun, Nov 1, 2026 at 12:03 AM Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
> >
> > Hi
> >
> > ne 1. 11. 2026 v 7:43 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
> >>
> >> hi, ALL,
> >> Is there an option to return a trimmed string from the query?
> >>
> >> If I try to retrieve the char(10) from the table and the field only have
> >> "abc", how to prevent the value to be returned as "abc     "?
> >
> >
> > no, there is not any option, you need to cast result value to varchar;
> >
> > (2026-11-01 07:57:03) postgres=# create table foo(a char(10));
> > CREATE TABLE
> > (2026-11-01 08:01:49) postgres=# insert into foo values('abc');
> > INSERT 0 1
> > (2026-11-01 08:02:03) postgres=# select a, a::varchar from foo;
> > ┌────────────┬─────┐
> > │     a      │  a  │
> > ╞════════════╪═════╡
> > │ abc        │ abc │
> > └────────────┴─────┘
>
> Sorry for a late reply.
>
> So does this mean that if I use varchar instead of char I will not
> have this problem?
>

sure.

char(n) type is "strange". It is designed for compatibility with old Cobol
databases, and there is not any benefit for their usage today.

Regards

Pavel


>
> Thank you,
>
> > (1 row)
> >
> > regards
> >
> > Pavel
> >
> >>
> >>
> >> Thank you.
> >>
> >>
>

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2026-11-01 07:02 ` Re: Return trimmed string from the query Pavel Stehule <pavel.stehule@gmail.com>
  2026-11-05 02:56   ` Re: Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
@ 2026-11-05 04:50     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Adrian Klaver @ 2026-11-05 04:50 UTC (permalink / raw)
  To: Igor Korot <ikorot01@gmail.com>; Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

On 11/4/26 6:56 PM, Igor Korot wrote:
> Hi,
> 

> Sorry for a late reply.
> 
> So does this mean that if I use varchar instead of char I will not
> have this problem?

It is all explained in the docs:

https://www.postgresql.org/docs/current/datatype-character.html

> 
> Thank you,
> 
>> (1 row)
>>
>> regards
>>
>> Pavel
>>
>>>
>>>
>>> Thank you.
>>>
>>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
@ 2026-11-01 15:13 ` Ron Johnson <ronljohnsonjr@gmail.com>
  2026-11-01 15:49   ` Re: Return trimmed string from the query Adrian Klaver <adrian.klaver@aklaver.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Ron Johnson @ 2026-11-01 15:13 UTC (permalink / raw)
  To: pgsql-general

On Sun, Nov 1, 2026 at 1:43 AM Igor Korot <ikorot01@gmail.com> wrote:

> hi, ALL,
> Is there an option to return a trimmed string from the query?
>
> If I try to retrieve the char(10) from the table and the field only have
> "abc", how to prevent the value to be returned as "abc     "?
>


TRIM() is the portable/standards method:
SELECT TRIM(TRAILING ' ' FROM a) FROM foo;

There are many useful string functions:
https://www.postgresql.org/docs/17/functions-string.html

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
  2026-11-01 15:13 ` Re: Return trimmed string from the query Ron Johnson <ronljohnsonjr@gmail.com>
@ 2026-11-01 15:49   ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Adrian Klaver @ 2026-11-01 15:49 UTC (permalink / raw)
  To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general

On 11/1/26 7:13 AM, Ron Johnson wrote:
> On Sun, Nov 1, 2026 at 1:43 AM Igor Korot <ikorot01@gmail.com 
> <mailto:ikorot01@gmail.com>> wrote:
> 
>     hi, ALL,
>     Is there an option to return a trimmed string from the query?
> 
>     If I try to retrieve the char(10) from the table and the field only have
>     "abc", how to prevent the value to be returned as "abc     "?
> 
> 
> 
> TRIM() is the portable/standards method:
> SELECT TRIM(TRAILING ' ' FROM a) FROM foo;

*TRIM removes spaces by default so the  ' ' is not necessary((length 
thrown in to prove trim):

select trim(TRAILING 'abc  '), length(trim(TRAILING'abc  '));
  rtrim | length
-------+--------
  abc   |      3


Even shorter would be::

select rtrim('abc  '), length(rtrim('abc  '));

rtrim | length
-------+--------
  abc   |      3

> 
> There are many useful string functions:
> https://www.postgresql.org/docs/17/functions-string.html <https:// 
> www.postgresql.org/docs/17/functions-string.html>
> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Return trimmed string from the query
  2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
@ 2026-11-02 12:10 ` hubert depesz lubaczewski <depesz@depesz.com>
  2 siblings, 0 replies; 10+ messages in thread

From: hubert depesz lubaczewski @ 2026-11-02 12:10 UTC (permalink / raw)
  To: Igor Korot <ikorot01@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

On Sun, Nov 01, 2026 at 01:17:10AM -0600, Igor Korot wrote:
> hi, ALL,
> Is there an option to return a trimmed string from the query?
> If I try to retrieve the char(10) from the table and the field only have
> "abc", how to prevent the value to be returned as "abc     "?

Others already helped, but also, please read:
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don't_use_char(n)

Best regards,

depesz







^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2026-11-05 04:50 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-01 07:17 Return trimmed string from the query Igor Korot <ikorot01@gmail.com>
2026-11-01 07:02 ` Pavel Stehule <pavel.stehule@gmail.com>
2026-11-01 15:13   ` Igor Korot <ikorot01@gmail.com>
2026-11-05 02:56   ` Igor Korot <ikorot01@gmail.com>
2026-11-05 04:48     ` Rob Sargent <robjsargent@gmail.com>
2026-11-05 04:49     ` Pavel Stehule <pavel.stehule@gmail.com>
2026-11-05 04:50     ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-11-01 15:13 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-11-01 15:49   ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-11-02 12:10 ` hubert depesz lubaczewski <depesz@depesz.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox