SET

Use the SET command to set or change runtime parameters. Issue this command to change parameters during the database connection.

Syntax

Syntax for the SET command:

SET <parameter> { TO | = } { <value> | '<value>' | DEFAULT }

Inputs

The SET command takes the following inputs:

Table 1. SET inputs
Input	Description
<parameter>	The parameter for which a value is to be set. These are described in Parameters:
<value>	The value that is to be set for the parameter. Specify DEFAULT to reset a parameter to its default value. Use single quotation marks: For values that are longer than 128 characters For values that contain blanks or special characters such as plus and minus signs To prevent character strings from being converted to the default case Possible values for each parameter are described in Parameters:

Parameters

You can use the SET command to set the following parameters:

TIME ZONE or TIMEZONE

The time zone for the database session.

'<timezone>': A time zone specified as signed hours with optional minutes (for example, '-08' or '+10:30') that indicates its offset from GMT. A minus sign indicates that the zone is west of Greenwich. The range of time zones is '-12:59' to '+13:00'.
LOCAL: The local timezone.

Syntax:

SET TIME ZONE { '<timezone>' | DEFAULT | LOCAL }

DATESTYLE

The datestyle option is intended only for porting applications. To format your date/time values, use the to_char family of functions.

Specify the date/time output style, the substyle, or both:

Date/time output style

The date/time output styles are:

ISO: Use ISO 8601-style dates and times (YYYY-MM-DD for dates; hh:mm:ss for times). This is the default.
SQL: Use DD/MM/YYYY or MM/DD/YYYY for dates; hh:mm:ss for times. Which date style is used depends on the substyle.
German: Use DD.MM.YYYY for dates; hh:mm:ss for times.

Substyle

The substyle determines both which date format is used for the SQL output format and the preferred interpretation of ambiguous date input:

European: Use DD/MM/YYYY for numeric date representations.
US: Use MM/DD/YYYY for numeric date representations.

Syntax:

SET DATESTYLE { TO | = }
    { <output_style> | <substyle> | <output_style>,<substyle> | DEFAULT }

You can initialize the date format by setting the pgdatestyle environment variable. If pgdatestyle is set in the frontend environment of a client that is based on libpq, libpq automatically sets datestyle to the value of pgdatestyle during connection start.

RANDOM_SEED

The seed for the random number generator. The random function uses this value. You can specify any floating-point number in the range from 0 to 1. (If a number outside this range is used, this product silently overflows.) This number is then multiplied by 2(31)-1.

Syntax:

SET RANDOM_SEED { TO | = } { <value> | DEFAULT }

You can also set the seed by starting the setseed function: select setseed(value);

LOAD_REPLAY_REGION

Enables load continuation. The default is 0 (not enabled).

If you set the load replay region, the system automatically continues a load after the system has been paused due to an SPU reset or failover. The replay region is used to hold raw input data that will be parsed, converted, packed into binary records, and sent to the respective SPUs that have not be precommitted. When the system resumes from a paused state, the system re-parses, re-converts, re-packs, and re-sends to the SPUs all the raw data in this region.

Syntax:

SET LOAD_REPLAY_REGION { TO | = } { 0 | 1 | DEFAULT }

CLIENT_USER_ID

The user ID under which the client is running. The value can be up to 512 characters. The default is an empty string ('').

Syntax:

SET CLIENT_USER_ID { TO | = } { <value> | '<value>' | DEFAULT }

CLIENT_WORKSTATION_NAME

The host name of the workstation on which the client runs. The value can be up to 512 characters. The default is an empty string ('').

Syntax:

SET CLIENT_WORKSTATION_NAME { TO | = } { <value> | '<value>' | DEFAULT }

CLIENT_APPLICATION_NAME

The name of the client. The value can be up to 512 characters. The default is an empty string ('').

Syntax:

SET CLIENT_APPLICATION_NAME { TO | = } { <value> | '<value>' | DEFAULT }

CLIENT_ACCOUNTING_STRING

The accounting string. The value can be up to 512 characters. The default is an empty string ('').

Syntax:

SET CLIENT_ACCOUNTING_STRING { TO | = } { <value> | '<value>' | DEFAULT }

Outputs

The SET command has the following outputs:

Table 2. SET outputs
Output	Description
`SET VARIABLE`	The command was successful.
`ERROR: not a valid option name: name`	The parameter you tried to set does not exist.
`ERROR: permission denied`	You do not have correct access. You must be an administrator to have access to certain settings.
`ERROR: name can only be set at start-up`	Certain parameters were fixed after the server is started.

Privileges

You must be an administrator, or you must have the appropriate object privileges.

Usage

The following examples provide sample usage.

Set the time zone to Eastern Standard Time (EST):
```
MYDB.MYSCH(USER)=>  SET TIME ZONE '-05';
```

Set the substyle to European:

MYDB.MYSCH(USER)=>  SET DATESTYLE TO European;

Set the output style to ISO and the substyle to US:
```
MYDB.MYSCH(USER)=>  SET DATESTYLE TO ISO,US;
```
Seed the random number generator with the number 0.45:
```
MYDB.MYSCH(USER)=>  SET RANDOM_SEED=0.45;
```

Enable load continuation:

MYDB.MYSCH(USER)=>  SET LOAD_REPLAY_REGION=1;

Set the name of the client workstation to homer.sampcorp.com and to prevent the value from being converted to the default case:
```
MYDB.MYSCH(USER)=>  SET CLIENT_WORKSTATION_NAME TO 'homer.sampcorp.com'
```