Postgres error when trying to join another table, casting error

Question

My first table is:

ID | Code   | Description
1  | IT     | Informatics
2  | GAM    | Gamer
3  | PROG   | Programmer

My second table looks like:

ID | Order  | Catcod
1  | 8080   | {IT,GAM}
2  | 7051   | 
3  | 5601   | PROG

Notice that the "Code" column in the first table is a Varchar column, and in the second table the Catcod column is a Varchar[] column.

The SQL i try to execute is:

SELECT *
FROM table2 RIGHT OUTER JOIN
     table1 
     ON table2.Catcod = table1.Code

The error im getting is:

ERROR:  operator does not exist: character varying[] = character varying
LINE 4:  RIGHT OUTER JOIN gc1pbcat on gc1vrkordhdr.catcod = gc1pbcat...
                                                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
********** Error **********

ERROR: operator does not exist: character varying[] = character varying
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Character: 216

Does anyone know a way how i can convert the Varchar[] into just Varchar, or maybe split the array?

Because i want to show the description of the Catcode in the Varchar[] in table2.

UPDATE

Or does someone have an awnser to my question how i can convert a Varchar[] to Varchar?

What should the condition test? If any of the elements of the array are equal to the other side? — Laurenz Albe
– Laurenz Albe, Commented Oct 5, 2016 at 11:26
I'm trying to get the descriptions out of table1 with the codes that are equal to the Catcod[] in table2. — Antiwoutertje
– Antiwoutertje, Commented Oct 5, 2016 at 11:27

klin · Accepted Answer · 2016-10-05 11:40:16Z

2

Use LEFT JOIN and any():

SELECT *
    FROM table2
    LEFT JOIN table1 on table1.code = any(table2.catcod);

 id | order |  catcod  | id | code | description 
----+-------+----------+----+------+-------------
  1 |  8080 | {IT,GAM} |  1 | IT   | InfOrmatics
  1 |  8080 | {IT,GAM} |  2 | GAM  | GaMer
  2 |  7051 |          |    |      | 
  3 |  5601 | {PROG}   |  3 | PROG | PRogrammer
(4 rows)

If you'd like to get one row for an order, use string_agg(), e.g.:

SELECT table2.id, table2.order, string_agg(description, ', ') description
    FROM table2
    LEFT JOIN table1 on table1.code = any(table2.catcod) 
GROUP BY 1, 2;

 id | order |    description     
----+-------+--------------------
  1 |  8080 | InfOrmatics, GaMer
  2 |  7051 | 
  3 |  5601 | PRogrammer
(3 rows)

edited Oct 5, 2016 at 11:40

answered Oct 5, 2016 at 11:35

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres error when trying to join another table, casting error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related