Set extra_float_digits=3 everywhere in postgres

Question

Postgres has a setting called extra_float_digits. It determines how many digits are returned when you select a floating point number in the database. It defaults to 0 and only applies to the current connection. If it isn't set to 3, information is lost when you select a value you previously inserted.

Is there a way to set this value so that it will default to 3 in all environments? I would like it to be set to 3 when I connect via psql on the commandline and also when my application connects via a database driver.

Answer 1

Solution #1

Available since PostgreSQL 9.4. Run as an SQL statement when connected as superuser:

ALTER SYSTEM SET extra_float_digits TO 3; 

(followed by select pg_reload_conf(); if it needs take effect immediately)

Solution #2

Edit the instance-wide postgresql.conf configuration file. Locate the existing extra_float_digits setting and change it to:

extra_float_digits = 3 

As in the previous solution, a configuration reload is necessary. Be aware that what's set through ALTER SYSTEM takes precedence over what's set in postgresql.conf.

Solution #3

Assign the configuration to specific users (any connection by this user will have this setting by default). Does not require to be superuser if you can connect with that user.

ALTER USER username SET extra_float_digits TO 3; 

Solution #4

Assign the configuration to specific databases (any connection to this database will have this setting by default). Does not require to be superuser if you're the owner of the database.

ALTER DATABASE dbname SET extra_float_digits TO 3;