PostgreSQL: tidier conversion of numeric to 2-digit hex

Question

The zoning data that I get has a set of three columns - red, green and blue. They're the R,G, and B values, respectively, of the fill colour for different planning zones.

In the original tables they're VARCHAR, but I convert them to INT since the R,G and B values in RGB are canonically 8-bit (otherwise anytime you want to use the values for their actual purpose you end up in double-colon hell - and more colons has gotta be bad, right?).

So anyhow, for presentation-layer stuff I need the RGB as hex colour (i.e., #000000 - #FFFFFF).

I know of two ways to do this:

upper('#'||lpad(to_hex(red)::text,2,'0')||lpad(to_hex(green)::text,2,'0')||lpad(to_hex(blue)::text,2,'0'))

or

upper('#'||lpad(to_hex(((red * 65536) + (green * 256) + blue))::text,6,'0'))

Both of these are pretty colon-heavy, and seem really super-kludgy by comparison with Python's

'#%02x%02x%02x' % eval(a,)

Where a = (red,green,blue) extracted from the db by a cursor.execute.

The lpad() on each PostgreSQL variant is necessary to get valid hex colours: to see why, convert (5,189,94) or (0,204,153) without lpad-ing, and put the results (#5BDC2 and #0CC99/#CC99 respectively) into a colour checker.

The upper() is just my preference for all-caps in colour codes; the Python eval() above gives allcaps.

Now the question: is there a more-parsimonious way to do this in PostgreSQL without just writing a function that implements one of the two variants I've outlined above?

Making it a function would be straightforward, but if there's already native functionality (either in-query or a more parsimonious way to code it as a function) it would be useful to know.

Environment: PostgreSQL 9.3.5 (Windows).

Adam · Accepted Answer · 2016-07-12 00:46:18Z

4

SELECT
    '#' || lpad(upper(to_hex((R << 16) | (G << 8) | B)), 6, '0')
FROM (
    SELECT
        1 AS R,
        28 AS G,
        123 AS B
    ) AS RGB;

No colon used ;) But I have PostgreSQL 9.5, I hope it works on 9.3.5 too.

edited Jul 12, 2016 at 0:46

answered Jul 12, 2016 at 0:24

Adam

5,6296 gold badges35 silver badges41 bronze badges

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

2 Comments

e4c5 Over a year ago

Just as I finished typing my own answer i see this :)

GT. Over a year ago

@Adam thanks. D'oh! I should have thought to check the documentation for bitwise shift operators - I use those quite a bit in Python and the syntax is the same (like PHP: the syntax for Octave and R is way different). I can confirm that the solution works on 9.3.5 too.

Collectives™ on Stack Overflow

PostgreSQL: tidier conversion of numeric to 2-digit hex

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related