Trending September 2023 # Changing The Parameter Value Using Set Command # Suggested October 2023 # Top 15 Popular

You are reading the article Changing The Parameter Value Using Set Command updated in September 2023 on the website Happystarlongbien.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Changing The Parameter Value Using Set Command

Definition of PostgreSQL SET

Hadoop, Data Science, Statistics & others

Syntax:

Below is the syntax of the set command.

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

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.