Renaming multiple columns in one statement with PostgreSQL
No.
While other actions can be combined, that's not possible with RENAME
. The manual:
All the forms of
ALTER TABLE
that act on a single table, exceptRENAME
,SET SCHEMA
,ATTACH PARTITION
, andDETACH PARTITION
can be
combined into a list of multiple alterations to be applied together.
Since RENAME
is a tiny operation on a system catalog, there is no harm in running multiple statements. Do it in a single transaction to minimize locking overhead and avoid race conditions with intermediate states (which are then invisible to all other transactions).
Other actions like ALTER COLUMN ... SET TYPE
are potentially expensive because they may have to rewrite the whole table. With big tables it would be wise to do as much as possible in a single statement.
Renaming multiple columns in PostgreSQL
There's no single command aproach. Obviously you could type multiple comands for RENAME
by your self, but let me intoduce some improvement:) As I said in this answer
...for all such bulk-admin-operations you could use PostgreSQL system tables to generate queries for you instead of writing them by hand
In your case it would be:
SELECT
'ALTER TABLE ' || tab_name || ' RENAME COLUMN '
|| quote_ident(column_name) || ' TO '
|| quote_ident( '_1' || column_name) || ';'
FROM (
SELECT
quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name,
column_name
FROM information_schema.columns
WHERE
table_schema = 'schema_name'
AND table_name = 'table_name'
AND column_name LIKE '\_%'
) sub;
That'll give you set of strings which are SQL commands like:
ALTER TABLE schema_name.table_name RENAME COLUMN "_settingA" TO "_1_settingA";
ALTER TABLE schema_name.table_name RENAME COLUMN "_settingB" TO "_1_settingB";
...
There no need using table_schema
in WHERE
clause if your table is in public
schema. Also remember using function quote_ident()
-- read my original answer for more explanation.
Edit:
I've change my query so now it works for all columns with name begining with underscore _
. Because underscore is special character in SQL pattern matching, we must escape it (using \
) to acctually find it.
Alter Multiple column Name and Data Type in PostgreSQL in one query
While you can change the data type of several columns in one ALTER TABLE
statement, renaming a column can only be done one at a time. So you will have to use several ALTER TABLE
statements.
I would recommend to run all statements in a single transaction, that way you have to acquire the ACCESS EXCLUSIVE
lock only once.
Postgres pgAdmin multiple column renames at once
You can write a query to fetch the column name and then to format and run a command using the results. It makes use of psql \gexec
parameter.
SELECT format('ALTER TABLE footballer RENAME COLUMN %I to %I', column_name, substring(column_name,length('footballer_')+1))
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'footballer'
AND column_name like 'footballer_%';\gexec
How to rename two columns inside a single transaction in PostgreSQL?
Just do it. Start a transaction, do what you want, end the transaction.
begin;
alter table foo rename name to old_name;
alter table foo rename new_name TO name;
commit;
PostgreSQL query to rename and change column type with single query
In PostgreSQL, ALTER TABLE
can take a series of operations. So:
ALTER TABLE <tablename> RENAME <oldcolumn> TO <newcolumn>;
ALTER TABLE <tablename> ALTER COLUMN <columnname> TYPE <newtype>;
is the same as
ALTER TABLE <tablename>
ALTER COLUMN <columnname> TYPE <newtype>
RENAME <oldcolumn> TO <newcolumn>;
However... why? IIRC the rename won't cause a full-table scan, so there's no benefit over just doing the two statements separately, within one transaction. What problem are you actually trying to solve with this?
Syntax error while using multiple rename RENAME expressions postgresql
You need to use multiple ALTER
statements:
ALTER TABLE table_name
RENAME COLUMN old_col_a TO new_col_a;
ALTER TABLE table_name
RENAME COLUMN old_col_b TO new_col_b;
ALTER TABLE
All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into a list of multiple alterations to be applied together. For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large tables, since only one pass over the table need be made.
Rename all columns from all tables with specific column name in PostgreSQL?
If you have superuser privileges you can make the changes in one sweep in the system catalogs:
UPDATE pg_attribute
SET attname = 'location_name'
WHERE attname = 'location';
Related Topics
Using a SQL Server for Application Logging. Pros/Cons
Sql: How to Order Null and Empty Entries to The Front in an Orderby
How to Avoid SQL Query Timeout
What Is Db/Development_Structure.SQL in a Rails Project
How to Do a SQL Update in Batches, Like an Update Top
Sql: Count() Based on Column Value
Joining Multiple Common Table Expressions
Reshape from Wide to Long in Big Query (Standard Sql)
Geometry and Geography Difference SQL Server 2008
Rake Task to Truncate All Tables in Rails 3
Insert-Output Including Column from Other Table
Copy Data from One Existing Row to Another Existing Row in SQL
Oracle SQL: Understanding the Behavior of Sys_Guid() When Present in an Inline View
Using SQL Query to Determine If a Table Exists
When SQL Developer Is Idle I Lose My Connection
How to Select Only Row with Max Sequence Without Using a Subquery