Padding a string in Postgresql with rpad without truncating it

how about this

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

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.

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.