SET vs ALTER in PostgreSQL: Key Differences

In PostgreSQL, SET and ALTER do different jobs: SET changes a setting for the current session or transaction, while ALTER permanently changes the definition of a database object like a table, column, or role. Use SET for runtime parameters that last only as long as your session, and ALTER for structural or persistent changes that affect all future sessions.

What SET Does

SET is a SQL command that assigns a new value to a PostgreSQL runtime parameter. These parameters control everything from the timezone and date style to memory limits and query planner behavior. The change is temporary—it applies only to the current session (or current transaction if you use SET LOCAL) and disappears when the session ends.

Common examples:

SET timezone = 'UTC'; — changes the timezone for the session
SET search_path = myschema, public; — changes where unqualified table names are looked up
SET statement_timeout = 5000; — limits query execution time to 5 seconds
SET enable_seqscan = off; — disables sequential scans for the session (for testing)

You can also set parameters when connecting using the PGOPTIONS environment variable or the options connection parameter. The SHOW command displays current values, and RESET returns a parameter to its default.

What ALTER Does

ALTER is a family of commands that modify the structure or properties of database objects. Unlike SET, changes made with ALTER are persistent—they are stored in the system catalogs and affect all future sessions. The exact syntax depends on the object type:

ALTER TABLE — add/drop columns, change data types, add constraints, rename tables
ALTER ROLE — change role attributes (e.g., password, login privilege) or set role-specific defaults
ALTER DATABASE — change database-level settings (e.g., ALTER DATABASE mydb SET timezone = 'UTC';)
ALTER SYSTEM — change server-level configuration (writes to postgresql.auto.conf)
ALTER SEQUENCE, ALTER INDEX, ALTER VIEW, etc.

For example, ALTER TABLE users ADD COLUMN email text; permanently adds a column to the users table.

Key Differences at a Glance

The most important distinction is persistence. SET is transient; ALTER is permanent. But there are other differences worth noting:

Scope: SET affects only the current session (or transaction with SET LOCAL). ALTER affects the object globally for all users and sessions.
Object type: SET works on runtime parameters; ALTER works on database objects (tables, roles, databases, etc.).
Permissions: SET usually requires no special privileges (except for superuser-only parameters). ALTER requires ownership or appropriate privileges on the object.
Transaction behavior: SET LOCAL is rolled back if the transaction is rolled back. ALTER is transactional for most objects—if you roll back, the change is undone.
Persistence: SET values are lost when the session ends. ALTER changes survive until explicitly reverted.

How to Choose Between SET and ALTER

Ask yourself: do you want the change to be permanent and affect everyone, or just for this session? Here’s a decision guide:

Use SET when you need a temporary adjustment for your own work—for example, changing the timezone for a report, or turning off a planner method to test a query.
Use ALTER DATABASE ... SET when you want a default for all sessions connecting to that database (e.g., ALTER DATABASE mydb SET timezone = 'UTC';).
Use ALTER ROLE ... SET when you want a default for a specific user (e.g., ALTER ROLE alice SET statement_timeout = 10000;).
Use ALTER TABLE (or other object ALTER) when you need to change the structure or properties of a table, column, index, etc.
Use ALTER SYSTEM for server-wide settings that should survive restarts (e.g., ALTER SYSTEM SET max_connections = 200;). Note that this requires a reload or restart to take effect.

For most day-to-day work, SET is for your session, and ALTER is for the database itself.

Common Mistakes and Misconceptions

One common mistake is using SET to try to change a table’s structure—that simply fails because SET only works on runtime parameters. Another is forgetting that SET does not persist; if you set timezone and then reconnect, it’s back to the default. Conversely, people sometimes use ALTER DATABASE when they only need a session change, which can affect other users unexpectedly.

Another misconception: ALTER SYSTEM does not immediately change the running server—it writes to a file that is read at the next reload or restart. To apply immediately, you need SELECT pg_reload_conf(); (for parameters that support reload) or a restart (for parameters that don’t).

Also note that SET does not require a semicolon in some client tools, but it’s good practice to include one. And SET LOCAL only lasts until the end of the current transaction—if you use it outside a transaction, it behaves like a regular SET.

Practical Examples

Let’s look at a few scenarios to illustrate the difference.

Scenario 1: Change timezone for a single query

SET timezone = 'America/New_York';
SELECT now();
-- session ends, timezone reverts to default

Scenario 2: Make a timezone default for a database

ALTER DATABASE mydb SET timezone = 'America/New_York';
-- all new sessions to mydb will use this timezone

Scenario 3: Add a column to a table

ALTER TABLE users ADD COLUMN last_login timestamptz;
-- permanent change to the table structure

Scenario 4: Set a role’s default statement timeout

ALTER ROLE alice SET statement_timeout = 15000;
-- every new session for alice gets this timeout

Notice how SET is used for session-level parameters, while ALTER is used to change defaults or object definitions.

Performance and Safety Considerations

From a performance standpoint, SET is trivial—it just changes a value in memory. ALTER can be expensive depending on the operation. For example, ALTER TABLE ... ADD COLUMN with a non-null default can rewrite the entire table in older PostgreSQL versions (though since 11, adding a column with a constant default is fast). Changing a column’s data type may require a full table rewrite. Always test ALTER on a staging environment first.

Safety-wise, ALTER is transactional—if you run it inside a transaction and roll back, the change is undone. However, some operations like ALTER INDEX ... SET TABLESPACE may not be fully transactional. Also, ALTER SYSTEM writes to a file that is read at startup; if you set an invalid value, the server may fail to start. Always double-check syntax and values.

For SET, there’s little risk, but be aware that some parameters are superuser only—a normal user cannot set them. Also, SET does not validate the value until it’s used, so a typo might not be caught immediately.

Frequently Asked Questions

Can I use SET to change a table column’s default value? No. SET only works on runtime parameters. To change a column default, use ALTER TABLE ... ALTER COLUMN ... SET DEFAULT.

Does SET LOCAL affect other transactions? No, SET LOCAL only affects the current transaction. It’s rolled back if the transaction rolls back, and it doesn’t affect other sessions.

How do I make a SET permanent for all sessions? Use ALTER DATABASE ... SET or ALTER ROLE ... SET to set a default for a database or role. For server-wide, use ALTER SYSTEM and then reload/restart.

What’s the difference between SET and RESET? RESET restores a parameter to its default value (or removes a database/role setting). For example, RESET timezone; sets it back to the default.

Can I ALTER a table while other users are querying it? Yes, PostgreSQL uses locking. Most ALTER TABLE operations acquire an ACCESS EXCLUSIVE lock, which blocks reads and writes until the operation completes. For large tables, this can be disruptive; consider using pg_repack or doing it during maintenance windows.

Is there a way to see what SET values are active? Yes, use SHOW ALL; or query the pg_settings view. For a specific parameter, SHOW parameter_name;.

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

The whole Numbers and Powers drawer →