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 name age Note: the other table
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 autocomplete two
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 Results, ie posts by
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 when the time enters the next day and the
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 the date. Answer
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 set to SUCCESS). How can I select only days with
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 think