Postgresql – Postgres updating fields with same value
I have a table as below name item inum na1 k1 5 na2 k1 6 na3 k2 7 na4 k3 7 na5 k3 8 If 2 item has the same value, then their inum needs to updated with the highest…
What is the best way to query data from Table if a Table is dependent to itself in Postgresql
I need to fetch data like the below: select u.id from public.user u where u.reporting_id in (select u.id from public.user u where u.reporting_id = ?) is there any best way of doing same in PostgreSQL
Postgresql – psql: How can I get a single json column in aggregation after a group by query
I am trying to get a single row for each group by clause in the query. Each SELECT output has a json in it. I am trying this: SELECT MIN(C.json_column->'key') AS Key, A.field1, B.field2 FROM json_table AS C LEFT JOIN…
Postgresql – Delete query to find records based on complex conditions not working
I have an postgres table in the below structure I want to write a query which will Delete all the records from the table which are older than 90 days and is in COMPLETE status. if any row has a…
Postgresql – Stop executing query when a condition evaluates to false (or true)
I have a table that stores basic information about files: CREATE TABLE filez ( id INTEGER NOT NULL, name TEXT NOT NULL, size BIGINT NOT NULL ); CREATE UNIQUE INDEX filez__id__idx ON filez USING (id); I need to select a…
Postgresql – FOR loop inside json_build_array() function does not work
I am currently working on a function in Postgres. I have the function working, but I need to add a loop inside the json_build_array() function to add multiple of what is inside the array. This is what I currently have:…
Mysql – If I have two queries with the same field but different sort field then, should I create a single compound index or multiple compound index
If I have two queries with the same field but different sort fields then, do I need to create a single compound index or multiple compound index with different sort fields? I am working on an e-commerce site, where I…
Postgresql – Postgres Check if One to many relationship has all values in another relationship
I have a table which contains all the Reports report_id | Name ---------------+------------- 1 | Sales Q3 2 | Sales Q2 I have a relation table which contains one to many relations about the report with dept report_id | dept_id…
Postgresql – How do i use Quotes inside of Quotes in C# and not let it error
Im trying to query some data from a postgres server using C#. This is the code im currently working with: namespace WpfApp1 { class postgresDB { public void Init_database() { NpgsqlConnection conn = new NpgsqlConnection("Server=FS01;User Id=postgres;" + "Password=pwd;Database=postgres;"); conn.Open(); Debug.WriteLine("DB…
Postgresql – Need to update row at Postgres table with join by array
my table schema looks this way: create table table_with_arrays ( dim_col_code_array integer[] -- contains foreign keys to dict_table.array_code dim_col_val_array varchar[] -- needs to be populated with dict_table.array_value ); create table dict_table( array_code integer, array_value varchar ) there is the field…