I want to join two table, with first table contain an array which is primary keys of second table in JSON format Answer demo:db<>fiddle You can use the ANY function: Edit: demo:db<>fiddle If you have jsonb arrays, you can use the @> operator. Note, that this works only if your integer id values…
Tag: postgresql
How to select TOP records in Postgres. Not a similar question
I have a table with three columns (City, Orderid, Total_quantity) and more than 500 rows. I want to fetch the top 50 records according to total_quantity wise. I can do this with rank_number but the …
How create Postgres database using SQLAlchemy
I want write project using database library (SQLAlchemy). When I use SQLite, all works good, but when I deploy project on server (Heroku with Postgres plugin), It doesn’t work. This is code for my database: I have engine & Base variable: In DATABASE_URL link to base on Heroku(Postgres). For create d…
Select all rows which match a condition dependent on over rows
Let’s assume I have a table T with the columns X, Y, Z where Z is some comparable value (I have to make it work on a PostgreSQL database but a solution that additionally supports oracle would be even nicer). What I want to achieve is best described by the following, sadly invalid query: This is invalid …
How can I assign pre-determined codes (1,2,3, etc,) to a JSON-type column in PostgreSQL?
I’m extracting a table of 2000+ rows which are park details. One of the columns is JSON type. Image of the table We have about 15 attributes like this and we also have a documentation of pre-determined codes assigned to each attribute. Each row in the extracted table has a different set of attributes th…
updating postgres table with unique Integer array
I am not so good in queries. I have a table with column users which is of type ‘users integer ARRAY’. For e.g. it looks like users[1,2] Now I want to update this array with new value, but it has to be distinct. For e.g. if I want to add 3 to it then the output should be users[1,2,3] and
How can I calculate an “active users” aggregation from an activity log in SQL?
In PostgreSQL, I have a table that logs activity for all users, with an account ID and a timestamp field: A single account_id can appear many times in a day, or not at all. I would like a chart showing the number of “active users” each day, where “active users” means “users who h…
Regexp search SQL query fields
I have a repository of SQL queries and I want to understand which queries use certain tables or fields. Let’s say I want to understand what queries use the email field, how can I write it? Example SQL …
PostgreSQL array overlap with the ALL construct
I want to achieve the same behavior as in next example: select array[1, 3] && array[1] and array[1, 3] && array[2] using the ALL construct like so: select array[1, 3] && all(…
How to check if a customer has made a warranty claim and a product sale?
I would like to know how to check in PostgreSQL to see if whether or not a customer has made both a warranty claim and a product sale. For example , I have a customer like this. CustomerID OrderID Product Name ABC123 1426457 PRODUCT A ABC123 1426458 WARRANTY CLAIM So for this customer. I want to set a column …