In a simple table in PostgreSQL CREATE TABLE "SOMESCHEMA".newtable ( col1 varchar NULL ); I inserted three rows with the values "A 1" "A~2" "A 3" Whe I make a query like SELECT col1 FROM "SOMESCHEMA".newtable order by col1 desc;…
This query below takes way too long to execute select s.id, s.description, count(distinct et.id) from "district s" s inner join ( select id.id from "industry_districts" id ) as id2 on id2.id = s.id inner join ( select id, industry_districts_id from…
I want get characters between [] from a string example: "hello" and "world" from "gegerferf[hello] frfer [world] frfre rfrf" into a table i am try with substring and for loop but i dont now. select regexp_replace(string, '...','', 'g') from mytable
First, I was having pgAdmin fatal error, the application could not be contacted. so I uninstalled pgAdmin and reinstalled it. Now, when I write queries, PostgreSQL says "Connection to pgAdmin server has been lost". Then, I logged out and into…
I'm looking for someone to confirm that I'm understanding this correctly: when I add intervals together in postgres it looks like the time elements get added and converted - so PT55M + PT10M => PT1H5M However, PT23H + PT2H =>…
The question may seems stupid but I am stuck on executing that kind of query: UPDATE user SET productArr = ARRAY[ARRAY[2,2],ARRAY[3,2],ARRAY[1,3]] WHERE id = 1; within a @Query Spring Data. I defined my repository method like: @Modifying @Query(value = """…
I am trying to connect 3 relations - Photo, PhotoComment and PhotoRating. I want to let my users to have a possibility to rate a photo. These photos can have multiple comments by many users and multiple rates by many…
in postgres I will be doing 2 kinds of lookups. first one is by "userId" and second is by "userId" and "abortScript" together. should i create 2 separate indexes for these columns like this CREATE INDEX "Category_userId_idx" ON "Category"("userId"); CREATE…
Here is an example plainto_tsquery call in postgres 15.1: # select plainto_tsquery('english', 'postgres is the best'); plainto_tsquery ------------------- 'postgr' & 'best' (1 row) This is the equivalent of select to_tsquery('english', 'postgr & best'); to_tsquery ------------------- 'postgr' & 'best' The docs…
I have two tables. One with a parent child hierarchy and one with the details of some leaves somewhere in the paths. create table category ( category_id integer, category_name text, parent_category integer, level integer); insert into category values (1, 'cat1',…