#Postgresql rename update#
If it's not, it's resolved according to the current search_path, just like ALTER TABLE would. 1 Answer Sorted by: 17 Quote from the manual The RENAME form changes the name of the index (.) There is no effect on the stored data (emphasis mine) So yes, this is a simple update to the system catalog. The table name can optionally be schema-qualified. Variables are handled safely (no SQL injection). Enter table and column names in the DECLARE section.įor repeated use, I would create a function and pass parameters instead of the variables. RAISE NOTICE 'Column % of table % not found!', quote_ident(_colname), _tbl ĭoes exactly what you ask for. _new_colname text := 'cust_date_mean' - exact, case sensitive, no double-quotingĮXECUTE format('ALTER TABLE %s RENAME COLUMN %I TO %I', _tbl, _colname, _new_colname) _colname name := 'cust_mean' - exact, case sensitive, no double-quoting _tbl regclass := 'public.tbl' - not case sensitive unless double-quoted While there is no built-in feature, you can use a DO statement: DO
(In the meantime I will clarify things with the team so it's not a big deal if such command doesn't exist but I think it could help). If the column has already the good name (here cust_date_mean), the rename command that must be applied only on the wrong name should be properly skipped and not issuing the following error: db_1 | ERROR: column "cust_mean" does not existĭb_1 | STATEMENT: ALTER TABLE tablename RENAME COLUMN cust_mean TO cust_date_mean ĭb_1 | ERROR: column "cust_mean" does not exist
Hence the idea of using an IF EXISTS statement on the column name while trying to rename it. I would be glad we could, because I'm facing an error that depends on who made and gave me an SQL script, for which in some cases everything is perfectly fine (when the column has the wrong name, name that will actually be changed using a RENAME statement), and in other cases not (when the column already has the right name). In Postgres 9.I couldn't find in the PostgreSQL documentation if there is a way to run an: ALTER TABLE tablename RENAME COLUMN IF EXISTS colname TO newcolname statement. ALTER TABLE example2 RENAME example1fk TO example3fk - rename column PostgreSQL doesn’t provide the IF EXISTS option for the RENAME COLUMN command. The RENAME COLUMN command allows us to rename a single or multiple columns. But the column name will likely have to change in your example, too. PostgreSQL provides a RENAME COLUMN clause that is used with the collaboration of ALTER TABLE command to rename a column. No reference to the referenced table name. However, since you did not specify a constraint name at creation time, the system picked a default, which is example2_example1fk_fkey in your case unless the name was taken. You can ignore that, or you may want to rename the constraint so it's not misleading.
The name of the constraint remains unchanged. Changing table names will not impair functionality at all. Internally, every object is stored in a catalog table and the OID (internal primary key) of the object is used for everything else.Īccordingly, a FOREIGN KEY reference is stored in the catalog tables pg_constraint (the constraint itself incl. Internal dependencies between tables and / or other objects are never bound to the object name.
0 kommentar(er)
