CODEWITHSUNDEEP
postgresql
GT.
GT.

Reputation: 1170

PostgreSQL: tidier conversion of numeric to 2-digit hex

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).

Upvotes: 2

Views: 616

Answers (1)

Adam
Adam

Reputation: 5599

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.

Upvotes: 4

Related Questions