SET DECIMALSEPARATORCOMMA
The PSQL database engine by default displays decimal data using a period (.) as the separator between ones and tenths (for example, 100.95). The SET DECIMALSEPARATORCOMMA statement allows you to specify that results should be displayed using a comma to separate ones and tenths (for example, 100,95).
As with all SET statements, the effects of this statement apply to the remainder of the current database session, or until another SET DECIMALSEPARATORCOMMA statement is issued.
Syntax
SET DECIMALSEPARATORCOMMA=<ON|OFF>
Remarks
The default value is OFF, meaning that the period is used as the default decimal separator.
In locales where the comma is used as the decimal separator, decimal data can be entered using a comma or a period as the separator (literal values that use the comma as the separator must be enclosed in single quotes, for example: ‘123,43’). When the data is returned, however (as in the results of a SELECT statement), it is always displayed using a period unless SET DECIMALSEPARATORCOMMA=ON has been specified.
Likewise, if your database contains data that was entered using the period as the decimal separator, you can choose to specify the comma as the separator for output and display by using this statement.
This command affects output and display only. It has no effect on values being inserted, updated, or used in a comparison.
Examples
The following example shows how to insert period-delimited data and the effects of the SET DECIMALSEPARATORCOMMA statement on the SELECT results.
CREATE TABLE t1 (c1 real, c2 real)
INSERT INTO t1 VALUES (102.34, 95.234)
SELECT * FROM t1
Results:
c1 c2
------- -------
102.34 95.234
SET DECIMALSEPARATORCOMMA=ON
SELECT * FROM t1
Results:
c1 c2
------- -------
102,34 95,234
============
The following example shows how to insert comma-delimited data, and the effects of the SET DECIMALSEPARATORCOMMA statement on the SELECT results.
Note The comma can only be used as the separator character if the client and/or server operating system locale settings are set to a locale that uses the comma as the separator. For example, if you have U.S. locale settings on both your client and server, you will receive an error if you attempt to run this example.
CREATE TABLE t1 (c1 real, c2 real)
INSERT INTO t1 VALUES ('102,34', '95,234')
SELECT * FROM t1
Results:
c1 c2
------- -------
102.34 95.234
SET DECIMALSEPARATORCOMMA=ON
SELECT * FROM t1
Results:
c1 c2
------- -------
102,34 95,234
See Also