0

We have several naming schema for columns (like id/key or created/creation_date/create_date) and want to unify it.

Also we want to do it in backward compatible way. We want to be able to run old code together with new one until we get rid of old code, so following:

ALTER TABLE table_name 
RENAME COLUMN column_name TO new_column_name;

is a breaking change.

Is there some form of aliasing for columns like views for tables (for Postgresql)?

ANSWER @CL (tnx for tip!) https://www.postgresql.org/docs/12/ddl-generated-columns.html

A generated column cannot be written to directly. In INSERT or UPDATE commands, a value cannot be specified for a generated column, but the keyword DEFAULT may be specified.

So if an idea is to keep columns until you update all report script - it is fine. But if you want a rollback to previous app version - you are out of luck ((

Currently we are on Google Postgres Cloud offer: https://cloud.google.com/sql/docs/postgres/db-versions and v12 is in beta only.

1
  • 1
    PG 12 has generated columns, but their values are actually stored in the table. – CL. May 18 '20 at 10:44
1

There is no easy way to do this, but the only way I can think of, is to use a layer of views to keep the old names.

Create two schemas e.g. "old" and "new" or something alike.

The "new" schema contains the real tables with the new column (and maybe table) names. Have applications that can deal with that set the search_path to only the new schema.

For every table that has renamed columns (or if the table itself got renamed), create a view in the "old" schema with the old names, e.g.

create view old.table_name
as
select new_column_name as column_name,
       some_other_column
from table_name;

This view will be automatically updateable without the need to write triggers for it.

Make applications that can only deal with the old names set their search_path to old, new. An unqualified reference to table_name will then use the view old.table_name with the columns that the code expects. As the view is updateable, this will work for DML just as well.

Obviously this won't work if the application code checks for the presence of real tables for some reason. Or tries to detect constraints for those tables.

It also requires that you can change the search path when connecting to the database (e.g. when using JDBC, you can achieve this by passing it in the connection URL)

1
  • Considering how much effort is necessary to "fix" names the budget is never be justified for the change. It becomes a technical debt (( – gavenkoa May 18 '20 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.