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)


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


FAQ