How to rename table postgresql

21.08.202221.08.2022 admin 0 Comments how, how to made, как, как сделать, как создать, совими руками

How to rename table postgresql

When you create a table and you realize that you made a mistake, or the requirements of the application change, you can drop the table and create it again. But this is not a convenient option if the table is already filled with data, or if the table is referenced by other database objects (for instance a foreign key constraint). Therefore PostgreSQL provides a family of commands to make modifications to existing tables. Note that this is conceptually distinct from altering the data contained in the table: here we are interested in altering the definition, or structure, of the table.

Change default values

Change column data types

All these actions are performed using the ALTER TABLE command, whose reference page contains details beyond those given here.

5.5.1.В Adding a Column

To add a column, use a command like:

The new column is initially filled with whatever default value is given (null if you don’t specify a DEFAULT clause).

You can also define constraints on the column at the same time, using the usual syntax:

In fact all the options that can be applied to a column description in CREATE TABLE can be used here. Keep in mind however that the default value must satisfy the given constraints, or the ADD will fail. Alternatively, you can add constraints later (see below) after you’ve filled in the new column correctly.

5.5.2.В Removing a Column

To remove a column, use a command like:

Whatever data was in the column disappears. Table constraints involving the column are dropped, too. However, if the column is referenced by a foreign key constraint of another table, PostgreSQL will not silently drop that constraint. You can authorize dropping everything that depends on the column by adding CASCADE :

See SectionВ 5.13 for a description of the general mechanism behind this.

5.5.3.В Adding a Constraint

To add a constraint, the table constraint syntax is used. For example:

To add a not-null constraint, which cannot be written as a table constraint, use this syntax:

The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.

5.5.4.В Removing a Constraint

To remove a constraint you need to know its name. If you gave it a name then that’s easy. Otherwise the system assigned a generated name, which you need to find out. The psql command \d tablename can be helpful here; other interfaces might also provide a way to inspect table details. Then the command is:

As with dropping a column, you need to add CASCADE if you want to drop a constraint that something else depends on. An example is that a foreign key constraint depends on a unique or primary key constraint on the referenced column(s).

This works the same for all constraint types except not-null constraints. To drop a not null constraint use:

(Recall that not-null constraints do not have names.)

5.5.5.В Changing a Column’s Default Value

To set a new default for a column, use a command like:

Note that this doesn’t affect any existing rows in the table, it just changes the default for future INSERT commands.

To remove any default value, use:

This is effectively the same as setting the default to null. As a consequence, it is not an error to drop a default where one hadn’t been defined, because the default is implicitly the null value.

5.5.6.В Changing a Column’s Data Type

To convert a column to a different data type, use a command like:

This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast. If a more complex conversion is needed, you can add a USING clause that specifies how to compute the new values from the old.

PostgreSQL will attempt to convert the column’s default value (if any) to the new type, as well as any constraints that involve the column. But these conversions might fail, or might produce surprising results. It’s often best to drop any constraints on the column before altering its type, and then add back suitably modified constraints afterwards.

How to rename table postgresql

Change default values

Change column data types

All these actions are performed using the ALTER TABLE command, whose reference page contains details beyond those given here.

5.6.1. Adding a Column

To add a column, use a command like:

The new column is initially filled with whatever default value is given (null if you don’t specify a DEFAULT clause).

From PostgreSQL 11, adding a column with a constant default value no longer means that each row of the table needs to be updated when the ALTER TABLE statement is executed. Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten, making the ALTER TABLE very fast even on large tables.

You can also define constraints on the column at the same time, using the usual syntax:

5.6.2. Removing a Column

To remove a column, use a command like:

See Section 5.14 for a description of the general mechanism behind this.

5.6.3. Adding a Constraint

To add a constraint, the table constraint syntax is used. For example:

To add a not-null constraint, which cannot be written as a table constraint, use this syntax:

The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.

5.6.4. Removing a Constraint

This works the same for all constraint types except not-null constraints. To drop a not null constraint use:

(Recall that not-null constraints do not have names.)

5.6.5. Changing a Column’s Default Value

To set a new default for a column, use a command like:

Note that this doesn’t affect any existing rows in the table, it just changes the default for future INSERT commands.

To remove any default value, use:

This is effectively the same as setting the default to null. As a consequence, it is not an error to drop a default where one hadn’t been defined, because the default is implicitly the null value.

5.6.6. Changing a Column’s Data Type

To convert a column to a different data type, use a command like:

Postgres Rename Table

By Payal Udhani

Introduction to Postgres Rename Table

Whenever we perform manipulations on the table in the PostgreSQL database, we often feel the necessity to perform some changes on the existing table. We can add more columns to the table, modify the existing column and its related properties, add or remove the constraints, remove columns, rename the table or assign the default value to certain columns of the table, add or remove primary and foreign key constraints and rename the table. We can perform all these operations using the ALTER TABLE command in PostgreSQL. In this article, we will study the syntax of the ALTER TABLE command and view a few examples to understand how we can change the name of the table in the PostgreSQL database.

Syntax:

Hadoop, Data Science, Statistics & others

ALTER TABLE tableName
RENAME TO alteredTableName

Example of Postgres Rename Table

Let us begin by creating a new table named educba which will store the data related to the corporation after checking all the present tables in our current DB by using \dt command –

CREATE TABLE educba
(id INTEGER PRIMARY KEY,
technologies VARCHAR,
workforce INTEGER,
address VARCHAR);

Check whether table is created by \dt command that gives output –

We can rename the name of the table using the alter table command. To rename the name of the table educba to educational_platforms, we can use the syntax mentioned above of alter table command in the following way –

ALTER TABLE educba RENAME TO educational_platforms;

Let us check using \d command after firing the above command to describe the educba and educational_platforms table. This gives the following output –

\d educba;
\d educational_platforms;

As can be seen that the educba table cannot be described as it has been renamed and now none of the tables named educba exists in our database. After describing the educational_platforms it contains the same structure and keys as that of our previously created educba table. Hence, we can conclude that our table is renamed successfully.

Let us see what happens if we try to alter the table that is not present in our database, say SQL demo.

ALTER TABLE SQL demo RENAME TO education;

This will result in the error after executing the above command output is as follows –

Error saying relation name SQL demo does not exist is given as the output of executing the above command. To avoid such error, we can use IF EXISTS statement in our alter table syntax in the following way –

ALTER TABLE IF EXISTS tableName
RENAME TO alteredTableName;

This will prevent the system to throw an error if none of the tables named tableName exists in the current database. Instead, the system will raise the notice saying that no such table exists. Let us try to execute the following query statement –

ALTER TABLE IF EXISTS psqldemo RENAME TO education;

Execution of the above query statement gives the following output –

As the tables are referred to as the relations in PostgreSQL database server a notice saying – “relation name psqldemo does not exist and hence the alter table command execution is being skipped” is produced.

Let us create a table name psqldemo and then try to alter it by using the above-mentioned syntax including IF EXISTS in it.

Create a table named psqldemo containing columns such as id, schemaname, tablename, owner, creationdate and accesseddate using the following query statement –

CREATE TABLE psqldemo
(id INTEGER PRIMARY KEY,
schemaname VARCHAR,
tablename VARCHAR,
owner VARCHAR,
creationdate timestamp default now(),
accesseddate timestamp);

Executing the above command on PostgreSQL command prompt gives the following output –

Let us now try to rename the psqldemo table to education using the alter table syntax ith if exists a statement in it. The query statement will be as follows –

ALTER TABLE IF EXISTS psqldemo RENAME TO education;

after executing the query the output is as follows –

Let us try describing the psqldemo and education tables and see the output –

For describing the psqldemo fire the following command –

that now results in the following output –

Output saying that none of the relation named psqldemo exists in the current database.

Describing the education table using the following query statement

gives following output –

which resembles the structure and keys assigned by us to the psqldemo table that was created and later renamed by us. Hence, our table is renamed successfully.

Points to Remember

There are some points that you should keep in your mind while renaming any table in PostgreSQL. They are as listed below –

Conclusion

We have a versatile and variable ALTER TABLE command in the PostgreSQL database that can be used to add, remove, and modify the columns their datatypes, constraints such as not null, default, etc. We can use this method to rename the name of the table present in our current Postgres database. To avoid throwing the error when attempting to rename the table that does not exist in our database, we can make the use of IF EXISTS statement that helps is to raise a notice saying no such relation i.e table exists in the database with the specified name. We should try to achieve maximum utilization of such commands that are available in PostgreSQL to prepare efficient, robust, and consistent PostgreSQL databases.

PostgreSQL Rename Table using Simple Steps

A very simple way to rename table in PostgreSQL is using alter table statement. While changing table name you would be thinking about what will happen to indexes and other constraints on the table. Obviously these questions are obvious so lets explore more about it.

Overview of PostgreSQL Rename Table Statement

In my previous post you read about PostgreSQL Create Database statement to create new database using command line. In this post we will discuss in detail about renaming table and its impact.

PostgreSQL has rename table statement which is used with rename clause to change the name of an existing table.

Syntax:

In the above statement, first you specify the existing table name which you want to rename and secondly, specify new table name.

So lets see an example by creating simple table and renaming it.

Insert data into table

Lets Create an Index on a Table before Renaming it.

Now, Lets rename existing table and see what will happen to indexes and data to old and new table.

As we can see above, older table “test” doesn’t exist and there is no index on older table. All the data and indexes has been migrated to new table.

Now check New table data and indexes.

As we can see above, our old table has been renamed to new one and all the existing rows and indexes are automatically created on new table.

Important Point:

When you rename a table, postgresql will automatically update its dependent objects like indexes, constraints and views.

Moving table into different Schema and renaming it:

Currently, our table is into public schema. Lets create one schema with name “myschema” and move table into myschema.

As we can see our table is moved to schema called “myschema”

Now rename table “dev” which is into myschema from public schema.

In the above example, we created new schema with name “myschema” and moved table from public schema to myschema and renamed table to a different schema.

PostgreSQL Rename Table: A Step-by-Step Guide

Summary: this tutorial shows you how to rename a table by using the PostgreSQL RENAME table clause of the ALTER TABLE statement.

Overview of PostgreSQL rename table statement

To rename an existing table, you use the ALTER TABLE statement as follows:

In this statement:

If you try to rename a table that does not exist, PostgreSQL will issue an error. To avoid this, you add the IF EXISTS option as follows:

In this case, if the table_name does not exist, PostgreSQL will issue a notice instead.

To rename multiple tables, you have to execute multiple ALTER TABLE RENAME TO statements. It’s not possible to rename multiple taable using a singe ALTER TABLE statement.

PostgreSQL rename table examples

The following statement creates a new table named vendors :

Suppose each vendor or supplier belongs to a group. To manage this relationship, you need to add the supplier_groups table as follows:

To save time querying the complete supplier data, you can create a view against the suppliers and supplier_groups tables like this:

When you rename a table to the new one, PostgreSQL will automatically update its dependent objects such as foreign key constraints, views, and indexes.

Let’s check the suppliers table first:

The output shows that the suppliers table has a foreign key constraint which references the supplier_groups table.

Now, you can rename the supplier_groups table to groups as follows:

You can verify the foreign key constraint in the suppliers table by describing the suppliers table as follows:

As you can see clearly from the output, the foreign key constraint was updated and referenced the groups table instead.

The following statement shows the supplier_data view:

The output shows that the supplier_groups table in the SELECT statement of the view was also updated to groups table.

In this tutorial, you learned how to rename a table by using the PostgreSQL RENAME table clause of the ALTER TABLE statement.

Источники информации:

Как делать своими руками…

How to rename table postgresql

How to rename table postgresql