ALTER_DATABASE(7pg14)

💅 ALTER DATABASE 💅

okay so bestie, welcome to the ALTER DATABASE page from the PostgreSQL 14.24 Documentation ✨ this is the one where you go mess with a whole entire database's vibe — its name, its owner, its tablespace, its settings — all from one command. it's giving admin main character energy 👑

---

✨ NAME ✨

ALTER_DATABASE — change a database 🦋

like, literally change a database. that's the whole job.

---

🎀 SYNOPSIS 🎀

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

five whole forms, each one a different little slay 💅 pick the one you need, okay?

---

💖 DESCRIPTION 💖

ALTER DATABASE changes the attributes of a database. periodt. ✨

the first form changes certain per-database settings (details down below in PARAMETERS, keep scrolling bestie). only the database owner or a superuser can change these settings. no random people touching your settings 🙅

the second form changes the name of the database. only the database owner or a superuser can rename a database — and non-superuser owners must also have the CREATEDB privilege. also?? you cannot rename the current database 😭 (connect to a different database if you need to do that, iykyk.)

the third form changes the owner of the database. to alter the owner you must own the database and also be a direct or indirect member of the new owning role, and you must have the CREATEDB privilege. (note that superusers have all these privileges automatically — they're just built different 👑)

the fourth form changes the default tablespace of the database. only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. heads up: this command physically moves any tables or indexes in the database's old default tablespace to the new tablespace 🚚 the new default tablespace must be empty for this database, and no one can be connected to the database while this happens. tables and indexes in non-default tablespaces are unaffected — they stay right where they are, unbothered.

the remaining forms change the session default for a run-time configuration variable for a PostgreSQL database. whenever a new session is subsequently started in that database, the specified value becomes the session default value 🌱 the database-specific default overrides whatever setting is present in postgresql.conf or has been received from the postgres command line. only the database owner or a superuser can change the session defaults for a database. and plot twist: certain variables cannot be set this way, or can only be set by a superuser 🙈

---

🦋 PARAMETERS 🦋

• name — the name of the database whose attributes are to be altered. the star of the show 💫
• allowconn — if false then no one can connect to this database. nobody. not even your bestie. 🚪🔒
• connlimit — how many concurrent connections can be made to this database. -1 means no limit. unlimited connections, unlimited chaos 🔥
• istemplate — if true, then this database can be cloned by any user with CREATEDB privileges; if false, then only superusers or the owner of the database can clone it. gatekeeping, but make it permissions 🎀
• new_name — the new name of the database. glow-up name change ✨
• new_owner — the new owner of the database. handing over the crown 👑
• new_tablespace — the new default tablespace of the database.

⚠️ this form of the command cannot be executed inside a transaction block. just. don't. it's not that kind of command, bestie 😭

• configuration_parameter, value — set this database's session default for the specified configuration parameter to the given value. if value is DEFAULT or, equivalently, RESET is used, the database-specific setting is removed, so the system-wide default setting will be inherited in new sessions 🧬 use RESET ALL to clear all database-specific settings. SET FROM CURRENT saves the session's current value of the parameter as the database-specific value.

see SET(7) and Chapter 20 for more information about allowed parameter names and values 📚

---

🌸 NOTES 🌸

it is also possible to tie a session default to a specific role rather than to a database; see ALTER ROLE (ALTER_ROLE(7)). and listen — role-specific settings override database-specific ones if there is a conflict. role wins. that's the hierarchy, periodt 💅

---

🌈 EXAMPLES 🌈

to disable index scans by default in the database test:

ALTER DATABASE test SET enable_indexscan TO off;

look at that. one line. iconic. no cap ✨

---

⭐ COMPATIBILITY ⭐

the ALTER DATABASE statement is a PostgreSQL extension. it's not standard SQL, it's our thing 🫶

---

💜 SEE ALSO 💜

CREATE DATABASE (CREATE_DATABASE(7)), DROP DATABASE (DROP_DATABASE(7)), SET(7), CREATE TABLESPACE (CREATE_TABLESPACE(7)) — go read those too, they're all linked in the group chat 🧚

---

and that's the whole page, bestie!! 🌟 brought to you by PostgreSQL 14.24 and dated 2026 — mwah 💋 go alter some databases responsibly 🎀💖