New answers tagged insert
2
https://dev.mysql.com/doc/refman/8.0/en/with.html says:
A WITH clause is permitted in these contexts:
At the beginning of SELECT, UPDATE, and DELETE statements.
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
I.e. not before an INSERT.
However, you can use INSERT INTO <table> WITH <cte> SELECT ...
Re your comments:
INSERT ...
0
Use the INSERT ... ON CONFLICT with the WHERE condition as you showed it.
Then check how many rows were affected, and if there was none, throw an error. You can do that either in client code or in a database function.
-2
In these kind of situations I use Python with Pandas DataFrame.to_sql() and SQLAlchemy Engine. I have not compared its speed with mysqlimport. For my purposes it is fast enough (400'000 rows, 3 columns, only integers).
import pandas
from sqlalchemy import create_engine
url = 'mysql+mysqlconnector://<user>:<password>@<host:port>'
engine = ...
2
Don't use integers to represent timestamps or dates, it is wrong. For the following, I will assume that column date is a timestamp.
The solution is to create a unique index like this:
CREATE UNIQUE INDEX ON tt_series (userid, CAST (date AS date));
Then any attempt to insert two rows on the same date will fail.
1
Assuming no concurrent writes on the involved tables, this simpler function should cover everything you describe:
CREATE OR REPLACE FUNCTION public.new_conversation(INOUT _conv_id text, _members jsonb, _sent_message text)
LANGUAGE plpgsql AS
$func$
BEGIN
IF NOT EXISTS (SELECT FROM conversations WHERE conversation_id = _conv_id) THEN
INSERT INTO ...
2
A workaround for insert-exec it to create a temp table in the caller and have the callee populate it if it exists. If it doesn't exist the callee can still populate its own local copy and return as a select to serve vintage callers and for easier debugging. This also works for dynamic sql!
Callee USP:
declare
@returnAsSelect bit = 0;
if (...
Top 50 recent answers are included