In PostgreSQL, you can use the to_char() to output a number in a certain format, including with a plus or minus sign to indicate whether it’s a positive or negative number.

To do this, use one of S, MI, PL, or SG in your format string.

Another option is to use PR in order to enclose any negative values in angle brackets.

Template Patterns for Signed Numbers

The following template patterns can be used to apply the appropriate sign to the number.

Pattern	Description
MI	Minus sign in specified position (if number < 0).
PL	Plus sign in specified position (if number > 0).
SG	Plus or minus sign in specified position, depending on whether the number is positive or negative.
S	Sign anchored to number (uses locale).
PR	This encloses any negative values inside angle brackets.

Note that MI, PL, or SG are Postgres extensions (they’re not standard SQL).

Example using S

Here’s an example to demonstrate the S pattern.

SELECT to_char(1, 'S9');

Result:

+1

In this case, the number is positive and so using my locale, the plus sign is prepended to it.

Here it is again, but with three values; a positive, a negative, and zero.

SELECT 
  to_char(1, 'S9') AS "1",
  to_char(-1, 'S9') AS "-1",
  to_char(0, 'S9') AS "0";

Result:

 1  | -1 | 0  
----+----+----
 +1 | -1 | +0

Example using SG

Here’s the same example with the SG pattern.

SELECT 
  to_char(1, 'SG9') AS "1",
  to_char(-1, 'SG9') AS "-1",
  to_char(0, 'SG9') AS "0";

Result:

 1  | -1 | 0  
----+----+----
 +1 | -1 | +0

Example using MI

Here’s what happens if I swap SG with MI.

SELECT 
  to_char(1, 'MI9') AS "1",
  to_char(-1, 'MI9') AS "-1",
  to_char(0, 'MI9') AS "0";

Result:

 1  | -1 | 0  
----+----+----
  1 | -1 |  0

Only the negative number gets the minus sign. The positive number and zero don’t get any sign.

Example using PL

Here’s the output on my system when I use PL.

SELECT 
  to_char(1, 'PL9') AS "1",
  to_char(-1, 'PL9') AS "-1",
  to_char(0, 'PL9') AS "0";

Result:

  1  | -1  |  0  
-----+-----+-----
 + 1 |  -1 | + 0

Example using PR

Here’s the output on my system when I use PR.

SELECT 
  to_char(1, '9PR') AS "1",
  to_char(-1, '9PR') AS "-1",
  to_char(0, '9PR') AS "0";

Result:

  1  | -1  |  0  
-----+-----+-----
  1  | <1> |  0 

Note that PR must come after 9.

Here’s what happens if I try to put it before 9:

SELECT 
  to_char(1, 'PR9') AS "1",
  to_char(-1, 'PR9') AS "-1",
  to_char(0, 'PR9') AS "0";

Result:

ERROR:  "9" must be ahead of "PR"

SG vs S

You might have noticed that the examples using SG and S appear to output the same result, and therefore wonder what the difference is between them.

The difference is that S is anchored to the number whereas SG, MI, PL are not.

S also uses the locale, so the actual sign used will depend on your locale.

Here’s an example that demonstrates the anchoring difference.

SELECT 
  to_char(1, 'S999') AS "S",
  to_char(1, 'SG999') AS "SG";

Result:

  S   |  SG  
------+------
   +1 | +  1

And here’s what happens as the number grows larger.

SELECT 
  to_char(1, 'S999') AS "S",
  to_char(1, 'SG999') AS "SG",
  to_char(10, 'S999') AS "S",
  to_char(10, 'SG999') AS "SG",
  to_char(100, 'S999') AS "S",
  to_char(100, 'SG999') AS "SG";

Result:

  S   |  SG  |  S   |  SG  |  S   |  SG  
------+------+------+------+------+------
   +1 | +  1 |  +10 | + 10 | +100 | +100