PostgreSQL - combining two tables

Question

Hello!

I have a PostgreSQL database with two tables named "user" and "group". They have different columns, but I want to join them.

user: id, firstname, lastname, email, registrationdate

group: id, name, desc, createdate

I can do two separate queries: select * from "user" where ... order by "registrationdate" asc; select * from "group" where ... order by "createdate" asc;

Is it possible to join these two queries into one and order all by date? The different columns could be NULL, because they do not have the same column names.

Is this possible? What I wanna do is a search in which user and groups will be displayed mixed ordered by the date.

Thanks & Best Regards.

Answer 1

You can create view and then sort by date.

Answer 2

This seems all kinda wrong but you can try

SELECT u.id, u.firstname || ' ' || u.lastname || ' ' || u.email, u.registrationdate AS DateVal
FROM user u
UNION ALL
SELECT g.id, g.name || ' ' || g.desc, g.createdate
FROM group g
ORDER BY 3

Answer 3

Maybe a VIEW? Something similar to this (I'm not sure if you have to give all the columns names):

CREATE VIEW
  myview
AS
  SELECT
    "user" as type,
    t1.id,
    t1.username,
    t1.firstname,
    t1.lastname,
    registrationdate as thedate,
    null,
    null,
    null
  FROM t1
UNION ALL
  SELECT
    "group" as type, 
    null,
    null,
    null,
    null,
    createdate as thedate,
    t2.id,
    t2.name,
    t2.desc
;

And then select:

SELECT * FROM myview ORDER BY thedate ASC;

Answer 4

Are you doing this for efficiency? If so, consider whether two separate queries would be both simpler, and possibly as fast or faster than making postgres jump through hoops. Especially, I've seen exactly zero systems in my life that were limited by the ability of the clients to do computation. The database, however, is often your performance Achilles heel.

Also, you will thank yourself later if you avoid column and table names that are postgres reserved words (user, desc). Having to quote those names in psql is a pain.

# create temporary table foo (i int);
# create temporary table bar (j int);
# insert into foo values (1);
# insert into foo values (2);
# insert into foo values (3);
# insert into bar values (3);
# insert into bar values (4);
# insert into bar values (5);
# select * from (select i, null as j from foo union select null, j from bar) baz order by coalesce(i, j);
 i | j
---+---
 1 |
 2 |
 3 |
   | 3
   | 4
   | 5