EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL ALTER DATABASE
 

PostgreSQL ALTER DATABASE

Priya Pedamkar
Article byPriya Pedamkar

PostgreSQL ALTER DATABASE

Introduction to PostgreSQL ALTER DATABASE

PostgreSQL alter database statement is used to alter the database features like changing the ownership, change the name of the database, set the new tablespace of the database, and set the configuration parameter for the database. Alter database command is essential and useful in PostgreSQL to change the feature of the database. To change the database settings, we need to privilege of database owner or admin user of the database. If we want to change the name of the database, we have used alter database command in PostgreSQL; also, if we want to change the database’s ownership, we have used the same command.

 

 

Syntax

Below is the syntax of the alter database statement in PostgreSQL.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

1. Change the name

Alter database name_of_database rename to new_database_name;

2. Change ownership

Alter database name_of_database owner to new_owner_of_the_database;

3. Change the tablespace

Alter database name_of_database set tablespace new_tablespace_name;

4. Change the defaults runtime parameter

Alter database name_of_database set configuration_parameter = value (New value of database which we have setting.)

Parameters

Below is the parameter description syntax of alter database statement in PostgreSQL.

  • Alter database: The “ALTER DATABASE” statement is used in PostgreSQL to change the features of a database.
  • Database name: We changed the features of the database, and the name of the database from which we made these changes is referred to as the database name.
  • Owner: This keyword is defined as changing the ownership of the database. We have to change the database ownership by using alter database statements. We have to change the database owner after the database creating.
  • Rename: This keyword is defined as changing the name of the database. We have to change the name of the database by using the alter database statement. We have to change the database name after database creation.
  • Tablespace: This keyword is defined as changing the tablespace of the database. We have to change the tablespace of the database by using the alter database statement. We have to change the database tablespace after database creation.
  • New tablespace name: We have defined a new tablespace name for the database.
  • Configuration parameter: Changing the configuration parameter of the database is the definition of a configuration parameter. We have to change the database configuration parameter by using the alter database statement.
  • New database name: We have defined a new database name for the database.
  • New owner: The new owner, which we have defined in the database, is the name assigned to the ownership of the database.

How to ALTER DATABASE statement work in PostgreSQL?

Below is the working of the alter database statement.

  • If we have to use alter database statements in PostgreSQL, we need to have privileges of the owner of the database or need to have admin privileges.
  • The below example shows we need to have privileges of admin or owner of the database to use the alter database statement in PostgreSQL.
psql -U db_test -d db_testing
alter database db_testing rename to db_testing_new;
psql -U postgres
alter database db_testing rename to db_testing_new;
\l+

Output:

PostgreSQL ALTER DATABASE - 1

  • The above first example shows that we have to alter the database using the username as db_test, but it will not work; it shows the error as “ERROR: must be the owner of database db_testing”.
  • In the second example, we have altered the database using the username as Postgres; after using this user, we have changed the database’s name to db_testing_new.
  • We can also set the concurrent connection limit to the database using the alter database statement in PostgreSQL.
  • We can set the session default setting by using the given configuration parameter. When you set the value to the default, new sessions inherit the system-wide settings, and they remove any database-specific settings.
  • We can also disable an index scan of the database using the alter database statement in PostgreSQL.
  • PostgreSQL uses the “ALTER DATABASE” command as an extension for modifying databases. We can change the attribute of the database by using an alter statement.

Examples

Below are the examples mentioned:

1. Change the Name

Below example shows that alter the database to change the database name. We are changing the database name of db_testing_new to db_testing using alter database statement. We have used Postgres user to change the name of the database in PostgreSQL.

PostgreSQL ALTER DATABASE - 2

2. Change the Ownership

Below example shows that alter the database to change the database ownership. We are changing the db_testing database ownership from Postgres user to db_test user using alter database statement. We have used Postgres user to change the ownership of the db_testing database in PostgreSQL.

Code:

\l+
alter database db_testing owner to db_test;
\l+

Output:

Change the ownership

3. Change the Tablespace

Below example shows that alter the database to change the database tablespace. Using the alter database statement, we are changing the db_testing database tablespace from the default tablespace to the my_test tablespace. We have used Postgres users to change the tablespace of the db_testing database in PostgreSQL.

Code:

create tablespace my_test location '/new_test';
\db
ALTER DATABASE db_testing SET TABLESPACE my_test;
\l+

Output:

Change the tablespace

4. Configuration Parameter

Below example shows that alter the database to change the database configuration parameter. We have to change the database configuration parameter to disable the index scan of db_testing using alter database statement. We have used Postgres users to change the configuration parameter of the db_testing database in PostgreSQL.

Configuration parameter

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL ALTER DATABASE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Database
  2. PostgreSQL Date Functions
  3. Data Types PostgreSQL
  4. PostgreSQL CASE Statement

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW