ALTER SESSION

Description

ALTER SESSION defines or modifies the conditions or parameters that affect the current session. Modified session parameters are kept until the current session is disconnected.

Precautions

If the START TRANSACTION statement is not executed before the SET TRANSACTION statement, the transaction is ended instantly and the statement does not take effect.
You can use the transaction_mode(s) method declared in the START TRANSACTION statement to avoid using the SET TRANSACTION statement. For details, see START TRANSACTION.

Syntax

Set transaction parameters of a session.

ALTER SESSION SET TRANSACTION
    { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE} } | { READ ONLY  | READ WRITE } } [, ...] ;

Set other GUC parameters of a session.

ALTER SESSION SET 
    {{config_parameter { { TO  | =  }  { value | DEFAULT } | FROM CURRENT }} 
      | TIME ZONE time_zone
      | CURRENT_SCHEMA schema
      | SCHEMA 'schema'
      | NAMES encoding_name
      | NAMES encoding_name COLLATE collate_name
      | ROLE role_name PASSWORD 'password'
      | SESSION AUTHORIZATION { role_name PASSWORD 'password' | DEFAULT }
    } ;

Parameters

config_parameter
Specifies the name of a configurable GUC parameter. You can use SHOW ALL to view available GUC parameters.
- value
  Specifies the new value of config_parameter. You can specify the parameter as a string constant, identifier, number, or comma-separated list. DEFAULT is used to set default values for parameters.
  - DEFAULT
  - OFF
  - RESET
  - User-specified value: The value must meet the restriction of the modified parameter.
- FROM CURRENT
  Uses the value in the current session as the value of configuration_parameter.

TIME ZONE timezone
Specifies the local time zone for the current session.

Value range: a valid local time zone. The corresponding GUC parameter is TimeZone. The default value is PRC.
CURRENT_SCHEMA schema
Specifies the current schema.

Value range: an existing schema name If the schema name does not exist, the value of CURRENT_SCHEMA will be empty.
SCHEMA 'schema'
Specifies the current schema. The schema here is a string.
NAMES encoding_name
Specifies the client character encoding name. This statement is equivalent to set client_encoding to encoding_name.

Value range: a valid character encoding name. The GUC parameter corresponding to this option is client_encoding. The default encoding is UTF8.
ROLE role_name
Specifies the value range of role_name. For details about the role name requirements, see •role_name.
PASSWORD 'password'
password indicates the password of the role, which must comply with the naming rules.
SESSION AUTHORIZATION
Sets the session user identifier of the current session.
ISOLATION LEVEL
Specifies a transaction isolation level, which determines what data is visible to a transaction if there are other concurrent transactions.

Value range:
- READ COMMITTED: Only committed data can be read. This is the default value.
- READ UNCOMMITTED: After this isolation level is set, its behavior is the same as that of READ COMMITTED.

Examples

-- Create the ds schema.
m_db=# CREATE SCHEMA ds;

-- Set the search path of a schema.
m_db=# SET SEARCH_PATH TO ds, public;

-- Set the time/date type to the traditional postgres format (date before month).
m_db=# SET DATESTYLE TO postgres, dmy;

-- Set the character code of the current session to UTF8.
m_db=# ALTER SESSION SET NAMES 'UTF8';

-- Set the time zone to Berkeley of California.
m_db=# SET TIME ZONE 'PST8PDT';

-- Set the time zone to Italy.
m_db=# SET TIME ZONE 'Europe/Rome';

-- Set the current schema.
m_db=# ALTER SESSION SET CURRENT_SCHEMA TO ds;
-- Delete the ds schema.
m_db=# DROP SCHEMA ds;
-- Start a transaction and set the transaction level.
m_db=# START TRANSACTION;
m_db=# ALTER SESSION SET TRANSACTION READ ONLY;
m_db=# ROLLBACK;