Padding a string in Postgresql with rpad without truncating it
found a slightly more elegant solution:
SELECT greatest(colname,rpad(colname, 2));
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;
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.