I am trying to figure out a way that I can search a column containing an array of integers. For example, column a -> [1,2]. Using another array, ex: b -> [1,2,3] If all the values in a are in the array b, I want to return that result. I’ve tried using unnest and = ANY but to no avail.
Tag: postgresql
Data from the table based on the received
Table Users: id, login, name, age Table views: id, date, user_id, post_id Table other: id, timeD (date), post_id What is already there: Result: date and user_id I need to add in request to display data about the user (login, name, age), the user id is optional and the timeD column Example: date timeD login na…
Using Triggers in PGAdmin4 v5 to get the geom of a line being generated in QGIS
So I have a database of pipes that is visually coded in qgis. My goal is to add a trigger to PGAdmin4 v5 that uses ST_StartPoint() and ST_EndPoint() to get the end points of the polyline (the pipe) as it is being entered. Then, I was going use this geometry to grab the nearest structure (a point) and autocomp…
Return first available row if initial SELECT finds nothing, in a single statement
I am trying to put together a SQL query that will return data based on a simple WHERE condition but will also return the first available record if the condition is not met. author_id post_id publish_date 1 31 2021-07-23 2 432 2021-06-22 2 555 2020-07-23 Using the data above my basic query would look like this…
How can I find time interval between 00:00:00 and 23:28:05 without date in PostgreSQL
So I have this table where I’ve got two times for each line, but no date and need to get the interval between those two, all is fine when it’s: 11:00:00 – 09:38:54 Returns: 01:21:06 As there’s no dates, times are stored in “time without time zone” format. The problem arises…
Can’t write SqlAlchemy query with contains operation
Let’s say that we have a two tables: Movie and Genre. Genre has an attribute genres which is a PostgreSQL ARRAY of genre ids. I have written a query to get all movie ids with genre names. But sadly I am getting: So, how can I rewrite my query to get what I want? Answer Remove the square brackets []
Unable to pull data for todays date (where statements worked on other tables)
I am trying to pull data for today’s date in the timestamp column only. The ‘where’ statement I am using worked for my other table, but this is still pulling old data. I have been trying a bunch of other methods but I think they may not be working because of the timestamp data and not just t…
How to run a string containing a static SQL query in PostgreSQL?
I have a function which generates a static SQL query and returns it as a string. What I need to do is to run the returned string as if I was typing it directly in psql. Here is a very simplified test case (the true mechanism is far more complicated but the following test case at least shows the idea)
Postgres – Select days with FAILURE status only
I have table with multiple result per days for particular project_name (SUCCESS, FAILURE, ABORTED, UNSTABLE). I would like to select only days where only FAILURE status occurs – nothing else. There are some days with multiple status (after FAILURE it can be solved and get status with different time and …
Postgresql : Query were each join result is a line
I got a database structure with three tables. On table containing some ids and two table containing the same row and a foreign key on the first table : How to get a result looking like this (a kind of left join were each result is displayed in lines instead of in additional columns in the result): Answer I th…