[PostgreSQL] How to select distinct from multiple columns

Question

Hi I'm sure there's query for this kind of operation, but I just can't seem to get it.
I have a table like this:

product_id | saved_by | deleted_by | accepted_by | published_by
-----------+----------+------------+-------------+-------------
 1         | user1    |            | user1       |
-----------+----------+------------+-------------+-------------
 2         |          | user2      |             |
-----------+----------+------------+-------------+-------------
 3         | user2    |            |             | user3
-----------+----------+------------+-------------+-------------
 4         |          |            | user1       | user4
-----------+----------+------------+-------------+-------------

And I'm trying to create a query that would select all the users in those fields distinctly resulting in something like this:

users
------
user1
user2
user3
user4

Any pointers?

Answer 1

First thing that comes to mind is to UNION the columns:

  SELECT t.users
  FROM
  (
  SELECT saved_by AS users
  FROM table
  UNION 
  SELECT deleted_by
  FROM table
  UNION 
  SELECT accepted_by
  FROM table
  UNION 
  SELECT published_by
  FROM table
  ) AS t;

http://postgresql.org/docs/9.0/interactive/sql-select.html

As a side note if this data was normalized it would be much easier to get the data the way you want.