Duplicate colum name overwritten

[KeyboardRage]

Hi, I'm having some troubles working with joins where the tables in question have identical column name.
As far as I know Postgres normally prefixes the table name on the key in these cases, but that does not happen in this lib, at least by default?

I can't find anything in the documentation, but it might just be it's too late in the night for me 😅 Any help would be appreciated.

Suppose you have this setup

CREATE TABLE cars (
    id VARCHAR(1) PRIMARY KEY,
    name VARCHAR(32)
);
CREATE TABLE drivers (
    id VARCHAR(1) PRIMARY KEY,
    name VARCHAR(32),
    car VARCHAR(1) REFERENCES cars(id)
);
INSERT INTO cars (id, name) VALUES ('x', 'car name a'),('y', 'car name b'),('z', 'car name c');
INSERT INTO drivers (id, name, car) VALUES ('a', 'driver name a', 'x'),('b', 'driver name b', 'y'),('c', 'driver name c', 'z');

And then you run this query

SELECT *
FROM cars
JOIN drivers ON cars.id = drivers.car
WHERE cars.id = 'x'

I would expect this output

[ {"cars.id": "x", "cars.name": "car name a", "drivers.id": "a", "drivers.name": "driver name a", "car": "x"} ]

... but what I get is ...

[ {id: "a", name: "driver name a", car: "x"} ]

The problem here is that I don't get the car's name. It gets overwritten by the name column of the driver.
The id also gets overwritten, but in this scenario it'd hold the same value anyway.

I could create my own parsing by using .values() and the .columns property, but it is rather tricky and error prone to have to do myself every time😬

[porsager]

That is because results are by default an array of row "objects". That means if there are duplicate names for a column, the last one wins.

You can indeed have results returned as an array of row "arrays", instead as your mention, but yes - not very ergonomic.

Also when working with Postgres.js it helps a lot to embrace the PostgreSQL json functions. Most used are row_to_json, json_agg and eg. json_build_object.

If you are in control of the schema I would also highly recommend going with a table prefixed primary key. There are so many benefits, which you can probably find plenty of discussions around elsewhere too, but look at how your example could be instead:

create table cars (
  car_id    text primary key,
  name      text
);

create table drivers (
  driver_id text primary key,
  name      text,
  car_id    text references cars
);

insert into cars (car_id, name) values ('x', 'car name a'), ('y', 'car name b'), ('z', 'car name c');
insert into drivers (driver_id, name, car_id) values ('a', 'driver name a', 'x'), ('b', 'driver name b', 'y'), ('c', 'driver name c', 'z');

select 
  cars.*,
  row_to_json(d) as driver
from cars
join drivers d using(car_id)
where car_id = 'x'

• note that I replaced your varchar usage - read more here - you can add a check if you need the length constraint

Looking at this you get postgres cross table references for free. You don't need to specify the id column of the cars table in the drivers references part, and you can use using(car_id) for your join. It also immediately becomes visible from a row what kind of object you're dealing with. This neatly solves the problem you had of the id column being overwritten in the output. As for the conflicting name in the output you can either name it eg driver_name, but you can also use row_to_json (for 1-1 relationships), or json_agg if you had more, to get a pretty output. With these things your result looks like this instead:

Result(1) [
  {
    car_id: 'x',
    name: 'car name a',
    driver: { 
      driver_id: 'a', 
      name: 'driver name a', 
      car_id: 'x' 
    }
  }
]

The idea of Postgres.js is to embrace Javascript and SQL equally, with a base premise — Don't do in Javascript, what could be done in SQL.

I hope this gives you a better kickstart, and an idea about how idiomatic usage looks.

[KeyboardRage]

This does the trick, and simplified the response :) Thanks for the explanations and examples, really helped