Win a copy of Kotlin for Android App Development this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Devaka Cooray
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

PostgreSQL "OR" update to "UNION ALL"  RSS feed

 
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I have a postgressql query using "OR" and I would like to write it again but by using "UNION ALL" because of the performance.

The query I have is like this one:



Then I wrote this one:



Particularly the way I wrote about the parameters is not working and as an output we get no rows.



Any idea of how should this parameters should be writen please?

Regards, Isaac
 
Sheriff
Posts: 5688
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By "parameters is not working" do you mean that you have Java code using a prepared statement?  If so, can you post the prepared statement code and the SQL query string?
 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I cant at the moment but what i mean is that the way i am writing the quotation marks '?'

It takes it as a literal. I nee use another syntax.

Any idea, please?
 
Bartender
Posts: 20356
111
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would be surprised if UNION ALL gives better performance than a simple "OR" query, since it's effectively 2 queries and a merge, but some EXPLAIN analysis could confirm.

The '?' on what was supposed to be raw SQL threw me too. But I think I recall that using ? with LIKE-style operations gave me problems. Like whether to put the '%' on the SQL or in the value to be substituted.
 
Knute Snortum
Sheriff
Posts: 5688
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Isaac Ferguson wrote:I cant at the moment but what i mean is that the way i am writing the quotation marks '?'

It takes it as a literal. I nee use another syntax.

Any idea, please?


I don't think the query string needs single quotes around the ? if you are using setString() with a PreparedStatement.
 
Tim Holloway
Bartender
Posts: 20356
111
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:
I don't think the query string needs single quotes around the ? if you are using setString() with a PreparedStatement.


Now that you mention it, I don't either.
 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
On the previous messages I have used mock values instead if ?, and it is working with a correspondant PreparedStatement.

On the another hand, I would like to select only once the appointmentid with news.


The desired output is appointmentid3,appointmentid4, appointmentid2. But I get for rows like this;


The are grouped by appointmentid but appointmentid3 should be jumped.



Any idea, please?
 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The thing is that also when the query one provides de same result that the query two, only one value should be provided.
 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For example, particularly in this case speakerid5 and lisenerid5 are grouped by appointmentid which it is also 5 in my mock database. So the undesired result I am gettting is two rows (one comin from each select) instad of just the newest one.



 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This another query which in theory avoid duplicates but when I try it I get no rows at all



In the table news  there are several rows, for example row1->speakerid1->lisenerid1

A colleague commented me that this should work... for me it makes no so much sense...but I find it interesting...

Any comment, please?
 
Knute Snortum
Sheriff
Posts: 5688
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Isaac Ferguson wrote:
The desired output is appointmentid3,appointmentid4, appointmentid2. But I get for rows like this;


The are grouped by appointmentid but appointmentid3 should be jumped.



Any idea, please?


I don't think that UNION ALL is helping here.  I believe it just returns the output of the two queries "stuck" together.  Since both queries have appointmentid, why not a simple JOIN?
 
Knute Snortum
Sheriff
Posts: 5688
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Isaac Ferguson wrote:This another query which in theory avoid duplicates but when I try it I get no rows at all



In the table news  there are several rows, for example row1->speakerid1->lisenerid1

A colleague commented me that this should work... for me it makes no so much sense...but I find it interesting...

Any comment, please?


I'm not sure about that WHERE clause.  What is it trying to do?  It would make more sense like this:
 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The performance with JOIN is worst, I search for the best possible performance.
 
Knute Snortum
Sheriff
Posts: 5688
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even with indexing?
 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By using EXPLAIN ANALYZE the best option is AND even over Union All
 
Angus Ferguson
Ranch Hand
Posts: 1301
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Finally the best performance was with AND
 
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!