PostgreSQL: how do I format the output of a query to show 'million' instead of zeros?

Question

How do I format the output of a query to display a value in number of millions (i.e 'million' appended. e.g: 1 million instead of 1000000) using psql?

Example:

SELECT city, population
FROM cities
WHERE state = 'California';

Actual Output:

     city      | population 
---------------+--------------------------
 Los Angeles   |                  3990456
 San Diego     |                  1425976
 San Jose      |                  1030119

Desired Output:

     city      | population 
---------------+--------------------------
 Los Angeles   |             3.99 million
 San Diego     |             1.43 million
 San Jose      |             1.03 million

All I could find on the topic is functions for data formatting for converting numbers/dates to strings and vice-versa: https://www.docs4dev.com/docs/en/postgre-sql/11.2/reference/functions-formatting.html

Also, the to_char function does not seem to perform this sort of formatting: https://www.postgresqltutorial.com/postgresql-to_char/

Using psql and PostgreSQL version 13 on macOS terminal.

score 1 · Accepted Answer · answered Jan 13 '21 at 05:14

There is not any build functionality for your purpose. You should to write own custom function:

CREATE OR REPLACE FUNCTION format_mil(n int)
RETURNS text AS $$
BEGIN
  IF n > 500000 THEN
    RETURN (n / 1000000.0)::numeric(10,2) || ' million';
  ELSE
    RETURN n::text;
  END IF;
END;
$$ LANGUAGE plpgsql;

postgres=# select format_mil(3990456);
┌──────────────┐
│  format_mil  │
╞══════════════╡
│ 3.99 million │
└──────────────┘
(1 row)

But simply SQL expression can be good enough too:

CREATE TABLE cities (population int);
INSERT INTO cities VALUES(3990456);
INSERT INTO cities VALUES(1425976);

postgres=# SELECT CASE WHEN population > 500000 THEN
                          (population/1000000.0)::numeric(10,2) || ' million'
                    ELSE population::text END 
              FROM cities;
┌──────────────┐
│  population  │
╞══════════════╡
│ 3.99 million │
│ 1.43 million │
└──────────────┘
(2 rows)

PostgreSQL: how do I format the output of a query to show 'million' instead of zeros?

1 Answers1