set(7) - f7

1SET()                            SQL Commands                            SET()
2
3
4

NAME

6       SET - change a run-time parameter
7
8

SYNOPSIS

10       SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
11       SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
12
13

DESCRIPTION

15       The  SET command changes run-time configuration parameters. Many of the
16       run-time parameters listed in in the documentation can be  changed  on-
17       the-fly  with  SET.   (But some require superuser privileges to change,
18       and others cannot be changed after server or session start.)  SET  only
19       affects the value used by the current session.
20
21       If  SET  or  SET  SESSION  is issued within a transaction that is later
22       aborted, the effects of the SET command disappear when the  transaction
23       is rolled back. (This behavior represents a change from PostgreSQL ver‐
24       sions prior to 7.3, where the effects of SET would not roll back  after
25       a  later  error.)  Once  the  surrounding transaction is committed, the
26       effects will persist until the end of the session, unless overridden by
27       another SET.
28
29       The effects of SET LOCAL last only till the end of the current transac‐
30       tion, whether committed or not. A special case is SET followed  by  SET
31       LOCAL  within  a  single  transaction: the SET LOCAL value will be seen
32       until the end of the transaction, but afterwards (if the transaction is
33       committed) the SET value will take effect.
34

PARAMETERS

36       SESSION
37              Specifies that the command takes effect for the current session.
38              (This is the default if neither SESSION nor LOCAL appears.)
39
40       LOCAL  Specifies that the command takes effect  for  only  the  current
41              transaction. After COMMIT or ROLLBACK, the session-level setting
42              takes effect again. Note that SET LOCAL will appear to  have  no
43              effect if it is executed outside a BEGIN block, since the trans‐
44              action will end immediately.
45
46       configuration_parameter
47              Name of a settable run-time parameter. Available parameters  are
48              documented in in the documentation and below.
49
50       value  New  value  of parameter. Values can be specified as string con‐
51              stants, identifiers, numbers, or comma-separated lists of these.
52              DEFAULT  can  be  used to specify resetting the parameter to its
53              default value.
54
55       Besides the configuration parameters documented in  in  the  documenta‐
56       tion,  there  are a few that can only be adjusted using the SET command
57       or that have a special syntax:
58
59       NAMES  SET NAMES value is an alias for SET client_encoding TO value.
60
61       SEED   Sets the internal seed for  the  random  number  generator  (the
62              function  random).  Allowed  values  are  floating-point numbers
63              between 0 and 1, which are then multiplied by 231-1.
64
65              The seed can also be set by invoking the function setseed:
66
67              SELECT setseed(value);
68
69
70       TIME ZONE
71              SET TIME ZONE value is an alias for SET timezone TO  value.  The
72              syntax  SET  TIME  ZONE  allows special syntax for the time zone
73              specification. Here are examples of valid values:
74
75              'PST8PDT'
76                     The time zone for Berkeley, California.
77
78              'Europe/Rome'
79                     The time zone for Italy.
80
81              -7     The time zone 7 hours west from UTC (equivalent to  PDT).
82                     Positive values are east from UTC.
83
84              INTERVAL '-08:00' HOUR TO MINUTE
85                     The time zone 8 hours west from UTC (equivalent to PST).
86
87              LOCAL
88
89              DEFAULT
90                     Set  the  time zone to your local time zone (the one that
91                     the server's operating system defaults to).
92
93       See in the documentation for more information about time zones.
94

NOTES

96       The function set_config provides equivalent functionality. See  in  the
97       documentation.
98

EXAMPLES

100       Set the schema search path:
101
102       SET search_path TO my_schema, public;
103
104
105       Set the style of date to traditional POSTGRES with ``day before month''
106       input convention:
107
108       SET datestyle TO postgres, dmy;
109
110
111       Set the time zone for Berkeley, California:
112
113       SET TIME ZONE 'PST8PDT';
114
115
116       Set the time zone for Italy:
117
118       SET TIME ZONE 'Europe/Rome';
119
120

COMPATIBILITY

122       SET TIME ZONE extends syntax defined in the SQL standard. The  standard
123       allows  only  numeric  time  zone  offsets while PostgreSQL allows more
124       flexible time-zone specifications. All other  SET  features  are  Post‐
125       greSQL extensions.
126