EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL SET

PostgreSQL SET

Priya Pedamkar
Article byPriya Pedamkar

Updated May 19, 2023

PostgreSQL SET

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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;\

PostgreSQL SET 1

  • 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;

PostgreSQL SET 2

  • 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;

PostgreSQL SET 3

  • 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;

PostgreSQL SET 4

 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 2

 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 3

 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;

example 4

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.

  1. PostgreSQL Describe Table
  2. Caching PostgreSQL
  3. PostgreSQL OR
  4. PostgreSQL enum
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test
 15+ Hour of HD Videos
5 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more