I have this data. tbl_data id value 1 A 1 B 1 C 2 A 2 C I want to select id which have ‘A’ and ‘B’ as value. SELECT id FROM tbl_data WHERE value = ‘A’ AND value = ‘…
Tag: postgresql
JOIN on Subquery Based on Date Evaluation
I have a query that includes a subquery that references one of the tables that my query joins on, but I also need to do an evaluation on the field returned from the subquery in my WHERE clause. Here’s the current query (rough example) – My subquery is currently returning the latest date from the c…
Trouble understanding a LIKE operator syntax
I got pasted a source code to extract a particular dataset and I am having a problem understanding one of the bits. Here’s the part of a query: Could you help me translate what does the inside bracket of this LIKE operator mean? Many thanks in advance. Answer COALESCE takes second argument if first one …
What’s the most efficient way to divide a table by a threshold into two sub-queries in PostgreSQL
I have a table test with schema id(int) and height(numeric) in PostgreSQL. I need to divide them by a certain height then calculate each sub-query. To my understanding the two sub-query above will iterate the table twice. In a programming language such as python, i can put data in a list then just iterate thr…
How to count missing rows in left table after right join?
There are two tables: Table education_data (list of countries with values by year per measured indicator). create table education_data (country_id int, indicator_id int, year date, value float ); …
How can I efficiently paginate the results of a complex SQL query?
I have a fairly complex SQL query which first fetches some data into a CTE and then performs several self-joins on the CTE in order to compute a value. Here’s an abberivated exmaple, with some complexities of our application simplified: The query is auto-generated and can scale to a complex computation …
Postgres LEFT JOIN not returning nulls?
Not sure why this is happening, but I have this query: And the item table has 100 elements. I’m looking for the resulting rows to be grouped by user_id and have each array be 100 items long. That is, if the value is not present in the vote table, to just instead sub in a 0 in its place. Unfortunately
PostgreSQL count of each status per day
I have the following table: I also have a table with every calendar day from 2019-11-01 to 2019-12-31. I need to find out how many occurrences of each status exist per calendar day for the time span listed above. If a status is Opened on 2019-12-14 and Pending on 2019-12-17, I need to count that it was Opened…
SQL multiple SELECTs very slow to run
I have the following query (Postgres) which gives the correct results, but it is very slow. Any ideas how I can make it faster? SELECT DISTINCT(vesselimo), vesselname, (SELECT COUNT(portid) FROM …
Select distinct values from a text column in PgAdmin [closed]
Using pgAdmin4 I imported text files to a table with 13 columns. The table columns are all of type text. The first column, named Date, contains text of dates (YYYY-MM-DD). There are multiple rows …