Varying Character comparison

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Varying Character comparison

Joseph Ruffino

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.
Reply | Threaded
Open this post in threaded view
|

Re: Varying Character comparison

richard coleman
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

RE: Varying Character comparison

Joseph Ruffino

Rik,

 

Is there a user list I can send this too?

 

It is cast, “as barcode” in line 2, but it will not accept it when I do barcode != 2111300.  I have tried adding quotes around ‘2111300’, and it has been running for 30+ minutes.  Do you know if another way to cast it?

 

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

 

From: richard coleman <[hidden email]>
Sent: Wednesday, January 6, 2021 12:42 PM
To: Joseph Ruffino <[hidden email]>
Cc: [hidden email]
Subject: 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 <[hidden email]> 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.

PLEASE NOTE: Pursuant to Illinois State Law, e-mail communication to and from this address may be subject to public disclosure.
Reply | Threaded
Open this post in threaded view
|

Re: Varying Character comparison

Rob Richardson-3
You could try reddit.com/r/sql

RobR

On Wednesday, January 6, 2021, 01:55:23 PM EST, Joseph Ruffino <[hidden email]> wrote:


Rik,

 

Is there a user list I can send this too?

 

It is cast, “as barcode” in line 2, but it will not accept it when I do barcode != 2111300.  I have tried adding quotes around ‘2111300’, and it has been running for 30+ minutes.  Do you know if another way to cast it?

 

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

 

From: richard coleman <[hidden email]>
Sent: Wednesday, January 6, 2021 12:42 PM
To: Joseph Ruffino <[hidden email]>
Cc: [hidden email]
Subject: 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 <[hidden email]> 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.

PLEASE NOTE: Pursuant to Illinois State Law, e-mail communication to and from this address may be subject to public disclosure.
Reply | Threaded
Open this post in threaded view
|

Re: Varying Character comparison

JackRG1
In reply to this post by Joseph Ruffino
Joseph,

What you quoted “as barcode” is NOT a “cast”, that is an alias, which is about naming a value in order to facilitate referencing it elsewhere. A “cast” is about stating explicitly what type you want the value converted to. For example cast(barcode as int) takes the “varchar” barcode and tries to convert it to an integer number. As Rik pointed out, if there are any entries in barcode that contain non-digits, this will fail.

The easier (and more reliable) way is to change your comparison to barcode != ‘2111300’. By quoting the immediate value, you are telling SQL that this is a string, and a string can be compared to a varchar (with is nothing more than a variable-length string of characters).

Jack

On Jan 6, 2021, at 10:55 AM, Joseph Ruffino <[hidden email]> wrote:

Rik,
 
Is there a user list I can send this too?
 
It is cast, “as barcode” in line 2, but it will not accept it when I do barcode != 2111300.  I have tried adding quotes around ‘2111300’, and it has been running for 30+ minutes.  Do you know if another way to cast it?
 
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
 
From: richard coleman <[hidden email]> 
Sent: Wednesday, January 6, 2021 12:42 PM
To: Joseph Ruffino <[hidden email]>
Cc: [hidden email]
Subject: 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 <[hidden email]> 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
 
PLEASE NOTE: Pursuant to Illinois State Law, e-mail communication to and from this address may be subject to public disclosure.
PLEASE NOTE: Pursuant to Illinois State Law, e-mail communication to and from this address may be subject to public disclosure.