I have a table that has a timestamp column something like: table Elements I would like to put a unique constraint on the table that will make sure that there are NO two entries with the same name and type and are within X months of any other row. I have asynchronous processes that push rows into this table, a…
Tag: postgresql
Is a SQL trigger faster than a if statement on a interface [closed]
Closed. This question is opinion-based. It is not currently accepting answers. Want to improve this question? Update the question so it can be answered with facts and citations by editing this post. Closed last year. Improve this question In a big data scenario, where a piece of code has to run tens of thousa…
SQL – Get the MAX COUNT access peak having only entrance and exit date
I have a table that keeps the access to our system, and it have two columns called entranceTime and exitTime Eg: What im trying to get is the “peak” of simultaneous connections, i tried using a subquery with MAX and COUNT like this: But with this query im getting the count of 5 instad of 3, is it …
PostgreSql format jsonb array
Can not find a proper query to format jsonb output in postgres I have a jonb column in a table. How do I select the posts column to have just certain fields? Something like: Any ideas? PS: The version of postgres is the latest 12, 13, 14 … Answer You may considering using json_to_record_set to extract y…
Select based on group membership
Suppose I have two tables t and o. I want to select all the rows of t in which t.feature = o.feature (ergo first two rows). In addition, I also want to select all the rows whose t.grouping = t.grouping of the previously selected rows. In this case t.grouping 1, 2 ergo rows (‘E’, 1), (‘F̵…
Selecting and comparing columns which contain constants
Follow up to this question. Say that on postgres, I have a table TABLE1 containing the columns id (integer), name (string): And attempt to run the following queries: On sqlfiddle, the former yields a result, whilst the latter fails with the message: On postgres 13.3 which I have installed locally, however, ne…
JSON array to a single row in SQL table
I have a table in PostgreSQL database. With some data in it: receipt column contains lists of 3-element lists. What I’m trying to achieve is write an SQL query that returns table in the form The best I’ve got so far is but I’m stuck on unnesting the inner arrays. I tried using jsonb_to_recor…
Equivalent of jsonb_path_query_array in Postgresql-11
Given a column containing the above data, I use select jsonb_path_query_array(column, ‘$.key’) to get the output [KA, KB] However this doesn’t work in Postgres-11. Are there any alternatives for the same ? Answer Yes. This yields a Postgres array. Use jsonb_agg instead of array_agg if you ne…
SQL SELECT transactions for items that two people have both purchased at least once
I have a table of transactions where a each transaction has a transaction_id, an item (what item was purchased in the transaction) and a customer_id (person who ordered the item). How can I pull all transactions only for items that have been purchased by both customer_id 100 and customer_id 101 at least once?…
Hasura GraphQL query : where clause with value from related entity
I recently started using GraphQL through a Hasura layer on top of a PostgreSQL DB. I am having troubles implementing a basic query. Here are my entities : article : articleId content publishedDate categoryId category : categoryId name createdDate What I am trying to achieve, in English, is the following : get…