Padding a string in Postgresql with rpad without truncating it

Question

Using Postgresql 8.4, how can I right-pad a string with blanks without truncating it when it's too long?

The problem is that rpad truncates the string when it is actually longer than number of characters to pad. Example:

SELECT rpad('foo', 5);  ==> 'foo  ' -- fine
SELECT rpad('foo', 2);  ==> 'fo'    -- not good, I want 'foo' instead.

The shortest solution I found doesn't involve rpad at all:

SELECT 'foo' || repeat(' ', 5-length('foo'));  ==> 'foo  ' -- fine
SELECT 'foo' || repeat(' ', 2-length('foo'));  ==> 'foo'   -- fine, too

but this looks ugly IMHO. Note that I don't actually select the string 'foo' of course, instead I select from a column:

SELECT colname || repeat(' ', 30-length(colname)) FROM mytable WHERE ...

Is there a more elegant solution?

TimoSolo · Accepted Answer · 2014-09-09 05:35:17Z

19

found a slightly more elegant solution:

SELECT greatest(colname,rpad(colname, 2));

eg:

SELECT greatest('foo',rpad('foo', 5));  -- 'foo  ' 
SELECT greatest('foo',rpad('foo', 2));  -- 'foo'

.

To explain how it works: rpad('foo',5) = 'foo ' which is > 'foo' (greatest works with strings as well as numbers) rpad('foo',2) = 'fo' which is < 'foo', so 'foo' is selected by greatest function.

if you want left-padded words you cant use greatest because it compares left-to-right (eg 'oo' with 'foo') and in some cases this will be greater or smaller depending on the string. I suppose you could reverse the string and use the rpad and reverse it back, or just use the original solution which works in both cases.

edited Sep 9, 2014 at 5:35

answered Aug 4, 2014 at 12:53

TimoSolo

7,3756 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mu is too short · Accepted Answer · 2012-08-27 19:26:17Z

8

If you don't want to write that repeat business all the time, just write your own function for it. Something like this:

create or replace function rpad_upto(text, int) returns text as $$
begin
    if length($1) >= $2 then
        return $1;
    end if;
    return rpad($1, $2);
end;
$$ language plpgsql;

or this:

create or replace function rpad_upto(text, int) returns text as $$
select $1 || repeat(' ', $2 - length($1));
$$ language sql;

Then you can say things like:

select rpad_upto(colname, 30) from mytable ...

You might want to consider what you want rpad_upto(null, n) to produce while you're at it. Both versions of rpad_upto above will return NULL if $1 is NULL but you can tweak them to return something else without much difficulty.

answered Aug 27, 2012 at 19:26

mu is too short

436k71 gold badges863 silver badges822 bronze badges

1 Comment

dmoebius Over a year ago

Thanks for your answer. It would work, but db admins do not allow me to create new functions (at least not for such a trivial task, only when absolutely neccessary).

Toon Krijthe · Accepted Answer · 2012-10-10 09:36:32Z

4

how about this

select case when length(col) < x then rpad(col, x)
else col
end
from table

edited Oct 10, 2012 at 9:36

Toon Krijthe

53.6k38 gold badges151 silver badges204 bronze badges

answered Oct 10, 2012 at 9:35

rutu

411 bronze badge

1 Comment

dmoebius Over a year ago

Nice, but 8 chars more than mine. ;)

Emery Lapinski · Accepted Answer · 2015-05-06 19:37:12Z

0

Assuming efficiency is not your biggest concern here:

select regexp_replace(format('%5s', 'foo'), '(\s*)(\S*)', '\2\1')

format() left-pads the string to the desired width
then regexp_replace moves any leading spaces to the end.

I guess that would fail if you have leading spaces in the strings and you want to preserve them. Also note that format() doesn't return null on null params.

answered May 6, 2015 at 19:37

Emery Lapinski

1,6621 gold badge19 silver badges28 bronze badges

2 Comments

Bernhard Barker Over a year ago

Simply select format('%-5s', 'foo') would give the same result.

Emery Lapinski Over a year ago

this feature appears to be unavailable in versions prior to 9.3

Melissa Barnett · Accepted Answer · 2020-01-10 19:16:30Z

0

PostgreSQL statement below is to right pad three place values and alter column data type to text for column 'columnname.' I used pycharm IDE to help construct statement. The statement will pad with 000.

I have been looking for a while to solve the same issue, except for left pad and I thought I would share.

alter table 'schema.tablename' alter column 'columnname' type text using rpad('columnname'::text,3,'0')

answered Jan 10, 2020 at 19:16

Melissa Barnett

501 silver badge4 bronze badges

Collectives™ on Stack Overflow

Padding a string in Postgresql with rpad without truncating it

5 Answers 5

Comments

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related