Question 236253 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – In Postgres how to join tables with columns of the same name, and alter column name?

I have two tables, A and B with a variety of columns, some of which are the same name.

CREATE TABLE A (
    id serial PRIMARY KEY,
    col_a int,
    col_b int,
    col_c int
);

CREATE TABLE A (
    id serial PRIMARY KEY,
    col_a int,
    col_b int,
    col_e int
);

I want to join the two tables on col_a, like following:

select * from A a join B b USING(col_a)

Ultimately, I want to create a view from this query create view my_view as (...). But when I do, I get an error because the columns id and col_b appear twice. Is there a way to, on the join, prepend/append a prefix/suffix to the columns of table B, so there are no name clashes?

Edit

Just to clarify, I do NOT want to use aliases because in practise my tables have O(50) columns and I want to use the wildcard operator *. This is definitely a solved problem if you use aliases, but particularly in my case where I am creating views of views of views, I want syntactic sugar to make it simpler.

Tags:

2

Answers


  1. 0

    Is there a way to prepend/append a prefix/suffix to the columns of table B, so there are no name clashes, without enumerating the columns and using alias column names for the result set?

    No. Sad but true.

    Login or Signup to reply.
  2. 0

    You can use composite values in your views, i.e. defining the view as having two columns, one with the A value and one with the B value:

    CREATE VIEW my_view AS SELECT a, b from A a join B b USING (col_a);

    They are a bit ugly to use in queries though, always requiring parenthesis to disambiguate the field selection expressions:

    SELECT (v.a).col_a, (v.a).id AS a_id, (v.b).id AS b_id FROM my_view v;
SELECT (a).col_a, (a).id AS a_id, (b).id AS b_id FROM my_view;

    (online demo)

    This is the closest you will get to a "prefix" though that can be automatically generated in the view definition. As soon as you define views of views, you should still (explicitly) name the underlying tables though not nest composite types further.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search