I have a table with a jsonb column and I want to migrate every record to a new schema. Old schema: {"foo": "bar"} New schema: {"foo": {"value": "bar"} The bar value varies by row and I want to preserve that…
I have implemented an application with REST interfaces in C# on .NET 6. When the URLs are called, then the data is fetched from a Postgresql database and returned. The application is implemented as code first. That means the tables…
I want to select elements that are not present in the database, but exist in a postgres array. To visualize: Assume that I have this data in my table: I have come close after some research, but I do not…
I have three tables: SchoolTable, SchoolOrgTable, and SchoolDetailsTable. The relationships between these tables are as follows: SchoolTable to SchoolOrgTable is a one-to-many relationship. SchoolOrgTable to SchoolDetailsTable is a many-to-one relationship. I'm currently retrieving data from these tables using a JOOQ…
I'm trying to write a query that would allow you to insert a new Room in a Building, but fail if that building ID does not exist INSERT INTO room ( building_id, room_name ) VALUES ( $1, CONCAT('Room ', (SELECT…
I have a table that looks like (never mind the database vendor as I need this in ANSI SQL): create table edge ( parent_id int not null, child_id int not null, value float not null, start date not null, end…
In a rails app, i have a 3 models, Room, User and UsersRoom which is an association table between Room and User. I want to query for a Room related to multiple users. If i do: Room.includes(:users_rooms).where(users_rooms: { user_id: user_ids }…
In a table called temporay_data with a data field called temporary_data too, filled with this JSON structure { "FormPayment": { "student": [ { "fullname": "name student1 ", "rate": 210, "meal": 7, "mealValue": 175, "finalValue": 385, "role": "student", "willPay": true },…
I am using QuestDB and one column has values double e.g. 10.5, 5.2 etc, but few rows come like -nan, inf for not a number and infinity. What is the best way to sum this column. If I do normal…
Given the table below, I'm trying to find a way to get the group matching a pre-defined subset of tokens BUT the group should not contain any tokens outside of the subset. group token 3 37 1 34 1 32…