Subject: SQL issue after migrating from version 13 to 15

From: "Campbell, Lance" <[email-masked]>
Date: Mon, 23 Oct 2023 15:19:35 +0000

We migrated our PostgreSQL database from version 13 to 15. Absolutely no coding changes were made to our software.
table_column is a column in a table of type tsvector

The below segment of the where clause works fine if the value passed is a single value like "real":

AND to_tsvector('simple', CAST (table_column as text)) @@ to_tsquery('simple', 'real')

However, this no longer works when there are two values "real,impact". The only change was migrating from PostgreSQL 13 to 15:

AND to_tsvector('simple', CAST (table_column as text)) @@ to_tsquery('simple', 'real,impact')

No exception is being thrown.

Thanks for your help with this.

Lance Campbell
University of Illinois

---

From: Tom Lane <[email-masked]>
Date: Mon, 23 Oct 2023 11:31:17 -0400

> AND to_tsvector('simple', CAST (table_column as text)) @@ to_tsquery('simple', 'real,impact')

You really should define what you mean by "works" in a question
like this.

However, I think what you are unhappy about is that the interpretation
of that to_tsquery input has changed.  In v13:

regression=# select to_tsquery('simple', 'real,impact');
    to_tsquery     
-------------------
 'real' & 'impact'
(1 row)

In v14 and later:

regression=# select to_tsquery('simple', 'real,impact');
     to_tsquery      
---------------------
 'real' <-> 'impact'
(1 row)

The v14 release notes mention that there were incompatible changes in
this area, although they don't cite this specific case.  But anyway,
if the behavior you want is & then I'd suggest writing &, rather than
assuming that some other punctuation will behave the same.  Or you
could switch to plainto_tsquery(), which disregards the punctuation
altogether.

			regards, tom lane

---

From: "Campbell, Lance" <[email-masked]>
Date: Mon, 23 Oct 2023 15:58:13 +0000

Thanks so much for the assistance. That resolved my issue. I hope you have a great week.

Lance

-----Original Message-----
From: Tom Lane <[email-masked]> 
Sent: Monday, October 23, 2023 10:31 AM
To: Campbell, Lance <[email-masked]>
Cc: [email-masked]
Subject: Re: SQL issue after migrating from version 13 to 15

> to_tsquery('simple', 'real,impact')

You really should define what you mean by "works" in a question like this.

However, I think what you are unhappy about is that the interpretation of that to_tsquery input has changed.  In v13:

In v14 and later:

The v14 release notes mention that there were incompatible changes in this area, although they don't cite this specific case.  But anyway, if the behavior you want is & then I'd suggest writing &, rather than assuming that some other punctuation will behave the same.  Or you could switch to plainto_tsquery(), which disregards the punctuation altogether.

			regards, tom lane