Updated May 19, 2023
Definition of PostgreSQL SET
The PostgreSQL SET command allows users to change the runtime configuration parameters directly from the database server. It provides the capability to modify multiple parameters during runtime by utilizing the set command in PostgreSQL. Using the set command, we can change the value of parameter at runtime, there is no need to take restart of database service to take effect of that parameter. We can set parameter value only current session, which we have connected, other user doesn’t affect to those parameter. When we set any parameter using the set command, later running the transaction is, the aborted effect of the set command is disable when we rollback the transaction.
Syntax:
Below is the syntax of the set command.
- SET [ LOCAL | SESSION (Set parameter at session or local level) ] configuration_parameter_name (Configuration parameter name which we have set for the session or local level) { TO | = } { value (Value of parameter.) | DEFAULT }
- SET [ LOCAL | SESSION (Set parameter at session or local level) ] TIME ZONE { timezone (timezone value which we have set) | LOCAL | DEFAULT }
Below is the parameter description syntax of the set command in PostgreSQL.
- Set –This is a command in PostgreSQL to set parameter at the session or local level. Set parameter value will affect only the current parameter sessions. This command represents a runtime configuration parameter used in PostgreSQL.
- Local –Local is define as the set command using local that will take effect only on the current transaction which we have running. After commit or rollback, it will not take effect again, we need to set is again.
- Session –Session is define as the set command using session level, it will take effect only on the current session which we have running.
- Configuration parameter name –This is the parameter name we have set using the set command in PostgreSQL.
- Value – This is the new parameter value we have set using the set command in PostgreSQL.
- Time zone –The time zone parameter in the set command is defined as the selected time zone for the current transaction or current session.
- Default –This is defined as the set default time zone of the current transaction or session. Default time zone is the server current timezone.
How Set work in PostgreSQL?
Below is the working of the set command.
- The “set” command in PostgreSQL is a metadata command used to configure the parameter value either at the session or local level.
- Set command is more important and useful in PostgreSQL to set the parameter to the current transaction or session for the user.
- We can set the value of the connected user as local and session level. If we set the parameter value as local, then the effect of this parameter is only the current transaction which we have runningon the database server.
- The example below shows that after setting the parameter’s value using the local level only valid until the current transaction is running.
SHOW TIMEZONE;
SET LOCAL TIMEZONE='America/Martinique';
SHOW TIMEZONE;\
- The above example shows that if we set the value of the timezone at the local level, it will only affect the current transaction we have running on the server.
- When the parameter value is set as session-level, its effect is limited to the current session running on the database server.
- The example below shows that after setting the parameter’s value using the session-level, it is only valid until the current session is running.
SHOW TIMEZONE;
SET SESSION TIMEZONE='America/Martinique';
SHOW TIMEZONE;
- The above example shows that if we set the value of the timezone at the session-level, it will only affect the current session we have running on the server.
- By default set timezone is set at session-level. The below example shows that the default level of setting parameter value are as follows.
SHOW TIMEZONE;
SET TIMEZONE='America/Martinique';
SHOW TIMEZONE;
- In the above example, we have not used any level to set parameter value. We have used the default level to set parameter value in PostgreSQL.
- We can also set the timezone value of the integer using the set command in PostgreSQL.
- There is no need to use case sensitive name of the set command in PostgreSQL. We can use it as a set or SET. There is no need to use case sensitive letter at the time of using the set command in PostgreSQL.
- We can use the same functionality of the set command by using the set_config functions. Also, we have the same obtained by using updating system view of pg_settings.
Examples
Below is an example of the set command in PostgreSQL.
Example #1 – Set the schema search path by using the set command
Below example shows that set the search path of a public schema in PostgreSQL.
- We have set the search path of the public schema name as test_schma.
SET search_path TO test_schema, public;
Example #2 – Set the timezone using the set command
The below example shows that set the timezone using the set command in PostgreSQL.
- We have set the timezone as ‘America/Cordoba’ as session-level.
SET session TIMEZONE='America/Cordoba';
show TIMEZONE;
Example #3 – Set the parameter value using the set command
Below example shows that change the parameter value at the session level using the set command in PostgreSQL.
- In the below example, we have changed the lock_timeout parameter value to 1000 milliseconds.
set lock_timeout to 1000;
show lock_timeout;
Example #4 – Set the parameter value as default using the set command
Below example shows that change the parameter value as default at the session level using the set command in PostgreSQL.
- In the below example, we have change the value of the lock_timeout parameter as default.
set lock_timeout TO DEFAULT;
show lock_timeout;
Conclusion
Set command in PostgreSQL is basically used to change the parameter value. We can change the parameter value as local and session-level. If we set the parameter value at session level, then scope of a parameter is valid only session-level. Scope of local is valid only single transaction.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL SET” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
