Re: Return trimmed string from the query
Re: Return trimmed string from the query
От:
Pavel Stehule <pavel.stehule@gmail.com>
Дата:
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)
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.
Return trimmed string from the query
От:
Igor Korot <ikorot01@gmail.com>
Дата:
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.
Re: Return trimmed string from the query
От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
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:
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Re: Return trimmed string from the query
От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 11/1/26 7:13 AM, Ron Johnson wrote:
> On Sun, Nov 1, 2026 at 1:43 AM 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 "?
>
>
>
> 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 www.postgresql.org/docs/17/functions-string.html>
>
> --
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
> lobster!
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Return trimmed string from the query
От:
hubert depesz lubaczewski <depesz@depesz.com>
Дата:
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
Re: Return trimmed string from the query
От:
Igor Korot <ikorot01@gmail.com>
Дата:
Hi,
On Sun, Nov 1, 2026 at 12:03 AM Pavel Stehule wrote:
>
> Hi
>
> ne 1. 11. 2026 v 7:43 odesílatel Igor Korot 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.
>>
>>
Re: Return trimmed string from the query
От:
Rob Sargent <robjsargent@gmail.com>
Дата:
> 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. >>> >>> > >
Re: Return trimmed string from the query
От:
Pavel Stehule <pavel.stehule@gmail.com>
Дата:
č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.
>>
>>
Re: Return trimmed string from the query
От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
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