*Apologies as I know this question has been asked before, but tried the previously suggested approaches and none fixed my issue. I am trying to compare a table found in two databases. Unfortunately, the table does not have a primary key, so I am trying to order by the same column and compare that way. When I …
Tag: postgresql
Finding the intersection between two integer arrays in postgres
In postgres documentation I found that if I have two intarrays I can use the & operator to get the common elements between the two arrays(intersection), but this statement: raises this error: I have postgresql version 13 I am wondering why doesn’t this work, and how can I fix the query or try anothe…
Junction table indexing in many-to-many relationship
Let’s say we have a PRODUCT table, ORDER table and a junction table PRODUCT_ORDER. I create a junction table with the following script: Also an index for the PK was created automatically: It is expected that there will be practically only read operations for these tables and I would like to index the ju…
TimescaleDB – how get timestamp differences between rows?
Say you need to know how long something was ‘active’ in a time range – (with timestamp in minutes, as an example) – For a 0-15min bucket, the answer would be 6.8-3.5 + 15-9.3 = 9.0mins. (ie the first active state lasts 6.8-3.5mins, the next one goes from 9.3mins to the 15min barrier). …
How to aggregate and group by in each column?
I have this table: I’d like to summarize major and minor columns to get this result: Is there any way to achieve this? I tried: But it didn’t count each number. Answer You may achieve this using window functions. major minor major_count minor_count A a 3 2 A b 3 1 B c 2 1 B d 2 1 View
How to select a same column from a specialized table in PostgreSQL?
I have this table Person table is a general table which has these 2 specialization: I want to select data from physician and volunteer with the name of each person. Is there any possible way to do this? Let’s say I have this example data: Person: IDPerson Name P001 Andy P002 Rudy P003 Budy P004 Khal P00…
marked user with label by time for each month
Data source User ID Visit Date 1 2020-01-01 12:29:15 1 2020-01-02 12:30:11 1 2020-04-01 12:31:01 2 2020-05-01 12:31:14 Problem I need advice im trying to do sub query for this result to mark user as retention if they havent visit back for 3 month. i using this query for the data to get user’s latest vis…
How to count number if special type of value by day in SQL?
I have a table in my database which looks like this: I want to get table with number of upload value_types per day (my time column is not per day as you see). So for this example it must look like: How should this SQL query look like? I dint understand turning time into days part especially. Answer You seem
SELECT after specific row with non-sequential (uuid) primary key
With the following table: How could I retrieve n rows after a specific id, ordered by inserted_at ? Answer I want to retrieve n rows after a specific id, ordered by inserted_at. I am expecting something like this: For this, you want one additional index on users(inserted_at).
Postgres: SQL Error [42601]: ERROR: syntax error at or near “int”
I am getting the below error for the following Block- SQL Error [42601]: ERROR: syntax error at or near “int” Position: 14 BLOCK Kindly help me in pointing my mistake. Answer First of all the types like smallint, integer, bigint and int store whole numbers, that is, numbers without fractional comp…