6

I have a column named foo in a table. I have constructed queries in scripts, which use this name.

I would now like to rename the column in the table to bar. This will break the scripts (there are several, but they could be changed if necessary). The table is referenced by other tables. I'd like to rename because the context of what is stored in that column has changed.

Is it possible to have two names for a column?

Paul White
  • 94,921
  • 30
  • 437
  • 687
SabreWolfy
  • 949
  • 1
  • 7
  • 16

2 Answers2

8

I will assume your table is named baz and is in schema public

create table baz (
  foo text primary key
);

insert into baz (foo) values ('hello');

select foo from baz;

Now you want to rename that column:

alter table baz rename column foo to bar;

But now your scripts are broken:

select foo from baz; -- nope!

So create a new schema, put a view in it, and alter your search_path to hide the table "behind" the view. 

create schema qux;

create or replace view qux.baz as 
    select
      bar as foo
    from public.baz;

set search_path to qux, public;

select foo from baz; -- works!

This is kind of the postgres way to do create synonym

Neil McGuigan
  • 8,653
  • 5
  • 42
  • 57
8

Depending on what you do exactly in your scripts, there are ways to work around it. I'll assume SELECT only for the purpose of this answer. But INSERT, UPDATE, DELETE are possible, too ...

VIEW

A VIEW would be an option, like @Andriy commented. If you need the current table to stay in place as is, things get more complicated, but still possible. You could mask the table with a VIEW of the same name in another schema listed before the schema of the table in the schema search_path. I actually did that on several occasions to preserve interfaces I could not adapt after updates to the DB schema.

Basic sample code:

CREATE SCHEMA override;

CREATE VIEW override.tbl AS
SELECT *, foo AS bar       -- to add the column a second time
    -- tbl_id, foo AS bar  -- to replace the column
FROM tbl;

Only grant the USAGE privilege on the schema to public to avoid abuse:

GRANT USAGE ON SCHEMA override TO public;

Put override as 1st schema in your search_path and your original query "magically" includes another column. If you don't want to mess with people's search_path, you can do that dynamically in your scripts at the start of each session:

DO
$do$
BEGIN
   EXECUTE (SELECT 'SET search_path = override,' || setting
            FROM pg_catalog.pg_settings WHERE name = 'search_path');
END
$do$;

This is safe against SQL injection, because the subquery returns a valid search path.

Similar use case masking a function:

TEMPORARY VIEW

Or you can just create a temporary view to mask the table:

CREATE TEMP VIEW tbl AS
SELECT *, foo AS bar FROM tbl;

Temporary objects are only visible inside the same session and die with it.

You may need to do more for INSERT, UPDATE, DELETE. Most utility commands (DML) can't be fooled like that.

"Generated column"

Update: the feature was added with Postgres 12. See:

Original answer:

Postgres does not have generated columns per se, but you can use a function that looks and works just like one:

CREATE TABLE tbl (tbl_id int, foo text);
INSERT INTO tbl VALUES (1, 'hello');

CREATE FUNCTION bar(tbl) RETURNS text LANGUAGE sql STABLE AS 'SELECT $1.foo';


SELECT *, t.bar
FROM   tbl t;

Detailed explanation:

fiddle
Old sqlfiddle

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633