I have a database table as like table_name (participant_member) : dataset and output formate link I need combination based group by unique count for example if a member already stay in purpose p1 …
Tag: postgresql
How to add if statement in knex.select()?
I have code like this const attr = req.query.attr knex.select(knex.raw(‘foo_name as name’)) what I want is somehow select that column if it meets condition of attr and if does not then do select …
Updating a field to One-to-many relation field sum
I added a field to my existing table and now I have to update the old entries to have the right values. UPDATE shift_job SET elapsed_time = ROUND(EXTRACT(EPOCH FROM actual_end_time – …
How to combine these two queries from different tables into one to calculate percentage?
I have the following query which has the students attendance in the period: And I have the lesson count in the same period. I’d like to divide total_presences by lessons count to get the students attendance percentage. How do I do that in a single query? Answer Probably the easiest way is to use a CTE: …
Aggregation of Json objects on a relational table on Postgres
I have a relational table that contains three variable values, an ID, and the date broken down into parts. ID | Year | Month | Day | A | B | C | 1 2019 12 01 5 10 15 1 2019 …
Postgres Dump Selected Rows
I have a database that has a central object that most of the data depends on; many reference table, one to many joins and many to many joins. I need to back up all rows based on a property in the central table and be able to restore these rows. For backup purposes. I have added ON DELETE CASCADE to
The most efficient method of performing a large number of similar SELECT queries on PostgreSQL?
I have a set of large tables with many records each. I’m writing a Python program that SELECTs a large number of records from these tables, based on the value of multiple columns on those records. Essentially, these are going to be lots of queries of the form: Each table has a different set of columns, …
tricky SQL with substrings
I have a table (postgres) with a varchar field that has content structured like: The uuid can occur in more than one record. But it must not occur for more than one combination of [givenname];[surname], according to a business rule. That is, if the John Smith example above is present in the table, then if uui…
How to increase performance of COUNT SQL query in PostgreSQL?
I have a table with multiply columns. But for simplicity purpose, we can consider the following table: create table tmp_table ( entity_1_id varchar(255) not null, status integer default 1 not …
Get room members, room’s owner and admin at the same time in one query with grouped by id (unique) on PostgreSQL 12
I want to get room’s member list, room’s owner member in case of he doesn’t exists in other table and admin member at the same time. Currently i fetch them individually. CREATE TABLE public….