SET statements

• 1: SET DATESTYLE
• 2: SET ESCAPE_STRING_WARNING
• 3: SET INTERVALSTYLE
• 4: SET LOCALE
• 5: SET ROLE
• 6: SET SEARCH_PATH
• 7: SET SESSION AUTHORIZATION
• 8: SET SESSION AUTOCOMMIT
• 9: SET SESSION CHARACTERISTICS AS TRANSACTION
• 10: SET SESSION GRACEPERIOD
• 11: SET SESSION IDLESESSIONTIMEOUT
• 12: SET SESSION MEMORYCAP
• 13: SET SESSION MULTIPLEACTIVERESULTSETS
• 14: SET SESSION RESOURCE_POOL
• 15: SET SESSION RUNTIMECAP
• 16: SET SESSION TEMPSPACECAP
• 17: SET SESSION WORKLOAD
• 18: SET STANDARD_CONFORMING_STRINGS
• 19: SET TIME ZONE
• 19.1: Time zone names for setting TIME ZONE

1 - SET DATESTYLE

Specifies how to format date/time output for the current session. Use SHOW DATESTYLE to verify the current output settings.

Syntax

SET DATESTYLE TO { arg | 'arg' }[, arg | 'arg' ]

Parameters

SET DATESTYLE has a single parameter, which can be set to one or two arguments that specify date ordering and style. Each argument can be specified singly or in combination with the other; if combined, they can be specified in any order.

The following table describes each style and the date ordering arguments it supports:

Vertica ignores the order argument for date styles ISO and GERMAN. If the date style is SQL or POSTGRES, the order setting determines whether dates are output in MDY or DMY order. Neither SQL nor POSTGRES support YMD order. If you specify YMD for SQL or POSTGRES, Vertica ignores it and uses their default MDY order.

Date styles and ordering can also affect how Vertica interprets input values. For more information, see Date/time literals.

Privileges

None

Input dependencies

In some cases, input format can determine output, regardless of date style and order settings:

• Vertica ISO output for DATESTYLE is ISO long form, but several input styles are accepted. If the year appears first in the input, YMD is used for input and output, regardless of the DATESTYLE value.

• INTERVAL input and output share the same format, with the following exceptions:

  • Units like CENTURY or WEEK are converted to years and days.

  • AGO is converted to the appropriate sign.

  If the date style is set to ISO, output follows this format:

  [ quantity unit [...] ] [ days ] [ hours:minutes:seconds ]
  

Examples

=> CREATE TABLE t(a DATETIME);
CREATE TABLE
=> INSERT INTO t values ('3/16/2016');
 OUTPUT
--------
      1
(1 row)

=> SHOW DATESTYLE;
   name    | setting
-----------+----------
 datestyle | ISO, MDY
(1 row)

=> SELECT * FROM t;
          a
---------------------
 2016-03-16 00:00:00
(1 row)

=> SET DATESTYLE TO German;
SET
=> SHOW DATESTYLE;
   name    |   setting
-----------+-------------
 datestyle | German, DMY
(1 row)

=> SELECT * FROM t;
          a
---------------------
 16.03.2016 00:00:00
(1 row)

=> SET DATESTYLE TO SQL;
SET
=> SHOW DATESTYLE;
   name    | setting
-----------+----------
 datestyle | SQL, DMY
(1 row)

=> SELECT * FROM t;
          a
---------------------
 16/03/2016 00:00:00
(1 row)

=> SET DATESTYLE TO Postgres, MDY;
SET
=> SHOW DATESTYLE;
   name    |    setting
-----------+---------------
 datestyle | Postgres, MDY
(1 row)

=> SELECT * FROM t;
            a
--------------------------
 Wed Mar 16 00:00:00 2016
(1 row)

2 - SET ESCAPE_STRING_WARNING

Issues a warning when a backslash is used in a string literal during the current session.

Syntax

SET ESCAPE_STRING_WARNING TO { ON | OFF }

Parameters

ON

[Default] Issues a warning when a back slash is used in a string literal.

Tip: Organizations that have upgraded from earlier versions of Vertica can use this as a debugging tool for locating backslashes that used to be treated as escape characters, but are now treated as literals.

OFF

Ignores back slashes within string literals.

Privileges

None

Notes

• This statement works under vsql only.

• Turn off standard conforming strings before you turn on this parameter.

Examples

The following example shows how to turn OFF escape string warnings for the session.

=> SET ESCAPE_STRING_WARNING TO OFF;

See also

• SET STANDARD_CONFORMING_STRINGS

3 - SET INTERVALSTYLE

Specifies whether to include units in interval output for the current session.

Syntax

SET INTERVALSTYLE TO [ plain | units ]

Parameters

plain

(default) Sets the default interval output to omit units.

units

Enables interval output to include subtype unit identifiers. When INTERVALSTYLE is set to units, the DATESTYLE parameter controls output. If you enable units and they do not display in the output, check the DATESTYLE parameter value, which must be set to ISO or POSTGRES for interval units to display.

Privileges

None

Examples

See Setting interval unit display.

4 - SET LOCALE

Specifies locale for the current session.

You can also set the current locale with the vsql command \locale.

Syntax

SET LOCALE TO ICU-locale-identifier

Parameters

locale-identifier

Specifies the ICU locale identifier to use, by default set to:

en_US@collation=binary

If set to an empty string, Vertica sets locale to en_US_POSIX.

The following requirements apply:

• Vertica only supports the COLLATION keyword.

• Single quotes are mandatory to specify collation.

Privileges

None

Commonly used locales

For details on identifier options, see About locale. For a complete list of locale identifiers, see the ICU Project.

de_DE

German (Germany)

en_GB

English (Great Britain)

es_ES

Spanish (Spain)

fr_FR

French (France)

pt_BR

Portuguese (Brazil)

pt_PT

Portuguese (Portugal)

ru_RU

Russian (Russia)

ja_JP

Japanese (Japan)

zh_CN

Chinese (China, simplified Han)

zh_Hant_TW

Chinese (Taiwan, traditional Han)

Examples

Set session locale to en_GB:

=> SET LOCALE TO en_GB;
INFO 2567:  Canonical locale: 'en_GB'
Standard collation: 'LEN'
English (United Kingdom)
SET

Use the short form of a locale:

=> SET LOCALE TO LEN;
INFO 2567:  Canonical locale: 'en'
Standard collation: 'LEN'
English
SET

Specify collation:

=> SET LOCALE TO 'tr_tr@collation=standard';
INFO 2567:  Canonical locale: 'tr_TR@collation=standard'
Standard collation: 'LTR'
Turkish (Turkey, collation=standard)  Türkçe (Türkiye, Sıralama=standard)
SET

See also

• Implement locales for international data sets
• About locale

5 - SET ROLE

Enables a role for the user's current session. The user can access privileges that have been granted to the role. Enabling a role has no effect on roles that are currently enabled.

Syntax

SET ROLE roles-expression

Parameters

roles-expression

Specifies what roles are the default roles for this user, with one of the following expressions:

• NONE (default): Disables all roles.

• roles-list: A comma-delimited list of roles to enable. You can only set roles that are currently granted to you.

• ALL [EXCEPT roles-list]: Enables all roles currently granted to this user, excluding any comma-delimited roles specified in the optional EXCEPT clause.

• DEFAULT: Enables all default roles. Default roles are, by definition, enabled automatically, but this option might be useful for re-enabling them if they are disabled with SET ROLE NONE.

Privileges

None

Examples

This example shows the following:

• SHOW AVAILABLE_ROLES; lists the roles available to the user, but not enabled.

• SET ROLE applogs; enables the applogs role for the user.

• SHOW ENABLED_ROLES; lists the applogs role as enabled (SET) for the user.

• SET ROLE appuser; enables the appuser role for the user.

• SHOW ENABLED_ROLES now lists both applogs and appuser as enabled roles for the user.

• SET ROLE NONE disables all the users' enabled roles .

• SHOW ENABLED_ROLES shows that no roles are enabled for the user.

=> SHOW AVAILABLE_ROLES;
      name       |          setting
-----------------+----------------------------
 available roles | applogs, appadmin, appuser
(1 row)

=> SET ROLE applogs;
SET
=> SHOW ENABLED_ROLES;
     name      | setting
---------------+---------
 enabled roles | applogs
(1 row)

=> SET ROLE appuser;
SET
=> SHOW ENABLED_ROLES;
     name      |     setting
---------------+------------------
 enabled roles | applogs, appuser
(1 row)

=> SET ROLE NONE;
SET


=> SHOW ENABLED_ROLES;
     name      | setting
---------------+---------
 enabled roles |
(1 row)

Set User Default Roles

Though the DBADMIN user is normally responsible for setting a user's default roles, as a user you can set your own role. For example, if you run SET ROLE NONE all of your enabled roles are disabled. Then it was determined you need access to role1 as a default role. The DBADMIN uses ALTER USER to assign you a default role:

=> ALTER USER user1 default role role1;

This example sets role1 as user1's default role because the DBADMIN assigned this default role using ALTER USER.

user1 => SET ROLE default;
user1 => SHOW ENABLED_ROLES;
    name      | setting
-----------------------
enabled roles |   role1
(1 row)

Set All Roles as Default

This example makes all roles granted to user1 default roles:

user1 => SET ROLE all;
user1 => show enabled roles;
    name      |   setting
----------------------------------
enabled roles | role1, role2, role3
(1 row)

Set All Roles as Default With EXCEPT

This example makes all the roles granted to the user default roles with the exception of role1.

user1 => set role all except role1;
user1 => SHOW ENABLED_ROLES
     name     | setting
----------------------------
enabled roles | role2, role3
(1 row)

6 - SET SEARCH_PATH

Specifies the order in which Vertica searches schemas when a SQL statement specifies a table name that is unqualified by a schema name. SET SEARCH_PATH overrides the current session's search path, which is initially set from the user profile. This search path remains in effect until the next SET SEARCH_PATH statement, or the session ends. For details, see Setting search paths.

To view the current search path, use SHOW SEARCH_PATH.

Syntax

SET SEARCH_PATH { TO | = } { schema-list | DEFAULT }

Parameters

schema-list

A comma-delimited list of schemas that indicates the order in which Vertica searches schemas for a table whose name is unqualified by a schema name.

If the search path includes a schema that does not exist, or for which the user lacks access privileges, Vertica silently skips over that schema.

DEFAULT

Sets the search path to the database default:

"$user", public, v_catalog, v_monitor, v_internal

Privileges

None

Examples

Show the current search path:

=> SHOW SEARCH_PATH;
    name     |                      setting
-------------+---------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

Reset the search path to schemas store and public:

=> SET SEARCH_PATH TO store, public;
=> SHOW SEARCH_PATH;
    name     |                     setting
-------------+-------------------------------------------------
 search_path | store, public, v_catalog, v_monitor, v_internal
(1 row)

Reset the search path to the database default settings:

=> SET SEARCH_PATH TO DEFAULT;
SET
=> SHOW SEARCH_PATH;
    name     |                      setting
-------------+---------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

7 - SET SESSION AUTHORIZATION

Sets the current and session user for the current database connection. You can change session authorization to execute queries as another user for testing or debugging purposes, or to limit query access.

Syntax

SET SESSION AUTHORIZATION { username | DEFAULT }

Parameters

username

The name of the user that you want to authorize for the current SQL session.

DEFAULT

Sets session authorization to the dbadmin user.

Privileges

Superuser

Examples

In the following example, the dbadmin gives the debuguser user session authorization, and then changes the session authorization back to the dbadmin user.

1. Verify the current user and session user:

   => SELECT CURRENT_USER(), SESSION_USER();
    current_user | session_user
   --------------+--------------
    dbadmin      | dbadmin
   (1 row)
   

2. Set authorization for the current session to debuguser, and verify the changes:

   => SET SESSION AUTHORIZATION debuguser;
   SET
   => SELECT CURRENT_USER(), SESSION_USER();
    current_user | session_user
   --------------+--------------
    debuguser    | debuguser
   (1 row)
   

3. After you complete debugging tasks, set the session authorization to DEFAULT to set the current and session user back to dbadmin user, and verify the changes:

   => SET SESSION AUTHORIZATION DEFAULT;
   SET
   => SELECT CURRENT_USER(), SESSION_USER();
    current_user | session_user
   --------------+--------------
    dbadmin      | dbadmin
   (1 row)
   

8 - SET SESSION AUTOCOMMIT

Sets whether statements automatically commit their transactions on completion. This statement is primarily used by the client drivers to enable and disable autocommit, you should never have to directly call it.

Syntax

SET SESSION AUTOCOMMIT TO { ON | OFF }

Parameters

ON

Enable autocommit. Statements automatically commit their transactions when they complete. This is the default setting for connections made using the Vertica client libraries.

OFF

Disable autocommit. Transactions are not automatically committed. This is the default for interactive sessions (connections made through vsql).

Privileges

None

Examples

This examples show how to set AUTOCOMMIT to 'on' and then to 'off'.

=> SET SESSION AUTOCOMMIT TO on;
SET
=> SET SESSION AUTOCOMMIT TO off;
SET

See also

• Client libraries

9 - SET SESSION CHARACTERISTICS AS TRANSACTION

Sets the isolation level and access mode of all transactions that start after this statement is issued.

A transaction retains its isolation level until it completes, even if the session's isolation level changes during the transaction. Vertica internal processes (such as the Tuple Mover and refresh operations) and DDL operations always run at the SERIALIZABLE isolation level to ensure consistency.

Syntax

SET SESSION CHARACTERISTICS AS TRANSACTION settings

settings

One or both of the following:

ISOLATION LEVEL argument

• READ ONLY | READ WRITE

ISOLATION LEVEL arguments

The ISOLATION LEVEL clause determines what data the transaction can access when other transactions run concurrently. You cannot change the isolation level after the first query (SELECT) or DML statement (INSERT, DELETE, UPDATE) if a transaction has run.

Set ISOLATION LEVEL to one of the following arguments:

SERIALIZABLE

Sets the strictest level of SQL transaction isolation. This level emulates transactions serially, rather than concurrently. It holds locks and blocks write operations until the transaction completes.

Applications that use SERIALIZABLE must be prepared to retry transactions in the event of serialization failures. This isolation level is not recommended for normal query operations.

Setting the transaction isolation level to SERIALIZABLE does not apply to temporary tables. Temporary tables are isolated by their transaction scope.

REPEATABLE READ

Automatically converted to SERIALIZABLE.

READ COMMITTED

Default, allows concurrent transactions.

READ UNCOMMITTED

Automatically converted to READ COMMITTED.

READ WRITE/READ ONLY

You can set the transaction access mode with one of the following:

READ WRITE

Default, allows read/write access to SQL statements.

READ ONLY

Disallows SQL statements that require write access:

• INSERT, UPDATE, DELETE, and COPY operations on any non-temporary table.

• CREATE, ALTER, and DROP

• GRANT, REVOKE

• EXPLAIN if the SQL statement to explain requires write access.

Note

Setting the transaction session mode to read-only does not prevent all write operations.

Privileges

None

Viewing session transaction characteristics

SHOW TRANSACTION_ISOLATION and SHOW TRANSACTION_READ_ONLY show the transaction settings for the current session:

=> SHOW TRANSACTION_ISOLATION;
         name          |   setting
-----------------------+--------------
 transaction_isolation | SERIALIZABLE
(1 row)

=> SHOW TRANSACTION_READ_ONLY;
         name          | setting
-----------------------+---------
 transaction_read_only | true
(1 row)

10 - SET SESSION GRACEPERIOD

Sets how long a session socket remains blocked while awaiting client input or output for a given query. If the socket is blocked for a continuous period that exceeds the grace period setting, the server shuts down the socket and throws a fatal error. The session is then terminated. If no grace period is set, the query can maintain its block on the socket indefinitely.

Vertica applies a session's grace period and RUNTIMECAP settings independently. If no grace period is set, a query can continue to block indefinitely on a session socket, regardless of the query's RUNTIMECAP setting.

Syntax

SET SESSION GRACEPERIOD duration

Parameters

duration

Specifies how long a query can block on any session socket, one of the following:

• 'interval': Specifies as an interval the maximum grace period for current session queries, up to 20 days.

• =DEFAULT: Sets the grace period for queries in this session to the user's GRACEPERIOD value. A new session is initially set to this value.

• NONE: Valid only for superusers, removes any grace period previously set on session queries.

Privileges

• Superusers can increase session grace period to any value, regardless of database or node settings.

• Non-superusers can only set the session grace period to a value equal to or lower than their own user setting. If no grace period is explicitly set for a user, the grace period for that user is inherited from the node or database settings.

Examples

See Handling session socket blocking in the Administrator's Guide.

11 - SET SESSION IDLESESSIONTIMEOUT

Sets the maximum amount of time that a session can remain idle before it exits.

Syntax

SET SESSION IDLESESSIONTIMEOUT duration

Parameters

duration

Specifies the amount of time a session can remain idle before it exits:

• NONE (default): No idle timeout set on the session.

• 'interval': Specifies as an interval the maximum amount of time a session can remain idle.

• =DEFAULT: Sets the idle timeout period for this session to the user's IDLESESSIONTIMEOUT value.

Privileges

• Superusers can increase the time a session can remain idle to any value, regardless of database or node settings.

• Non-superusers can only set the session idle time to a value equal to or lower than their own user setting. If no session idle time is explicitly set for a user, the session idle time for that user is inherited from the node or database settings.

Examples

See Managing client connections in the Administrator's Guide.

12 - SET SESSION MEMORYCAP

Limits how much memory can be allocated to any request in the current session. This limit only applies to the current session; it does not limit the total amount of memory used by multiple sessions.

Syntax

SET SESSION MEMORYCAP limit

Parameters

limit

One of the following:

• 'max-expression': A string value that specifies the memory limit, one of the following:

  • int% — Expresses the maximum as a percentage of total memory available to the Resource manager, where int is an integer value between 0 and 100.For example:

    MEMORYCAP '40%'

  • int{K|M|G|T} — Expresses memory allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

    MEMORYCAP '10G'

• =DEFAULT: Sets the memory cap for queries in this session to the user's MEMORYCAP value.A new session is initially set to this value.

• NONE: Removes the memory cap for this session.

Privileges

• Superusers can increase session memory cap to any value.

• Non-superusers can only set the session memory cap to a value equal to or lower than their own user setting.

Examples

Set the session memory cap to 2 gigabytes:

=> SET SESSION MEMORYCAP '2G';
SET
=> SHOW MEMORYCAP;
   name    | setting
-----------+---------
 memorycap | 2097152
(1 row)

Revert the memory cap to the default setting as specified in the user profile:

=> SET MEMORYCAP=DEFAULT;
SET
=> SHOW MEMORYCAP;
   name    | setting
-----------+---------
 memorycap | 2013336
(1 row)

See also

13 - SET SESSION MULTIPLEACTIVERESULTSETS

Enables or disable the execution of multiple active result sets (MARS) on a single JDBC connection. Using this option requires an active JDBC connection.

Syntax

SET SESSION MULTIPLEACTIVERESULTSETS TO { ON | OFF }

Parameters

ON

Enable MultipleActiveResultSets.Allows you to execute multiple result sets on a single connection.

OFF

Disable MultipleActiveResultSets. Allows only one active result set per connection.(Default value.)

Privileges

None

Examples

This example shows how you can set MultipleActiveResultSets to on and then to off:

=> SET SESSION MULTIPLEACTIVERESULTSETS TO on;
SET
=> SET SESSION MULTIPLEACTIVERESULTSETS TO off;
SET

14 - SET SESSION RESOURCE_POOL

Associates the user session with the specified resource pool.

Syntax

SET SESSION RESOURCE_POOL = { pool-name | DEFAULT }

Parameters

pool-name

The name of an existing resource pool to associate with the current session. Non-superusers must have USAGE privileges on the specified resource pool.

DEFAULT

Sets the session's resource pool to the resource pool assigned to this user.

Privileges

None

Examples

This example sets ceo_pool as the session resource pool:

=> SET SESSION RESOURCE_POOL = ceo_pool;
SET

See also

• ALTER RESOURCE POOL
• CREATE RESOURCE POOL
• CREATE USER
• DROP RESOURCE POOL
• GRANT (Resource pool)
• SET SESSION MEMORYCAP
• Managing workloads

15 - SET SESSION RUNTIMECAP

Sets the maximum amount of time queries and stored procedures can run in a given session. If a query or stored procedure exceeds its session's RUNTIMECAP, Vertica terminates it and returns an error. You cannot increase the RUNTIMECAP beyond the limit that is set in your user profile.

Syntax

SET SESSION RUNTIMECAP duration

Parameters

duration

Specifies how long a given query can run in the current session, one of the following:

• NONE (default): Removes a runtime limit for all current session queries.

• 'interval': Specifies as an interval the maximum runtime for current session queries, up to one year—for example, 1 minute or 100 seconds.

• =DEFAULT: Sets maximum runtime for queries in this session to the user's RUNTIMECAP value.

Privileges

• Superusers can increase session RUNTIMECAP to any value.

• Non-superusers can only set the session RUNTIMECAP to a value equal to or lower than their own user RUNTIMECAP.

Examples

Set the maximum query runtime for the current session to 10 minutes:

=> SET SESSION RUNTIMECAP '10 minutes';

Revert the session RUNTIMECAP to your user default setting:

=> SET SESSION RUNTIMECAP =DEFAULT;
SET
=> SHOW RUNTIMECAP;
    name    |  setting
------------+-----------
 runtimecap | UNLIMITED
(1 row)

Set the RUNTIMECAP to 1 SECOND and run an anonymous procedure with an infinite loop:

=> SET SESSION RUNTIMECAP '1 SECOND';
SET

=> DO $$
BEGIN
    LOOP
    END LOOP;
END;
$$;

ERROR 0:  Query exceeded maximum runtime
HINT:  Change the maximum runtime using SET SESSION RUNTIMECAP

See also

• Setting a runtime limit for queries
• Managing workloads

16 - SET SESSION TEMPSPACECAP

Sets the maximum amount of temporary file storage that any request issued by the session can consume. If a query's execution plan requires more storage space than the session TEMPSPACECAP, it returns an error.

Syntax

SET SESSION TEMPSPACECAP limit

Arguments

limit

The maximum amount of temporary file storage to allocate to the current session, one of the following:

• NONE (default): Unlimited temporary storage

• = DEFAULT: Session TEMPSPACECAP is set to the user's TEMPSPACECAP value.

• String that specifies the storage limit, one of the following:

  • int% expresses the maximum as a percentage of total temporary storage available to the Resource Manager, where int is an integer value between 0 and 100. For example:

    SET SESSION TEMPSPACECAP '40%';
    

  • int{K|M|G|T} expresses storage allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

    SET SESSION TEMPSPACECAP '10G';
    

Privileges

Non-superusers:

• Restricted to setting only their own sessions

• Session TEMPSPACECAP cannot be greater than their own TEMPSPACECAP.

Examples

Set the session TEMPSPACECAP to 20 gigabytes:

=> SET SESSION TEMPSPACECAP '20G';
SET
=> SHOW TEMPSPACECAP;
     name     | setting
--------------+----------
 tempspacecap | 20971520
(1 row)

Set the session TEMPSPACECAP to unlimited:

=> SET SESSION TEMPSPACECAP NONE;
SET
=> SHOW TEMPSPACECAP;
     name     |  setting
--------------+-----------
 tempspacecap | UNLIMITED
(1 row)

See also

• ALTER USER
• CREATE USER
• Managing workloads

17 - SET SESSION WORKLOAD

Sets the workload for the current session.

For details on this and other session parameters, see SHOW:

Syntax

SET SESSION WORKLOAD TO { workload_name | NONE }

Parameters

workload_name

The workload to use for the current session. The specified workload must be associated with a workload routing rule.

NONE

Removes the workload for the current session.

Privileges

None

Examples

The following example sets analytics as the workload for the current session:

=> CREATE ROUTING RULE analytic_rule ROUTE WORKLOAD analytics TO SUBCLUSTER my_subcluster;
=> SET SESSION WORKLOAD analytics;

18 - SET STANDARD_CONFORMING_STRINGS

Specifies whether to treat backslashes as escape characters for the current session. By default, Vertica conforms to the SQL standard and supports SQL:2008 string literals within Unicode escapes.

Syntax

SET STANDARD_CONFORMING_STRINGS TO { ON | OFF }

Parameters

ON

(Default) Treat ordinary string literals ('...') as backslashes () literally. This means that backslashes are treated as string literals and not as escape characters.

OFF

Treat backslashes as escape characters.

Privileges

None

Requirements

• This statement works under vsql only.

• Standard-conforming strings must be ON to use Unicode-style string literals (U&'\nnnn').

Examples

Turn off conforming strings for the session:

=> SET STANDARD_CONFORMING_STRINGS TO OFF;

Verify the current setting:

=> SHOW STANDARD_CONFORMING_STRINGS;
            name             | setting
-----------------------------+---------
 standard_conforming_strings | off
(1 row)

Turn on conforming strings for the session:

=> SET STANDARD_CONFORMING_STRINGS TO ON;

See also

• SET ESCAPE_STRING_WARNING

• Standard-Conforming Strings and Escape Characters

19 - SET TIME ZONE

Changes the TIME ZONE run-time parameter for the current session. Use SHOW TIMEZONE to show the session's current time zone.

If you set the timezone using POSIX format, the timezone abbreviation you use overrides the default timezone abbreviation. If the date style is set to POSTGRES, the timezone abbreviation you use is also used when converting a timestamp to a string.

Syntax

SET TIME ZONE [TO] { value | 'value' }

Parameters

value

One of the following:

• A time zone literal supported by Vertica. To view the default list of valid literals, see the files in the following directory:
  

  /opt/vertica/share/timezonesets
  

• A signed integer representing an offset from UTC in hours

• A time zone literal with a signed integer offset. For example:

  => SET TIME ZONE TO 'America/New York -3'; -- equivalent to Pacific time
  

  Note

  Only valid timezone+offset combinations are meaningful as arguments to SET TIME ZONE. However, Vertica does not return an error for meaningless combinations—for example, America/NewYork + 150.

• An interval value

• Constants LOCAL and DEFAULT, which respectively set the time zone to the one specified in environment variable TZ, or if TZ is undefined, to the operating system time zone.

Only valid (timezone+offset) combination are acceptable as parameter for this function.

Privileges

None

Examples

=> SET TIME ZONE TO DEFAULT;
=> SET TIME ZONE TO 'PST8PDT'; -- Berkeley, California
=> SET TIME ZONE TO 'Europe/Rome'; -- Italy
=> SET TIME ZONE TO '-7'; -- UDT offset equivalent to PDT
=> SET TIME ZONE TO INTERVAL '-08:00 HOURS';

See also

19.1 - Time zone names for setting TIME ZONE

The time zone names listed below are valid settings for the SQL time zone (the TIME ZONE run-time parameter).

These names are not the same as the names shown in /opt/vertica/share/timezonesets, which are recognized by Vertica in date/time input values. The TIME ZONE names listed below imply a local Daylight Saving Time rule, where date/time input names represent a fixed offset from UTC.

In many cases, the same zone has several names. These are grouped together. The list is sorted primarily by commonly used zone names.

In addition to the names in the list, Vertica accepts time zone names as one of the following:

• STDoffset

• STDoffsetDST

where STD is a zone abbreviation, offset is a numeric offset in hours west from UTC, and DST is an optional Daylight Saving Time zone abbreviation, assumed to stand for one hour ahead of the given offset.

For example, if EST5EDT were not already a recognized zone name, Vertica accepts it as functionally equivalent to USA East Coast time. When a Daylight Saving Time zone name is present, Vertica assumes it uses USA time zone rules, so this feature is of limited use outside North America.

Time zones

• Africa:
  • Africa/Abidjan
  • Africa/Accra
  • Africa/Addis_Ababa
  • Africa/Algiers
  • Africa/Asmera
  • Africa/Bamako
  • Africa/Bangui
  • Africa/Banjul
  • Africa/Bissau
  • Africa/Blantyre
  • Africa/Brazzaville
  • Africa/Bujumbura
  • Africa/Cairo Egypt
  • Africa/Casablanca
  • Africa/Ceuta
  • Africa/Conakry
  • Africa/Dakar
  • Africa/Dar_es_Salaam
  • Africa/Djibouti
  • Africa/Douala
  • Africa/El_Aaiun
  • Africa/Freetown
  • Africa/Gaborone
  • Africa/Harare
  • Africa/Johannesburg
  • Africa/Kampala
  • Africa/Khartoum
  • Africa/Kigali
  • Africa/Kinshasa
  • Africa/Lagos
  • Africa/Libreville
  • Africa/Lome
  • Africa/Luanda
  • Africa/Lubumbashi
  • Africa/Lusaka
  • Africa/Malabo
  • Africa/Maputo
  • Africa/Maseru
  • Africa/Mbabane
  • Africa/Mogadishu
  • Africa/Monrovia
  • Africa/Nairobi
  • Africa/Ndjamena
  • Africa/Niamey
  • Africa/Nouakchott
  • Africa/Ouagadougou
  • Africa/Porto-Novo
  • Africa/Sao_Tome
  • Africa/Timbuktu
  • Africa/Tripoli Libya
  • Africa/Tunis
  • Africa/Windhoek
• America
  • America/Adak America/Atka US/Aleutian
  • America/Anchorage SystemV/YST9YDT US/Alaska
  • America/Anguilla
  • America/Antigua
  • America/Araguaina
  • America/Aruba
  • America/Asuncion
  • America/Bahia
  • America/Barbados
  • America/Belem
  • America/Belize
  • America/Boa_Vista
  • America/Bogota
  • America/Boise
  • America/Buenos_Aires
  • America/Cambridge_Bay
  • America/Campo_Grande
  • America/Cancun
  • America/Caracas
  • America/Catamarca
  • America/Cayenne
  • America/Cayman
  • America/Chicago CST6CDT SystemV/CST6CDT US/Central
  • America/Chihuahua
  • America/Cordoba America/Rosario
  • America/Costa_Rica
  • America/Cuiaba
  • America/Curacao
  • America/Danmarkshavn
  • America/Dawson
  • America/Dawson_Creek
  • America/Denver MST7MDT SystemV/MST7MDT US/Mountain America/Shiprock Navajo
  • America/Detroit US/Michigan
  • America/Dominica
  • America/Edmonton Canada/Mountain
  • America/Eirunepe
  • America/El_Salvador
  • America/Ensenada America/Tijuana Mexico/BajaNorte
  • America/Fortaleza
  • America/Glace_Bay
  • America/Godthab
  • America/Goose_Bay
  • America/Grand_Turk
  • America/Grenada
  • America/Guadeloupe
  • America/Guatemala
  • America/Guayaquil
  • America/Guyana
  • America/Halifax Canada/Atlantic SystemV/AST4ADT
  • America/Havana Cuba
  • America/Hermosillo
  • America/Indiana/Indianapolis
  • America/Indianapolis
  • America/Fort_Wayne EST SystemV/EST5 US/East-Indiana
  • America/Indiana/Knox America/Knox_IN US/Indiana-Starke
  • America/Indiana/Marengo
  • America/Indiana/Vevay
  • America/Inuvik
  • America/Iqaluit
  • America/Jamaica Jamaica
  • America/Jujuy
  • America/Juneau
  • America/Kentucky/Louisville America/Louisville
  • America/Kentucky/Monticello
  • America/La_Paz
  • America/Lima
  • America/Los_Angeles PST8PDT SystemV/PST8PDT US/Pacific US/Pacific- New
  • America/Maceio
  • America/Managua
  • America/Manaus Brazil/West
  • America/Martinique
  • America/Mazatlan Mexico/BajaSur
  • America/Mendoza
  • America/Menominee
  • America/Merida
  • America/Mexico_City Mexico/General
  • America/Miquelon
  • America/Monterrey
  • America/Montevideo
  • America/Montreal
  • America/Montserrat
  • America/Nassau
  • America/New_York EST5EDT SystemV/EST5EDT US/Eastern
  • America/Nipigon
  • America/Nome
  • America/Noronha Brazil/DeNoronha
  • America/North_Dakota/Center
  • America/Panama
  • America/Pangnirtung
  • America/Paramaribo
  • America/Phoenix MST SystemV/MST7 US/Arizona
  • America/Port-au-Prince
  • America/Port_of_Spain
  • America/Porto_Acre America/Rio_Branco Brazil/Acre
  • America/Porto_Velho
  • America/Puerto_Rico SystemV/AST4
  • America/Rainy_River
  • America/Rankin_Inlet
  • America/Recife
  • America/Regina Canada/East-Saskatchewan Canada/Saskatchewan SystemV/CST6
  • America/Santiago Chile/Continental
  • America/Santo_Domingo
  • America/Sao_Paulo Brazil/East
  • America/Scoresbysund
  • America/St_Johns Canada/Newfoundland
  • America/St_Kitts
  • America/St_Lucia
  • America/St_Thomas America/Virgin
  • America/St_Vincent
  • America/Swift_Current
  • America/Tegucigalpa
  • America/Thule
  • America/Thunder_Bay
  • America/Toronto Canada/Eastern
  • America/Tortola
  • America/Vancouver Canada/Pacific
  • America/Whitehorse Canada/Yukon
  • America/Winnipeg Canada/Central
  • America/Yakutat
  • America/Yellowknife
• Antarctica
  • Antarctica/Casey
  • Antarctica/Davis
  • Antarctica/DumontDUrville
  • Antarctica/Mawson
  • Antarctica/McMurdo
  • Antarctica/South_Pole
  • Antarctica/Palmer
  • Antarctica/Rothera
  • Antarctica/Syowa
  • Antarctica/Vostok
• Asia
  • Asia/Aden
  • Asia/Almaty
  • Asia/Amman
  • Asia/Anadyr
  • Asia/Aqtau
  • Asia/Aqtobe
  • Asia/Ashgabat Asia/Ashkhabad
  • Asia/Baghdad
  • Asia/Bahrain
  • Asia/Baku
  • Asia/Bangkok
  • Asia/Beirut
  • Asia/Bishkek
  • Asia/Brunei
  • Asia/Calcutta
  • Asia/Choibalsan
  • Asia/Chongqing Asia/Chungking
  • Asia/Colombo
  • Asia/Dacca Asia/Dhaka
  • Asia/Damascus
  • Asia/Dili
  • Asia/Dubai
  • Asia/Dushanbe
  • Asia/Gaza
  • Asia/Harbin
  • Asia/Hong_Kong Hongkong
  • Asia/Hovd
  • Asia/Irkutsk
  • Asia/Jakarta
  • Asia/Jayapura
  • Asia/Jerusalem Asia/Tel_Aviv Israel
  • Asia/Kabul
  • Asia/Kamchatka
  • Asia/Karachi
  • Asia/Kashgar
  • Asia/Katmandu
  • Asia/Krasnoyarsk
  • Asia/Kuala_Lumpur
  • Asia/Kuching
  • Asia/Kuwait
  • Asia/Macao Asia/Macau
  • Asia/Magadan
  • Asia/Makassar Asia/Ujung_Pandang
  • Asia/Manila
  • Asia/Muscat
  • Asia/Nicosia Europe/Nicosia
  • Asia/Novosibirsk
  • Asia/Omsk
  • Asia/Oral
  • Asia/Phnom_Penh
  • Asia/Pontianak
  • Asia/Pyongyang
  • Asia/Qatar
  • Asia/Qyzylorda
  • Asia/Rangoon
  • Asia/Riyadh
  • Asia/Riyadh87 Mideast/Riyadh87
  • Asia/Riyadh88 Mideast/Riyadh88
  • Asia/Riyadh89 Mideast/Riyadh89
  • Asia/Saigon
  • Asia/Sakhalin
  • Asia/Samarkand
  • Asia/Seoul ROK
  • Asia/Shanghai PRC
  • Asia/Singapore Singapore
  • Asia/Taipei ROC
  • Asia/Tashkent
  • Asia/Tbilisi
  • Asia/Tehran Iran
  • Asia/Thimbu Asia/Thimphu
  • Asia/Tokyo Japan
  • Asia/Ulaanbaatar Asia/Ulan_Bator
  • Asia/Urumqi
  • Asia/Vientiane
  • Asia/Vladivostok
  • Asia/Yakutsk
  • Asia/Yekaterinburg
  • Asia/Yerevan
• Atlantic
  • Atlantic/Azores
  • Atlantic/Bermuda
  • Atlantic/Canary
  • Atlantic/Cape_Verde
  • Atlantic/Faeroe
  • Atlantic/Madeira
  • Atlantic/Reykjavik Iceland
  • Atlantic/South_Georgia
  • Atlantic/St_Helena
  • Atlantic/Stanley
• Australia
  • Australia/ACT
  • Australia/Canberra
  • Australia/NSW
  • Australia/Sydney
  • Australia/Adelaide
  • Australia/South
  • Australia/Brisbane
  • Australia/Queensland
  • Australia/Broken_Hill
  • Australia/Yancowinna
  • Australia/Darwin
  • Australia/North
  • Australia/Hobart
  • Australia/Tasmania
  • Australia/LHI
  • Australia/Lord_Howe
  • Australia/Lindeman
  • Australia/Melbourne
  • Australia/Victoria
  • Australia/Perth Australia/West
• CET
• EET
• Etc/GMT
  • GMT
  • GMT+0
  • GMT-0
  • GMT0
  • Greenwich
  • Etc/Greenwich
  • Etc/GMT+0...Etc/GMT+12
  • Etc/GMT-0...Etc/GMT-14
• Europe
  • Europe/Amsterdam
  • Europe/Andorra
  • Europe/Athens
  • Europe/Belfast
  • Europe/Belgrade
  • Europe/Ljubljana
  • Europe/Sarajevo
  • Europe/Skopje
  • Europe/Zagreb
  • Europe/Berlin
  • Europe/Brussels
  • Europe/Bucharest
  • Europe/Budapest
  • Europe/Chisinau Europe/Tiraspol
  • Europe/Copenhagen
  • Europe/Dublin Eire
  • Europe/Gibraltar
  • Europe/Helsinki
  • Europe/Istanbul Asia/Istanbul Turkey
  • Europe/Kaliningrad
  • Europe/Kiev
  • Europe/Lisbon Portugal
  • Europe/London GB GB-Eire
  • Europe/Luxembourg
  • Europe/Madrid
  • Europe/Malta
  • Europe/Minsk
  • Europe/Monaco
  • Europe/Moscow W-SU
  • Europe/Oslo
  • Arctic/Longyearbyen
  • Atlantic/Jan_Mayen
  • Europe/Paris
  • Europe/Prague Europe/Bratislava
  • Europe/Riga
  • Europe/Rome Europe/San_Marino Europe/Vatican
  • Europe/Samara
  • Europe/Simferopol
  • Europe/Sofia
  • Europe/Stockholm
  • Europe/Tallinn
  • Europe/Tirane
  • Europe/Uzhgorod
  • Europe/Vaduz
  • Europe/Vienna
  • Europe/Vilnius
  • Europe/Warsaw Poland
  • Europe/Zaporozhye
  • Europe/Zurich
• Factory
• Indian
  • Indian/Antananarivo
  • Indian/Chagos
  • Indian/Christmas
  • Indian/Cocos
  • Indian/Comoro
  • Indian/Kerguelen
  • Indian/Mahe
  • Indian/Maldives
  • Indian/Mauritius
  • Indian/Mayotte
  • Indian/Reunion
• MET
• Pacific
  • Pacific/Apia
  • Pacific/Auckland NZ
  • Pacific/Chatham NZ-CHAT
  • Pacific/Easter
  • Chile/EasterIsland
  • Pacific/Efate
  • Pacific/Enderbury
  • Pacific/Fakaofo
  • Pacific/Fiji
  • Pacific/Funafuti
  • Pacific/Galapagos
  • Pacific/Gambier SystemV/YST9
  • Pacific/Guadalcanal
  • Pacific/Guam
  • Pacific/Honolulu HST SystemV/HST10 US/Hawaii
  • Pacific/Johnston
  • Pacific/Kiritimati
  • Pacific/Kosrae
  • Pacific/Kwajalein Kwajalein
  • Pacific/Majuro
  • Pacific/Marquesas
  • Pacific/Midway
  • Pacific/Nauru
  • Pacific/Niue
  • Pacific/Norfolk
  • Pacific/Noumea
  • Pacific/Pago_Pago
  • Pacific/Samoa US/Samoa
  • Pacific/Palau
  • Pacific/Pitcairn SystemV/PST8
  • Pacific/Ponape
  • Pacific/Port_Moresby
  • Pacific/Rarotonga
  • Pacific/Saipan
  • Pacific/Tahiti
  • Pacific/Tarawa
  • Pacific/Tongatapu
  • Pacific/Truk
  • Pacific/Wake
  • Pacific/Wallis
  • Pacific/Yap
• UCT Etc
• UCT
• UTC
  • Universal Zulu
  • Etc/UTC
  • Etc/Universal
  • Etc/Zulu
• WET