I have a query that should return some rows, but it returns blank. As far as I know .. the pictures down will show the issue. Here is the query This is the table : I tested the calculation and it worked a lone . Answer Your condition cannot be right because you are comparing M.PLAYED_AT to be BETWEEN M.PLAYED_AT
Tag: postgresql
Gist index in PostgreSQL only works on order, but not on where predicate
I’ve got a plain table with LatLon column containing a point with object location in space with GiST index created on this column: I’ve got a query on selecting only points within some distance to the point with fixed coordinates: Although explain on such query shows the index is not being used during execution: At the same time, executing a
Add information to one table from table contains duplicates
I have the following table: In Table_1, (ID, Name) pairs can repeat and have any combination Table_1: ID Name Value1 Value2 1 John 34 45 1 John 15 78 2 Randy 67 12 2 Randy 40 46 1 Randy 23 85 2 Holmes 10 100 I want to find all information for all unique pairs. So the output should be:
Filtering unique values
Consider the following table: Column_A Column_B Column_C 1 UserA NULL 2 UserB NULL 3 UserC 1 4 UserA 1 5 UserB NULL 6 UserB 2 7 UserC 2 I’d like to return all rows (Column_A, Column_B, Column_C) such that either: Column_C is NULL, or for every unique value in Column_C, return the first row with Column_B == UserA. If no
How can we achieve the below output (PostgreSQL)
I need to calculate the Numerator and Denominator values for one metric called Payments% and the formulae for numerator is count(distinct id) where menuaction in (‘Billpayment’, Renewal’) Denominator formulae is count(distinct id) and I need the below columns in output using postgresql database. Table Date: Can someone please help on this scenario Thanks in advance! Answer A window function would
How to get percentage based on columns
so here is what I have: I have a hired column which is boolean values, population which is either US or EU values. Right now the freq divides the hired over the entire population which is like 5,000 values but I would like it to divide which are specifically US or EU values. Can anyone help me with doing this?
Quartiles in Postgres
I have payment amounts like so – How do I divide records like this into quartiles. It doesn’t have to be perfect. For example, the data in the question would transform like so – Ids 124049 and 2323 are given the same quartile because those payment values are close together. Answer you can use ntile function : db<>fiddle here
Update column of a table with new foreign key of associated table
Let’s say I have a Persons and Books table that were associated. Currently persons.usercode serves as the primary key and hence the foreign key on associated tables. I would like to change the primary key of the persons table to persons.uid. So now I want the books table to look like Dropping and adding the new primary key constraint shouldn’t
Postgresql column reference is ambiguous
I want to call my function but I get this error: ERROR: column reference “list” is ambiguous LINE 3: SET list = ARRAY_APPEND(list, input_list2), the error is on the second list inside array_append function. My function: Answer You have to use the alias name in the insert query because list has two references, one reference in EXCLUDED.list and another reference
How can I merge two SQL tables and then append the result to a third?
Say I have the following three tables: Table 1 Table 2 Table 3 What is the most efficient way using SQL to merge Tables 1 and 2 together and append the result to Table 3 in order to get the following result (but leave 1 and 2 as they are)? Table 3 after operation Answer Just insert a cross join