Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

SET

Sets the value of a server configuration parameter.

Use the RESET command to return a parameter to its default value. See Modifying the Server Configuration for more information about parameters.

Syntax

Copy
SET { [ SESSION | LOCAL ] { SEED | parameter_name } { TO | = } { value | 'value' | DEFAULT } | SEED TO value }

Parameters

SESSION

Specifies that the setting is valid for the current session. Default value.

LOCAL

Specifies that the setting is valid for the current transaction.

SEED TO value

Sets an internal seed to be used by the RANDOM function for random number generation.

SET SEED takes a numeric value between 0 and 1, and multiplies this number by (231-1) for use with the RANDOM Function function. If you use SET SEED before making multiple RANDOM calls, RANDOM generates numbers in a predictable sequence.

parameter_name

Name of the parameter to set. See Modifying the Server Configuration for information about parameters.

value

New parameter value. Use single quotes to set the value to a specific string. If using SET SEED, this parameter contains the SEED value.

DEFAULT

Sets the parameter to the default value.

Examples

Changing a Parameter for the Current Session

The following example sets the datestyle:

Copy
set datestyle to 'SQL,DMY';

Setting a Query Group for Workload Management

If query groups are listed in a queue definition as part of the cluster's WLM configuration, you can set the QUERY_GROUP parameter to a listed query group name. Subsequent queries are assigned to the associated query queue. The QUERY_GROUP setting remains in effect for the duration of the session or until a RESET QUERY_GROUP command is encountered.

This example runs two queries as part of the query group 'priority', then resets the query group.

Copy
set query_group to 'priority'; select tbl, count(*)from stv_blocklist; select query, elapsed, substring from svl_qlog order by query desc limit 5; reset query_group;

See Implementing Workload Management

Setting a Label for a Group of Queries

The QUERY_GROUP parameter defines a label for one or more queries that are executed in the same session after a SET command. In turn, this label is logged when queries are executed and can be used to constrain results returned from the STL_QUERY and STV_INFLIGHT system tables and the SVL_QLOG view.

Copy
show query_group; query_group ------------- unset (1 row) set query_group to '6 p.m.'; show query_group; query_group ------------- 6 p.m. (1 row) select * from sales where salesid=500; salesid | listid | sellerid | buyerid | eventid | dateid | ... ---------+--------+----------+---------+---------+--------+----- 500 | 504 | 3858 | 2123 | 5871 | 2052 | ... (1 row) reset query_group; select query, trim(label) querygroup, pid, trim(querytxt) sql from stl_query where label ='6 p.m.'; query | querygroup | pid | sql -------+------------+-------+---------------------------------------- 57 | 6 p.m. | 30711 | select * from sales where salesid=500; (1 row)

Query group labels are a useful mechanism for isolating individual queries or groups of queries that are run as part of scripts. You do not need to identify and track queries by their IDs; you can track them by their labels.

Setting a Seed Value for Random Number Generation

The following example uses the SEED option with SET to cause the RANDOM function to generate numbers in a predictable sequence.

First, return three RANDOM integers without setting the SEED value first:

Copy
select cast (random() * 100 as int); int4 ------ 6 (1 row) select cast (random() * 100 as int); int4 ------ 68 (1 row) select cast (random() * 100 as int); int4 ------ 56 (1 row)

Now, set the SEED value to .25, and return three more RANDOM numbers:

Copy
set seed to .25; select cast (random() * 100 as int); int4 ------ 21 (1 row) select cast (random() * 100 as int); int4 ------ 79 (1 row) select cast (random() * 100 as int); int4 ------ 12 (1 row)

Finally, reset the SEED value to .25, and verify that RANDOM returns the same results as the previous three calls:

Copy
set seed to .25; select cast (random() * 100 as int); int4 ------ 21 (1 row) select cast (random() * 100 as int); int4 ------ 79 (1 row) select cast (random() * 100 as int); int4 ------ 12 (1 row)

On this page: