I’m trying to build a tricky join across grains, with tableA being at a lower grain than tableB. In this example, I’m trying to accomplish the following results: tableA.id tableA.fieldA tableB.id …
How to filter and sum the array of integers in PostgreSQL
I have a task that requires to sum ALL topup_val performed by the id_user that had at least one topup_val ever by the amount of €15 exactly. This task needs to be solved in a single SELECT statement, …
Postgresql – How to insert only if specific conditions are met
I have the following table: create table foobar ( account_id bigint not null, timestamp timestamp not null, balance_one numeric not null, balance_two numeric not null, …
error : 1242 – Subquery returns more than 1 row
I am trying to calculate the total credits for each studentID in the enrollment table. I have some repeated studentIDs which means that the same student is already enrolled in many sections and each section has different credit. When I tried these queries It made all studentIDs have the same sum of credits as…
How to create a public id?
I have a database. As you can see the primary key is an auto_increment and is also unique. I read that publically sharing a row’s primary key of a table to the public is unsafe. I want to assign each row in customers a unique ID that I can publically share. How can I do this without having to specify
Inserting data into table from table-valued parameter
I am trying to create a stored procedure to which is passed a TVP and then some data from the TVP is inserted into two tables. I have already implemented the stored procedure, but only the second …
selecting from multiple tables in SQL without a join
Complete SQL novice, trying to make sense of the following TPC-H query: I was under the impression that you can only select from different tables after a join. However I don’t see any joins here. Is it implicit? Answer The comma is archaic syntax for the CROSS JOIN. It is essentially the same thing as a…
Create view with JSON array based on many to many table
I have a typical table with users. I have also a many to many table where first column is UserId and second BusinessId. I want to create a view with users where their businessId will be as json. I tried something like this: But in view I get this: Id BusinessEntityIds 1 [{“BusinessId”:1925},{̶…
How to include CHECK in JPA @Column columnDefinition?
I am trying to figure out syntax for columnDefinition in Column annotation when i want to add CHECK. So far what i have tried: Using JPA 2.2 (JavaEE 8) and openjpa as jpa provider. Could not find anything in docs: https://javaee.github.io/javaee-spec/javadocs/javax/persistence/Column.html Error i am getting: …
Is there a way of computing numerical integration on PostgreSQL?
I’ve got a table with the following columns: timestamp, name and activePower. I wanted to compute power consumption based on that and add it to a Grafana line chart. Right now, what I’m doing is a cumulative sum, like the following: Unfortunately, the intervals of timestamp are not regular, and I …