postgresql calling column with same name

Question

I have two tables, where they have the same ID name (I cannot change the way the tables are designed) and I'm trying to query table2's ID, how would I do this when they are joined?

create table table1(
    id          integer, -- PG: serial
    description MediumString not null,
    primary key (id)
);


create table table2 (
    id          integer, -- PG: serial
    tid         references table1(id),
    primary key (id)
);

So basically when they're joined, two columns will have the same name "id" if I do the following query

select * from table1
join table2 on table1.id = table2.tid;

Richard Huxton · Accepted Answer · 2012-04-22 08:19:00Z

7

Alias the columns if you want both "id"s

SELECT table1.id AS id1, table2.id AS id2
FROM table1...

answered Apr 22, 2012 at 8:19

Richard Huxton

23.5k5 gold badges43 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kgrittn Over a year ago

SELECT * is generally a bad idea anyway for anything other than ad hoc queries; if you use it things can break in unpredictable and surprising ways based on schema changes.

Eric · Accepted Answer · 2016-06-21 17:02:03Z

3

If you want to query all * on both tables but still be able to reference a specific id you can do that too, you will end up with duplicate id columns that you probably won't use, but in some situations if you really need all the data, it's worth it.

select table1.*, table2.*, table1.id as 'table1.id', table2.id as 'table2.id'
from ...

answered Jun 21, 2016 at 17:02

Eric

5142 gold badges9 silver badges22 bronze badges

Comments

t_motooka · Accepted Answer · 2012-04-22 03:53:41Z

0

You cannot select it using select *. try this :

select table1.id, table1.description, table2.id, table2.tid
from table1
inner join table2
 on table1.id = table2.tid

answered Apr 22, 2012 at 3:53

t_motooka

5655 silver badges14 bronze badges

Collectives™ on Stack Overflow

postgresql calling column with same name

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related