Updated on 2022-06-11 GMT+08:00

ALTER SESSION

Function

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 command is not executed before the SET TRANSACTION command, the transaction is ended instantly and the command does not take effect.
  • You can use the transaction_mode(s) method declared in the START TRANSACTION command to avoid using the SET TRANSACTION command.

Syntax

  • Set transaction parameters of a session.
    1
    2
    ALTER SESSION SET [ SESSION CHARACTERISTICS AS ] TRANSACTION
        { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED } | { READ ONLY  | READ WRITE } } [, ...] ;
    
  • Set other running parameters of a session.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    ALTER SESSION SET 
        {{config_parameter { { TO  | =  }  { value | DEFAULT }
          | FROM CURRENT }} | CURRENT_SCHEMA [ TO | = ] { schema | DEFAULT }
          | TIME ZONE time_zone
          | SCHEMA schema
          | NAMES encoding_name
          | ROLE role_name PASSWORD 'password'
          | SESSION AUTHORIZATION { role_name PASSWORD 'password' | DEFAULT }
          | XML OPTION { DOCUMENT | CONTENT }
        } ;
    

Parameter Description

To modify the description of parameters related to the session, see Parameter Description of the SET syntax.

Examples

Create the ds schema.

CREATE SCHEMA ds;

Set the search path of the schema.

SET SEARCH_PATH TO ds, public;

Set the time/date type to the traditional postgres format (date before month).

SET DATESTYLE TO postgres, dmy;

Set the character code of the current session to UTF8.

ALTER SESSION SET NAMES 'UTF8';

Set the time zone to Berkeley of California.

SET TIME ZONE 'PST8PDT';

Set the time zone to Italy.

SET TIME ZONE 'Europe/Rome';

Set the current schema.

ALTER SESSION SET CURRENT_SCHEMA TO tpcds;

Set XML OPTION to DOCUMENT.

ALTER SESSION SET XML OPTION DOCUMENT;

Create the role joe, and set the session role to omm.

CREATE ROLE joe WITH PASSWORD 'password';
ALTER SESSION SET SESSION AUTHORIZATION joe PASSWORD 'password';

Switch to the default user.

ALTER SESSION SET SESSION AUTHORIZATION default;

Helpful Links

SET