Tables are the most frequently used database objects in any database, including PostgreSQL. Every table must have an owner. In Postgres, a user who creates a database object like tables, views, etc. is referred to as the owner of that particular object. However, the owner of any particular object can be changed when needed. For this purpose, the “ALTER TABLE” must be executed with the “OWNER TO” clause.
This article will present a step-by-step guide on how to change the owner of a specific Postgres table using the ALTER TABLE command.
How to Change or ALTER the Table Owner in Postgres?
To change or modify the table’s owner, use the “ALTER TABLE” command followed by the selected “table’s name”. After that, use the “OWNER TO” clause followed by the new owner’s name. The following syntax will help you clarify this concern:
ALTER TABLE tab_name OWNER TO new_owner_name;
The table’s owner, a superuser, or a user with the “ALTER TABLE” permissions can change the owner of a specific table.
The below-listed steps will help you alter the table’s owner efficiently:
Step 1: Review the Table’s Existing Owner
Type the “\dt” command to see the current/original owner of the “employee_information” table:
\dt employee_information;
The output depicts that the “employee_information” table is owned by the “postgres” user.
Step 2: Review Available Users
Type the “\du” command and hit the “ENTER” button to see the available users:
\du;
The output shows the available users along with their attributes.
Step 3: Change the Table’s Owner
Suppose we have to change the table’s owner from “postgres” to “sample_user”. For this, type the following “ALTER TABLE” command and press the “ENTER” button:
ALTER TABLE employee_information OWNER TO sample_user;
The above snippet depicts that the “ALTER TABLE” command was executed successfully.
Step 4: Verify the Table’s New Owner
Now type the “\dt” command followed by “employee_information” to see the owner of the table:
\dt employee_information;
The output shows that the owner of the specified table has been changed to “sample_user”.
Conclusion
To change or modify the table’s owner in PostgreSQL, use the “ALTER TABLE tab_name OWNER TO new_owner_name;” command. To alter the table’s owner, the user must be a superuser, or he must have the “ALTER TABLE” permissions. In PostgreSQL, the “\dt” command can be used to verify the owner of a particular table. This article has presented a step-by-step guide on how to alter the table’s owner in Postgres.
