The SET command in PostgreSQL changes a configuration parameter for the current session, letting you adjust behavior like time zones, statement timeouts, or search paths without editing server files. You enter SET parameter = value; (or SET parameter TO value;) and the new setting applies immediately to all subsequent commands in that session until you change it again, reset it, or the session ends.
What SET does behind the scenes
PostgreSQL stores configuration parameters in a hierarchy: the server’s postgresql.conf file, command-line options at startup, then database, user, and session-level overrides. SET writes to the session layer, which sits above all of those. It does not modify any file or the database catalog—it simply changes the in-memory value for the current backend process.
Parameters fall into categories such as context (when they can be changed), unit (whether they expect a number with a unit like ms or MB), and scope (session, transaction, or superuser-only). SET works only for parameters marked as “user” or “superuser” context; some, like shared_buffers, can only be set at server start.
How to use SET effectively
You can use SET in several practical ways:
- Change the
search_pathto avoid schema-qualifying table names:SET search_path = app, public; - Set a statement timeout for long-running queries:
SET statement_timeout = '30s'; - Adjust
work_memfor a single heavy sort or hash operation:SET work_mem = '64MB'; - Switch to a different date style:
SET datestyle = 'ISO, DMY'; - Set application-specific custom parameters (prefixed with a dot, e.g.,
SET myapp.user_id = 42;) for use in triggers or functions.
You can also use SET inside a transaction with SET LOCAL so the change reverts automatically when the transaction ends—useful for temporary adjustments that should not leak into later work.
Common mistakes and gotchas
One frequent error is forgetting quotes around string values. SET TimeZone = UTC; fails because UTC is parsed as an identifier, not a string. Use 'UTC' instead. Another is trying to set a parameter that requires superuser privileges; you’ll get a permission denied error. Also, SET does not persist—if you want a permanent change, you must edit postgresql.conf or use ALTER DATABASE/ALTER ROLE.
Be aware that SET affects only the current session. If you open a new connection, the value resets to whatever the server default is. For application-wide settings, use ALTER DATABASE or ALTER ROLE instead.
SET vs. ALTER DATABASE vs. postgresql.conf
These three methods operate at different levels:
SETchanges the value for the current session only.ALTER DATABASE ... SETsets a default for all sessions connecting to that database.ALTER ROLE ... SETsets a default for all sessions of that user.- Editing
postgresql.confchanges the server-wide default, affecting every database and user unless overridden.
In practice, you use SET for temporary, session-specific needs, and the other methods for persistent defaults.
Resetting and inspecting settings
To revert a session parameter to its default, use RESET parameter_name; or SET parameter_name TO DEFAULT;. To see the current value, use SHOW parameter_name;. To list all parameters that differ from their defaults, query the pg_settings view:
SELECT name, setting, unit, context FROM pg_settings WHERE source = 'session';
This view also shows the source column, which tells you where the current value came from (e.g., default, config file, session).
Frequently Asked Questions
Does SET require a server restart?
No. SET changes the value in memory immediately and does not require a restart. It only affects the current session, so the server keeps running normally.
Can I use SET in a function or trigger?
Yes, but with caution. SET inside a function changes the session’s configuration for the rest of that session, which can be surprising. Use SET LOCAL inside a transaction or set_config(...) with is_local = true to limit the change to the function’s transaction.
Why does my SET not stick after I reconnect?
Because SET is session-scoped. When you disconnect, the setting is lost. To make it persist, use ALTER DATABASE or ALTER ROLE to set a default for future connections.
What is the difference between SET and SET SESSION?
There is no difference—SET is equivalent to SET SESSION. Both change the value for the entire session. SET LOCAL is the one that limits the change to the current transaction.
How do I set a custom parameter?
Custom parameters must have a name that includes a dot, like myapp.debug. You can set them with SET myapp.debug = 'on'; and read them with current_setting('myapp.debug'). They are not predefined, so you can use them for your own application logic.
Can SET be used in a read-only transaction?
Yes, but only for parameters that are not marked as “superuser” and that do not require a transaction context. Some parameters, like transaction_read_only, cannot be changed once a transaction has started.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- Microsoft Surface Duo: The Foldable Phone Story
- Samsung SM-T813 Hard Reset: Step-by-Step Guide
- Mafia Clash Codes: Real Meanings & Usage
- Remove Programs from Mac: Full Uninstall Guide
- Project Sentinel Morning Digest: Data Stack Failure Alerts
- Best KVM VPS Hosting: Top Picks & Guide