I’ve been stuck on this question for a while, and I was wondering if the community would be able to direct me in the right direction? I have some tag IDs that needs to be grouped, with exceptions (column: deleted) that need to be retained in the results. After which, for each grouped tag ID, I need to s…
Tag: postgresql
Recursive CTE in Postgres
I have a table with the structure: And, for each employee, I need to show the top manager ID. I mean, if for example, I have the EmployeeID 2 whose manager is 3 and, therefore, the number 3 has the manager number 5, I would have to show: I need to do this with a Recursive CTE in Postgres. Answer
How to enforce that there is only one `true` value in a column per names (in an enum) of another column?
I have the following structure with an enum { ‘ready’, ‘set’, ‘go’} on name: How can I put a constraint on it so that there will only ever be 3 true’s (one on ready, one on set, and one on go)? Answer You can use filtered unique indexes (or as Postgres calls them R…
Any way to get a more meaningful error message in postgres?
Let’s have some tea … CREATE OR REPLACE FUNCTION allRelevantTeas() RETURNS TABLE(tea_id INTEGER) AS $function$ DECLARE result REFCURSOR; stmt TEXT; countries_with_tea TEXT[] := array[‘…
Use a subquery and a custom value with IN operator
I can’t really find any answer that fits my question. I need to know how I can be able to use a subquery together with a custom value with the IN Operator in SQL. Here is an example of a query I came up with: This query is meant to show posts only from users in a users’ following table
UPDATE based on multiple overlapping conditions
I have a query which essentially transfers a job from one worker to another. The query works fine, but I need to add one additional check: ensure the current job owner (in jobs) is the worker matching @owner and @teamId. This is essentially the condition that I need to add: To the query below: The problem is …
Postgres Counts based on Month and Years
I have a psql table I want to get a break down of users points that shows their points the current month, current year, and the lifetime. I’m a bit lost on how to do this. What would the query look like? The final table should look like this Answer How about conditional aggregation?
Is it possible to process external data as some kind of virtual table?
I have the following problem I need to make a funnel, where I need to show correlation between the data I have and data from DB. I have a query of the following kind: Is it possible to process the data in where statement in such a way that I could address to it as to a table I mean
Sum of item count in an SQL query based on DATE_TRUNC
I’ve got a table which contains event status data, similar to this: ID Time Status —— ————————– —— 357920 2019-12-25 09:31:38.854764 1 …
pagination and filtering on a very large table in postgresql (keyset pagination?)
I have a scientific database with currently 4,300,000 records. It’s a scientific database, and an API is feeding it. In june 2020, I will probably have about 100,000,000 records. This is de layout of the table ‘output’: BTW, this is dummy data. But output is a table with 5 columns: ID, senso…