alter_database(7)

1ALTER DATABASE(7)                SQL Commands                ALTER DATABASE(7)
2
3
4

NAME

6       ALTER DATABASE - change a database
7
8

SYNOPSIS

10       ALTER DATABASE name [ [ WITH ] option [ ... ] ]
11
12       where option can be:
13
14           CONNECTION LIMIT connlimit
15
16       ALTER DATABASE name RENAME TO newname
17
18       ALTER DATABASE name OWNER TO new_owner
19
20       ALTER DATABASE name SET TABLESPACE new_tablespace
21
22       ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
23       ALTER DATABASE name SET configuration_parameter FROM CURRENT
24       ALTER DATABASE name RESET configuration_parameter
25       ALTER DATABASE name RESET ALL
26
27

DESCRIPTION

29       ALTER DATABASE changes the attributes of a database.
30
31       The  first  form  changes certain per-database settings. (See below for
32       details.) Only the database owner or a superuser can change these  set‐
33       tings.
34
35       The  second  form  changes  the name of the database. Only the database
36       owner or a superuser can rename a database; non-superuser  owners  must
37       also  have  the  CREATEDB  privilege.  The  current  database cannot be
38       renamed. (Connect to a different database if you need to do that.)
39
40       The third form changes the owner of the database.  To alter the  owner,
41       you  must  own  the database and also be a direct or indirect member of
42       the new owning role, and you must have the CREATEDB  privilege.   (Note
43       that superusers have all these privileges automatically.)
44
45       The  fourth  form changes the default tablespace of the database.  Only
46       the database owner or a superuser can do this; you must also have  cre‐
47       ate  privilege  for  the new tablespace.  This command physically moves
48       any tables or indexes in the database's old default tablespace  to  the
49       new tablespace. Note that tables and indexes in non-default tablespaces
50       are not affected.
51
52       The remaining forms change the session default for a run-time  configu‐
53       ration  variable  for  a PostgreSQL database. Whenever a new session is
54       subsequently started in that database, the specified value becomes  the
55       session  default  value.  The database-specific default overrides what‐
56       ever setting is present in postgresql.conf or has  been  received  from
57       the  postgres  command line. Only the database owner or a superuser can
58       change the session defaults for a database. Certain variables cannot be
59       set this way, or can only be set by a superuser.
60

PARAMETERS

62       name   The name of the database whose attributes are to be altered.
63
64       connlimit
65              How many concurrent connections can be made to this database. -1
66              means no limit.
67
68       newname
69              The new name of the database.
70
71       new_owner
72              The new owner of the database.
73
74       new_tablespace
75              The new default tablespace of the database.
76
77       configuration_parameter
78
79       value  Set this database's session default for the specified configura‐
80              tion  parameter  to  the  given  value.  If value is DEFAULT or,
81              equivalently, RESET is used, the  database-specific  setting  is
82              removed, so the system-wide default setting will be inherited in
83              new sessions. Use RESET ALL to clear all database-specific  set‐
84              tings.   SET  FROM  CURRENT saves the session's current value of
85              the parameter as the database-specific value.
86
87              See SET [set(7)] and in the documentation for  more  information
88              about allowed parameter names and values.
89

NOTES

91       It  is also possible to tie a session default to a specific role rather
92       than to a database; see ALTER ROLE [alter_role(7)].  Role-specific set‐
93       tings override database-specific ones if there is a conflict.
94

EXAMPLES

96       To disable index scans by default in the database test:
97
98       ALTER DATABASE test SET enable_indexscan TO off;
99
100

COMPATIBILITY

102       The ALTER DATABASE statement is a PostgreSQL extension.
103