I am trying to auto increment a table with a single column which is a primary key, eg: CREATE TABLE my_table (id PRIMARY KEY NOT NULL); I would usually just not include the primary key column in the insert statment…
I have a table in my database with many (10+) int4 columns, and I am also developing an application server which updates these values. My current approach requires multiple round trips, because I do something like this, each as a…
I have a RESTful app. Here's a test method from it. It is intended to retrieve a page of QuestionCommentResponseDto objects in descending order @Test @Sql(executionPhase = BEFORE_TEST_METHOD, value = BASE_SCRIPT_PATH + "GetPageTest/before.sql") @Sql(executionPhase = AFTER_TEST_METHOD, value = BASE_SCRIPT_PATH +…
id_start id_end 1 2 2 3 3 4 I want to collect elements who are "connected" like [1, 2, 3, 4]. (in an array for example) I tried a recursive query like: WITH RECURSIVE q_rec AS ( SELECT id_start, id_end…
I have a query for deployment table. There is no data for hotfix column now. I want to show all change count without hotfix and with hotfix for time intervals. Table data: deployTime changeno hotfix 2022-08 aaa 2022-08 bbb 2022-11…
I'm trying desing and implement database model in python from a json file but my python code seems like is not inserting data into table and i'm not sure where is the problem in my code import json import psycopg2…
Get the average traffic from last week data per WEEK number and get the traffic data for last week Traffic(D-7) For example if date = 5/13/2023, need to output traffic data (Traffic(D-7)) for date = 5/6/2023 I manage to get…
total nube in PL/PGSQL. I would like to define an array of strings and then used that in my SELECT... WHERE In statement but can't seem to get it to work, help appreciated. DO $$ DECLARE testArray varchar[] := array['john','lisa'];…
I am using ST_Intersects to check if two polygons intersect. Relevant part of my query is: SELECT entity_number FROM coordinates WHERE ST_INTERSECTS($1, location) It works well to determine if one polygon scrosses the other's surface: I expected ST_Intersects to return…
Ex. SELECT * FROM A JOIN B ON A.idx = B.idx JOIN C ON A.idx = C.idx WHERE A.create_dt BETWEEN '2023-05-01' AND '2023-05-31' OR A.last_dt BETWEEN '2023-05-01' AND '2023-05-31' OR B.create_dt BETWEEN '2023-05-01' AND '2023-05-31' OR B.last_dt BETWEEN '2023-05-01' AND…