RE: SQL issue after migrating from version 13 to 15

From: "Campbell, Lance" <lance(at)illinois(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: RE: SQL issue after migrating from version 13 to 15
Date: 2023-10-23 15:58:13
Message-ID: SJ0PR11MB5629FF3A0251014B14EBEB79DED8A@SJ0PR11MB5629.namprd11.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Lance

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Monday, October 23, 2023 10:31 AM
To: Campbell, Lance <lance(at)illinois(dot)edu>
Cc: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Re: SQL issue after migrating from version 13 to 15

"Campbell, Lance" <lance(at)illinois(dot)edu> writes:
> 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')

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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Shaozhong SHI 2023-10-25 19:57:03 Concurrently run scipts
Previous Message Tom Lane 2023-10-23 15:31:17 Re: SQL issue after migrating from version 13 to 15