*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 sort the above
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 another efficient alternative. Answer The documentation you
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 junction table intelligently. Usually, I additionally create
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 would you do something like this
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 P005 Apho Phycisian: IDPhysician
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 visit each month includes null
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 components, of various ranges. Attempts to store values outside of