I’m trying to select random strings. The problem is it returns the same value for each row. Why is that and how to fix? 10 rows Answer Postgres overoptimizes the subquery. I think this is an error, because it is missing the fact that random() is volatile. A simple fix is a correlation clause: I rewrote …
Tag: postgresql
Case when date = current_date then text
I have a table like this: But when I execute this query: I get this result: SQL Fiddle I would expect that in the rows where is_equal is true, the result should be TODAY. What am I doing wrong? Answer Nevermind, I solved it, but I don’t want to discard the question. The solution is to CAST(token_date as…
How to make separately unique each column in postgresql?
I know we can apply primary key to a column to provide uniqueness for a row and we can apply multiple primary keys and get a composite key. But this didn’t work for my case. I have userID and email columns. And I want them to be unique at the same time. When I applied primary key attribute to both
JPA Batch inserts with non auto generated id
im triying to batch insert a few Million Entitys. The Batch insert kind of works, but my programm executes a few JDBC Statements in the background which i dont want. } my Repository: my Entity: my JPA Settings: The Batch Insert Does work, but if i try to Upload 100 Entitys i have 33 JDBC Statements which are …
How to transpose column to row and duplicate the original column header?
I have a table for example as below, the alphabet is header and the numbers are its records a b c d e f ——————— 3 4 5 6 2 3 5 3 2 9 8 7 I want to …
Check constraint before insert a value into the database
I’m trying to create a table to store order_details. The table has a column status that should only be allowed to contain the value true once per each order_id, but may contain the value false multiple times per order_id. I want to do this within the table structure itself. Any help would be appreciated…
How to select count of 0s, 1s, and both 0s and 1s in a postgres table column?
Say there’s a table that has columns named binary_value, name, and created_at along with the id column. Here’s the SQL Fiddle for this question: http://sqlfiddle.com/#!15/d15d1/36 What would be an efficient query to get a result like the following? So far, I’ve got: Which gives it in column …
How to check if two columns are consistent in a table. sql
I’m struggling to ask the question so I will just put an example table. Basically, if I have two columns with headings person and insured car, how can I check if the same person consistently insures the same brand of car. So basically in this table I want to filter out person 0 because he insures both T…
Find the row violating the constraint to be added
I’m trying to add a check constraint to a table like ALTER TABLE foo ADD CONSTRAINT bar CHECK (…); and I get the error: ERROR: check constraint “bar” is violated by some row ********** Error ****…
SQL : IN operator vs multiple ORs
There is a behaviour I would like to understand for good. Query #1: Output : 504 Query #2 Output : 87 Query #3 Output : 0 I want to understand why am I getting zero in the third query. Based on this, the ( , ) is equivalent to a multiple OR. Isn’t this OR inclusive ? Answer the (