PostgreSQL, merge join results

Question

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results.

Table "job" : parent_id, target_id, start_date, end_date, jobposition_id

Table "study" : parent_id, target_id, start_date, end_date

At this moment if I do something like :

SELECT *
FROM user AS u 
LEFT JOIN study AS s ON s.parent_id=u.id OR s.target_id=u.id 
LEFT JOIN job AS j ON j.parent_id=u.id OR j.target_id=u.id
WHERE u.id=2;

I got a resultset with every common column doubled (I have 2 times parent_id, target_id, start_date, end_date).

How can I get a simple resultset with only one column parent_id, etc (and a jobposition_id column set to NULL for every study result) ?

Thanks

Answer 1

I hope to have fully understood your request. With this query you get a concatenation of tables (a UNION) with all records.

SELECT parent_id, target_id, start_date, end_date, NULL AS jobposition_id FROM study
UNION ALL
SELECT parent_id, target_id, start_date, end_date, jobposition_id FROM job

You can play with UNION to obtain the merging concept you have in mind. Here's a link to the official documentation (PostgreSQL 9.4): UNION clause. I think that you would like to remove duplicates (if there are duplicate entries in both tables), so probably the UNION DISTINCT is right for you.

Answer 2

Your query uses SELECT * ... which will select every column from the tables (in the order that they exist within the tables). So if you have:

CREATE TABLE t1 (
  id bigserial primary key, -- bleh - never use just "id" .. this is only for example
  desc text
);

CREATE TABLE t2 (
  id bigserial primary key,
  t1_id bigint references t1 (id),
  desc text
);

select
  *
from t1
join t2 on t1.id = t2.t1_id

The query will give you the following columns in your result set:

id, desc, id, t1_id, desc

The simple solution is to basically NEVER use "*" ... always SPECIFY which columns you want .. and then you can also ALIAS those columns..

select
  t1.id AS t1_id,
  t1.desc AS t1_desc,
  t2.id AS t2_id,
  t2.desc AS t2_desc
from t1
join t2 on t1.id = t2.t1_id;

This query will return the following column names

t1_id, t1_desc, t2_id, t2_desc

One major problem with using "*" in your queries is that if the table structure ever changes, it will affect every single query that is using that table, which will often times break code. Where as if you are specifying the column names, the chances of that happening are significantly reduced. The resulting error messages that the database will give you are also a lot easier to understand when it comes to "what broke?"