CODEWITHSUNDEEP
sqlpostgresqlcastingsubquery
Tytire Recubans
Tytire Recubans

Reputation: 997

Fail to add WHERE constraint on result of nested query - ERROR 42883 in postgres

This seems like a pretty straight forward nested query to me - but clearly there is something that I am missing entirely.

SETTING:

given that the tables are pretty big, I use some prior knowledge to reduce the number of company numbers to be matched:

Plus

so:

SELECT *
INTO ccod_psc
FROM psc
WHERE (SELECT DISTINCT psc.company_number)
          in
      (SELECT DISTINCT stacked_company_numbers
       FROM (SELECT company_number_1
             FROM ccod
             WHERE company_number_1 is not null
             UNION ALL
             SELECT company_number_2
             FROM ccod
             WHERE company_number_2 is not null) AS stacked_company_numbers
       WHERE char_length(stacked_company_numbers::text) = 8);

[42883] ERROR: operator does not exist: text = record
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 86

Now the words of this error are pretty self-explanatory, but I have no idea how to fix it, and have tried a lot of other answers related to error 42883, but unsuccessfully.

Thanks for any bit of help.

-- EDIT:

I also found a way to make my query work:

SELECT *
INTO ccod_psc
FROM psc
WHERE (SELECT DISTINCT psc.company_number)
          in
      (SELECT company_number_1
       FROM ccod
       WHERE company_number_1 is not null
       UNION -- this takes cares of the duplicates.
       SELECT company_number_2
       FROM ccod
       WHERE company_number_2 is not null) AS stacked_company_numbers
WHERE char_length(stacked_company_numbers::text) = 8)

Upvotes: 1

Views: 215

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think two left joins is more efficient:

SELECT p.*
INTO ccod_psc c 
FROM psc p LEFT JOIN
     ccod c1
     ON p.company_number = c1.company_number_1 LEFT JOIN
     ccod c2
     ON p.company_number = c2.company_number_2 AND
        c1.company_number_1 IS NULL
WHERE c1.company_number_1 IS NOT NULL OR
      c2.company_number_2 IS NOT NULL

The advantage of this approach is that this can use indexes on ccod(company_number_1) and ccod(company_number_2).

EDIT:

If there can be duplicates in ccod, then exists might have better performance:

SELECT p.*
INTO ccod_psc c 
FROM psc p
WHERE EXISTS (SELECT 1
              FROM ccod c1
              WHERE p.company_number = c1.company_number_1
             ) OR
     EXISTS (SELECT 1
              FROM ccod c2
              WHERE p.company_number = c2.company_number_2
             ) ;

Upvotes: 1

GMB
GMB

Reputation: 222392

I do suspect that exists does what you want:

select p.*
from psc p
where 
    char_length(p.company_numbers::text) = 8
    and exists (
        select 1
        from ccod c
        where p.company_number in (c.company_number_1, c.company_number_2)
    )

The query pulls out records from psc whose company_number are 8 characters long and can be found in either company_number_1 or company_number_2 in table ccod.

Upvotes: 1

Related Questions