SET, SHOW, and RESET

 The SET command allows you to change various POSTGRESQL parameters. The changes remain in effect for the duration of the database connection. Table shows two common parameters that can be controlled with SET.

 

Table: SET options

Function	SET option
DATESTYLE	DATESTYLE TO 'ISO''POSTGRES''SQL''US'
	'NONEUROPEAN''EUROPEAN''GERMAN'
TIMEZONE	TIMEZONE TO 'value'

 The SET DATESTYLE command controls the appearance of dates when printed in psql, as seen in Table .

 

Table: DATESTYLE output

		Output for
Style	Optional Ordering	February 1, 1983
ISO		1983-02-01
POSTGRES	US or NONEUROPEAN	02-01-1983
POSTGRES	EUROPEAN	01-02-1983
SQL	US or NONEUROPEAN	02/01/1983
SQL	EUROPEAN	01/02/1983
German		01.02.1983

It controls the format (slashes, dashes, or year first) and the display of the month first (US) or day first (European). The command SET DATESTYLE TO 'SQL,US' would most likely be selected by users in the United States, while Europeans might prefer SET DATESTYLE TO 'POSTGRES,EUROPEAN'. The ISO datestyle and GERMAN datestyle are not affected by any of the other options. 

 The TIMEZONE defaults to the time zone of the server or the PGTZ environment variable. The psql client might be in a different time zone, so SET TIMEZONE allows this parameter to be changed inside psql. See the SET manual page for a full list of SET options. 

The SHOW command is used to display the current database session parameters. The RESET command allows a session parameter to be reset to its default value. Figure  shows examples of these commands.^9.5   

 

        test=> SHOW DATESTYLE;
        NOTICE: DateStyle is ISO with US (NonEuropean) conventions
        SHOW VARIABLE
        test=> SET DATESTYLE TO 'SQL, EUROPEAN';
        SET VARIABLE
        test=> SHOW DATESTYLE;
        NOTICE:  DateStyle is SQL with European conventions
        SHOW VARIABLE
        test=> RESET DATESTYLE;
        RESET VARIABLE
        test=> SHOW DATESTYLE;
        NOTICE:  DateStyle is ISO with US (NonEuropean) conventions
        SHOW VARIABLE