The request below: returns a list like this: How to get the ids for which id must have at least a and b, and more generally the content of a given array ? From the example above, I would get: Answer For two values, you can use windowing boolean aggregation: A more generic approach uses array aggregation:
Tag: postgresql
group by date and two different times in the same date in Sql
i want to select from my database the data that is BETWEEN to date and two different times in the same date but when I use the Sum in one of the variants it give me a wrong result i thnik that the …
query to insert records to another table by calculating the percentage on a column
I have 2 Postgres tables with the following structure: Lets say I have data in the tmp table as below: I have a script below that inserts the data from public.tmp to public.mo table based on the criteria that if the column STATUS has mix values (COMPLIANT and NC) for a particular HOSTNAME then compliant_status is PARTIAL and if all
How can I make a query that return only the exact list of parameters
I have a table that has the ingredient information and I would like to return only if the value is exactly as the parameter I receive. The Table structure is: Name | Recipe Ingredient X | …
How to copy CSV into table with missing column?
I want to import data from a csv file into redshift. CSV format: Here is my import command: The problem I have is that sometimes, my file has only col1, col2 and col3. Is it possible to execute the COPY and add null for the missing values? Answer Yes, but must be explicit about it: The missing col2 will be
For each ID get the first record where Product is not null (Postgresql)
I have customer data with information when the appointment took place, what type of appointment it was and wheather a Product was selected. I am trying to get the earliest appointment for every id where Product is not null. Basically reduce the table where id only appears once with appointment date and type where product is not missing. This doesn’t
Find columns that have all values in another table
I’m having trouble with the query below. The aim is to select the clientID’s that have a loan in all of the branches in the city ‘Sherbrooke’. WITH sherb AS (SELECT branch_id FROM …
Getting data from three tables in postgresql
I’m new to postgresql. I have three tables Table1: teacher Table2: school_location Table3: teachers_school_location I want to write a query to get data from these three tables like this: (output in json by calling an API) When user select tid = 1, I want output like this When user select tid = 2, I want output like this I used
sql query for stratified sampling with dynamic sample size
Let say we have a table in this format: From this example, we see two stratas s1 and s2. What I want to do is stratified sampling and the sample size is the last column. For example, I want to randomly sample 2 instances from s1 and 1 random sample from s2. Any help is appreciated. Please keep in mind
How to define minimum and maximum value as range for a postgresql field?
I would like to compress the values that a field can take in my PostgreSQL database, but could not find a way to do it in a query while creating the table. For instance, let’s say I have a table which is named Books, and it contains some essential information about my books. The fields might be like: bookId ownerId