I currently using PostgreSQL through DBeaver In my "gia_su_pro" table, at "course_id" field, currently there is some cell contains more than one ID for the same course and separated by a paragraph separator ¶ (Pilcrow symble). I want to separate the course ID and create a new record for it with the same value as… Read More Using UPDATE keyword to update a CTE

Supposing I have something like Name username Mike BestMike99 Someone DaBest What query do I need to run in order to SELECT/return a json like json_agg {Mike: BestMike99, Someone: Dabest} >Solution : Having create table users ("Name" varchar, username varchar); It would be select array_to_json(array_agg( concat(‘{"’,"Name",’": "’,username,’"}’)::json)) from users; Working sample here

I want to populate a multi-year calendar table and calculate the value of multiple fields based on the date. I’m running this script on Postgres, it is taking forever to run even if I pass start_date as 2023-02-16 and end_date as 2023-02-17. Please help with what is wrong here. >Solution : Example using generate_series(): INSERT… Read More Populating Multi-Year Calendar Table using SQL

I’ve made a previous post over here going through date ranges in that is current and in the future. However, I’ve not specified enough to include dates that would only happen in the future. I’m making a new post since there are no current post addressing this. Considering date ranges are a "weird" data type… Read More How do you query future date ranges to look for valid DATERANGE?

Suppose the following: create table member ( id serial primary key, member_name varchar(55), role_ids bigint[] not null ); create table role ( id serial primary key, role_name varchar(55) unique ); insert into role values (1, ‘admin’), (2, ‘common’); I can create an admin member like this: insert into member (role_ids) select ARRAY[id] as role_id from… Read More How to specify native table fields and a foreign table field during an insert?

According by the task I’m doing I need to change INNER JOIN to NATURAL JOIN and get the same result as this result of inner join I got that result with this query: SELECT person_order.order_date, person.name || ‘ (‘ || ‘age:’ || person.age || ‘)’ AS person_information FROM person_order INNER JOIN person ON person_order.person_id =… Read More Use of NATURAL JOIN

Hello everyone, Here I am trying to do the multiplication of reach row multiplied by different values. For example.., I have 3 columns with customer_id(int), product_name(varchar) , price(int) Now, In the column of Product_name = LV, MK, Price column should be multiplied by 10 (Price * 10) and Product_name = YSL product Price column should… Read More MULTIPLY EACH ROW VALUES WITH DIFFERENT VALUES

Hello everyone, Here I am trying to do the multiplication of reach row multiplied by different values. For example.., I have 3 columns with customer_id(int), product_name(varchar) , price(int) Now, In the column of Product_name = LV, MK, Price column should be multiplied by 10 (Price * 10) and Product_name = YSL product Price column should… Read More MULTIPLY EACH ROW VALUES WITH DIFFERENT VALUES

I am using DBeaver to query a PostgreSQL database. I have this query, it simply selects the highest id per Enterprise_Nbr. The query works but is really slow. Is there any way I can rewrite the query to improve performance. I am using the querytool DBeaver because I don’t have direct access to PostgreSQL. The… Read More sql slow postgresql dbeaver

There exists a table as below with a letter and a corresponding value. practice=# select * from table; letter | value ——–+——— A | 5000.00 B | 6000.00 C | 6000.00 C | 7000.00 B | 8000.00 A | 9000.00 (6 rows) I wish to obtain the sum of each letter through use of a… Read More Calculating percentages across groups using PostgreSQL