public inbox for [email protected]
help / color / mirror / Atom feedSQL issue after migrating from version 13 to 15
3+ messages / 2 participants
[nested] [flat]
* SQL issue after migrating from version 13 to 15
@ 2023-10-23 15:19 Campbell, Lance <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Campbell, Lance @ 2023-10-23 15:19 UTC (permalink / raw)
To: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SQL issue after migrating from version 13 to 15
@ 2023-10-23 15:31 Tom Lane <[email protected]>
parent: Campbell, Lance <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2023-10-23 15:31 UTC (permalink / raw)
To: Campbell, Lance <[email protected]>; +Cc: [email protected] <[email protected]>
"Campbell, Lance" <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: SQL issue after migrating from version 13 to 15
@ 2023-10-23 15:58 Campbell, Lance <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Campbell, Lance @ 2023-10-23 15:58 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]>
Thanks so much for the assistance. That resolved my issue. I hope you have a great week.
Lance
-----Original Message-----
From: Tom Lane <[email protected]>
Sent: Monday, October 23, 2023 10:31 AM
To: Campbell, Lance <[email protected]>
Cc: [email protected]
Subject: Re: SQL issue after migrating from version 13 to 15
"Campbell, Lance" <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-23 15:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-23 15:19 SQL issue after migrating from version 13 to 15 Campbell, Lance <[email protected]>
2023-10-23 15:31 ` Tom Lane <[email protected]>
2023-10-23 15:58 ` Campbell, Lance <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox