Re: Varying Character comparison

Joseph,

This is really an SQL question, not something specific to PostgreSQL or
pgAdmin. That being said, it looks like your e.index_entry field is of
type varchar (character varying) and you are trying to compare it to an
int. If that is the case you will need to either cast the e.index_entry
field in your queries to INT (which will fail if any aren't castable to
that type, or wrap your 2111300 in quotes (ex:'2111300' ) to make it a
string as opposed to an int.

I hope that helps,

rik.

On Wed, Jan 6, 2021 at 1:25 PM Joseph Ruffino <jruffino(at)gailborden(dot)info>
wrote:

> 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.
>