4

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?

  • Is the column foo a unique column name in the database, i.e. not found on other tables? Do you have functions and views using the column or just scripts on your terminal and/or application? Would a find-replace for your scripts be a feasible option? If not, why not? – Ziggy Crueltyfree Zeitgeister Apr 15 '16 at 0:35
7

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

  • This won't work if the script does inserts/updates into this column. – hayd Jun 19 '19 at 21:18
4

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
$$BEGIN
EXECUTE (SELECT 'SET search_path = override,' || setting
         FROM pg_catalog.pg_settings WHERE name = 'search_path');
END$$;

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"

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:

SQL Fiddle.

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.