MYSQL Min Max Historic Values
I have a MYSQL table with this format: Insurance Date Value A 2020-01-01 2 A 2020-02-01 5 A 2020-03-01 4 A 2020-04-01 6 A 2020-05-01 8 A 2020-06-01 1 A 2020-07-01 3 A 2020-08-01 7 A 2020-09-01 9 A 2020-10-01…
Postgresql – Sql regexp that accepts chinese character,ascii & rejects special characters
I need a sql regexp pattern that satisfied the following criteria. 1.Accepts chinese characters, 2.Accepts - a-z,A-Z,0-9,spaces 3.Rejects only special characters. I've tried the following. Select regexp_like((val)::TEXT , ('^[ !-’~¡-ÿ]*$')::TEXT) Or regexp_like((val)::TEXT ,('^[^[:ascii:]]+$')::text); The above query also accepts special characters…
Postgresql – How to check if a value is made of spaces in SQL?
I'm trying to make a query in my PostgreSQL database that checks whether an optional string value exists or not. How do I do that, taking into account my database always fill out the full size of the column with…
MySQL Retrieving values with intermediary table
I have the following tables: MONKEY : id name 1 John 2 Mary CHECK : id monkey_id checkdate 1 1 2023-06-02 03:00:00 2 2 2023-08-24 14:30:00 3 1 2023-06-04 14:35:00 MEASUREMENTS : id check_id length width weight 1 1 23.34…
Postgresql – How to link the pairs of values from 2 columns
I have a table [tt] with 2 columns which has a set of values that can repeat. Columns are named [a] and [b]: [a] [b] 1 2 1 3 1 4 2 1 2 3 3 2 4 1 4…
Mysql – Group by multiple columns and add them
I have a table something like below level1 level2 level3 key A B C k1 A B C K2 A B D k2 A B E k3 A F G k4 A F null k5 A null null k6 A…
Postgresql – Why is SELECT…FOR UPDATE not working if executed inside a CTE that is not used later in the transaction?
I have a simple table that is as seen below: Table "public.test" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | name | text | | | Indexes: "ss_pkey" PRIMARY KEY,…
Postgresql – Batch Insert: Using Single vs. Multiple Statements
In a single transaction, is there any significant performance difference between doing batch insert with a single statement like: -- BEGIN TRANSACTION INSERT INTO films (code, title, did, date_prod, kind) VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), ('HG120', 'The Dinner Game',…
Postgresql – calculate field has_impact based on start and end time
Trying to populate field has_impact based on the following for each feed view in the session. has_impact is true when difference between (view_end_time - view_start_time) > 3 and view_perc > 0.8 else false. view_logs table create table view_logs(session_id varchar(10), post_id…
Postgresql – Massive delete based on subquery on WHERE statement. Postgres
I need made a subquery with IN statement. Like this (simplify): DELETE FROM table_1 WHERE id_table_1 IN (SELECT string_agg(fk_id_table_1::TEXT,',') FROM table_2 I need made a massive delete, based on second subquery. The problem is if i use IN statement i…