0

I have a table like this:

| id | cars                     |      owner     |
|----|--------------------------|----------------|
|  1 | {tesla, bmw, mercedes}   | Chris Houghton |
|  2 | {toyota, bmw, fiat}      | Matt Quinn     |

Is there a way to access the car table array DISTINCT values and store them in a new table without duplicate values?

I want this table

| brands |
|--------|
|  tesla |
|  bmw   |
|mercedes|
| toyota |
|  fiat  |
vkatsou
  • 73
  • 6

1 Answers1

2

I believe you are looking for this kind of statement. 

SELECT 
 DISTINCT 
  table_array.array_unnest
FROM (
  SELECT 
   UNNEST(cars)
  FROM 
   <table>
) AS table_array(array_unnest)

see demo

This indeed works but how can I store them for example in a column "brand" of a table Manufactures.

INSERT INTO 
 Manufactures 
(brand)
SELECT 
  DISTINCT 
    table_array.array_unnest
  FROM (
    SELECT 
      UNNEST(cars)
    FROM 
     <table>
  ) AS table_array(array_unnest)

see demo

Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
  • This indeed works but how can I store them for example in a column "brand" of a table Manufactures. Your solution only output the wright column. – vkatsou Apr 04 '19 at 22:46
  • Is there a way without using unnest because It gives me this error: function unnest(character varying) does not exist – vkatsou Apr 04 '19 at 23:14
  • 1
    *"Is there a way without using unnest because It gives me this error: function unnest(character varying) does not exist –"* @vkatsou i advice upgrading to a more modern PostgreSQL version? Because it seams you are still running PostgreSQL 8.3? – Raymond Nijland Apr 04 '19 at 23:18
  • Thank you for your time! – vkatsou Apr 04 '19 at 23:19
  • @vkatsou PostgreSQL 8.3 simulate/emulate [UNNEST](https://wiki.postgresql.org/wiki/Array_Unnest) method, if you can't upgrade. – Raymond Nijland Apr 04 '19 at 23:22