RE: Varying Character comparison

Joseph, 

Hi,

 

I am not sure if this is how I add a question, but I cannot find anything on lists.postgesql.org.

 

I am having a problem with a SQL that I am running in pgAdmin 4.27.  The SQL is being used to check for duplicates in our PostgreSQL DB.

 

I am trying to edit out the barcode (e.index_entry) when it is equal to 2111300.  When I run it with the above, I get and error:

ERROR: operator does not exist: character varying <> integer

LINE 66: and e.index_entry != 2111300 ^

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

 

I have tried <> and adding single quotes around 2111300. The <> gets the same error, and the single quotes ‘’ has it run for a long time.

 

Any help would be appreciated.

 

Here is the SQL we are using with the lines I use highlighted:

 

SELECT

            r.creation_date_gmt as created,

            e.index_entry as barcode,

            'p' || r.record_num || 'a' as patron_record_num,

            pn.last_name || ', ' ||pn.first_name || COALESCE(' ' || NULLIF(pn.middle_name, ''), '') || ' ' || pr.birth_date_gmt as patron,

            pr.ptype_code,

            pr.activity_gmt,

            pr.expiration_date_gmt,

            pr.mblock_code as block_code,

            pr.owed_amt::float8::numeric::money as owed_amt,

            pr.home_library_code

            -- pr.home_library_code,

 

FROM

            sierra_view.patron_record_fullname as pn

 

JOIN

            sierra_view.patron_record as pr

ON

            pr.record_id = pn.patron_record_id

 

JOIN

            sierra_view.record_metadata as r

ON

            r.id = pr.record_id

 

JOIN

            sierra_view.phrase_entry AS e

ON

            (e.record_id = r.id) AND (e.index_tag = 'b') AND (e.varfield_type_code = 'b')

 

WHERE

            pr.birth_date_gmt || pn.first_name || COALESCE(' ' || NULLIF(pn.middle_name, ''), '') || ' ' || pn.last_name

IN

(

            SELECT

 

                        p.birth_date_gmt ||

                        n.first_name || COALESCE(' ' || NULLIF(n.middle_name, ''), '') || ' ' || n.last_name as patron_name

                        -- e.index_entry,

                        -- count(*) as matches

 

            FROM

                        sierra_view.record_metadata AS r

 

            JOIN

                        sierra_view.patron_record AS p

            ON

                        p.record_id = r.id

 

            JOIN

                        sierra_view.patron_record_fullname AS n

            ON

                        n.patron_record_id = r.id

 

            -- JOIN

            -- sierra_view.phrase_entry AS e

            -- ON

            --   (e.record_id = r.id) AND (e.index_tag = 'b') AND (e.varfield_type_code = 'b')

 

            WHERE

                        r.record_type_code = 'p'

                        -- and r.creation_date_gmt >= '2017-05-01'

 

            and p.mblock_code != 'd'

            and e.index_entry  != 2111300

 

            GROUP BY

                        p.birth_date_gmt,

                        patron_name,

                        p.ptype_code

                        -- e.index_entry

 

            HAVING

            COUNT(*) > 1

)

 

-- and pr.mblock_code != 'd'

 

ORDER BY

pn.last_name || pn.first_name || pr.birth_date_gmt || COALESCE(' ' || NULLIF(pn.middle_name, ''), ''),

pr.ptype_code ASC,

pr.activity_gmt DESC

 

Joseph A. Ruffino

Gail Borden Public Library District

Web Programmer

270 N. Grove Ave.

Elgin, IL 60120

Phone: (847) 429-5986 Fax: (847) 608-5201

http://www.gailborden.info

 

PLEASE NOTE: Pursuant to Illinois State Law, e-mail communication to and from this address may be subject to public disclosure.