SQLite constraint violation – How to insert data to this schema?

First of all, sorry for the question but I was hours searching for a solution and the only things that I got are Java exceptions. I’m developing a little web crawler for movies, so I created this two tables.

CREATE TABLE IF NOT EXISTS movies(     movie_id INTEGER PRIMARY KEY,     title VARCHAR(100) NOT NULL UNIQUE,     info VARCHAR(250) );  CREATE TABLE IF NOT EXISTS genres(     genre_id INTEGER PRIMARY KEY,     title VARCHAR(50) NOT NULL UNIQUE, ); 

Well, as you can see is a many to many relationship, so I made a junction table like this:

CREATE TABLE IF NOT EXISTS movies_genres(     movie_id INTEGER NOT NULL,     genre_id INTEGER NOT NULL,     PRIMARY KEY(movie_id, genre_id),     FOREIGN KEY (movie_id) REFERENCES movies(movie_id),     FOREIGN KEY (genre_id) REFERENCES genres(genre_id) ); 

And I’m trying to insert data with (arg are the method arguments):

INSERT INTO movies_genres(movie_id, genre_id)  SELECT m.movie_id, g.genre_id FROM SELECT (movie_id FROM movies WHERE title = arg) AS m CROSS JOIN SELECT (genre_id FROM genres WHERE title = arg) AS g; 

But I get this SQLITE_CONSTRAINT] Abort due to constraint violation (UNIQUE constraint failed. I don’t know where is the error, maybe is in the schema but I can’t see the error. Any idea on how fix it?.

How can I check if BULK INSERT is running on a table?

A BULK INSERT command may take a long time to run.

I have a program that receives multiple files and pushes them to the DB using the BULK INSERT command to a specific table. After a file is uploaded, the program deletes it and moves to the next.

The BULK INSERT command may continue to run on the DB after the program stopped. so a file may be loaded to the DB, or still in process when my program starts.

So, my questions are:

  1. Can I tell if a BULK INSERT command is still running for a specific table?
  2. Can I tell if a file has been already loaded to the DB?

Generate all combinations for variables and insert into temp table

0

I have one requirement there are 4 variables and i want all combinations of 4 variables and insert into temp table .

DECLARE StartDateTime DATETIME; DECLARE Age INT;  DECLARE Duration INT ; DECLARE TotalDD INT;  CREATE TEMPORARY TABLE tempTable(     Duration INT,     TotalDD INT,     Age INT,     StartDateTime DATETIME,     ); SET Age = 16; SET TotalDD = 14; SET Duration = 30; SET StartDateTime = CURDATE(); 

Excepted Result:

Duration age TotalDD StartDateTime 30 null null null null 16 null null 30 null null null 30 16 null null null null 14 20200622 30 null 14 null 30 16 null 20200622 

……….. ………. so on

postgresql: filter with subquery and then insert

having the following query, my plan was to filter with the subquery first and then to INSERT:

INSERT INTO cals_new (listing_id,date,available,price,timestamp)                                          (SELECT listing_id,date,available,price,timestamp                                           FROM   cals c                                           WHERE  NOT EXISTS ( SELECT                                                                FROM   update                                                                WHERE  id = c.id                                                                )); 

But EXPLAIN gives the following:

QUERY PLAN

Insert on cals_new (cost=513077.42..5338467.65 rows=109800833 width=44) -> Merge Anti Join (cost=513077.42..5338467.65 rows=109800833 width=44) Merge Cond: (c.id = update.id) -> Index Scan using cals_pkey on cals c (cost=0.57..3958576.01 rows=113119496 width=44) -> Sort (cost=513076.85..521373.51 rows=3318663 width=8) Sort Key: update.id -> Seq Scan on update (cost=0.00..62881.63 rows=3318663 width=8) 

If I understend the EXPLAIN output correctly, it does the expansive INSERT first, and only filters after.

How to optimise the query so that it works as expected? More specifically, filters with the subquery first and then does the INSERT?

Thank you!

Insert a row into the table omitting all values?

Suppose I have a table with a serial as primary key and couple of other columns, that are all nullable. Is there any way to insert a new row in that table by omitting all the columns in the insert clause? Since serial is auto-incremental and can be omitted right? The rest of columns can be null and can therefore be omitted too? So theoretically it should be possible somehow. But is it?

Insert a node in a binary search tree

The teacher gave us this code that inserts a node in a binary search tree, but I am not sure what the $ z.p = y$ instruction does. wouldn’t the algorithm work without it?

abr_insert(T, z) //z is the node that must be inserted     {             y = NULL;             x = T.root;             while(x != NULL)             {                     y  = x;                     if(z.key < x.key)                             x = x.left;                     else                             x = x.right;             }             z.p = y; //here is the instruction that I don't understand             if(y = NULL)                 T.root = z;             else if(z.key < y,key)                     y.left = z;             else                     y.right = z;     } 

MySQL Date insert

I have a date for example in the following format in column A

03/03/2020 01:48:18 pm (Currently Month, Day, Year and 12 hour format for time)

I would like to read the date in Column A and insert into Column B into the following format

2020/03/03/ 13:48:18( Need format Year, Month, Day and 24 hour format)

Would like a query to search for all records in Column A and insert into Column B , row for row

Confused in how to insert a slack variable in a constrain inequality

According to my understanding , we should put a slack variable to equate an inequality constrain by inserting the slack varaible in the side that is less than the other side , for example if we have 4x+2<2 this will be 4x+2+slack_variable=2 .But here in Wikipedia :
https://en.wikipedia.org/wiki/Slack_variable In the example section , it says the following “By introducing the slack variable y>=0, the inequality Ax<=b can be converted to the equation y-Ax+b = 0” Which means that the slack variable is inserted in the bigger side ! Please some one explain this confusion.

Translation anchor insert broken (#file_links)

Hello @sven, sorry to bother you again.

It seems when using the #file_links to pull in lines of content, then using translate around the #file_links, SER does not insert the anchor properly and instead breaks the link, it ends up being a giant block of text.

Note: I have tested this without using #file_links (by grabbing a random article and surrounding it with the translate macro) and it works properly.

Here is the code I use to create my article templates:

#trans_en_sk #file_links[example.dat,1,S] #file_links[example.dat,1,S] #file_links[example.dat,1,S] #notrans

The test site/anchor I used in this example (note: only testing in preview mode):

https://www.google.com/#Google

Output of the bug (blurred for text privacy):

You can see the entire bottom part becomes a link. I have now tested this many times, and every time the result is the same. A giant block of text as the link, and the actual anchor not linked at all.

Please let me know if this is preview-bug only, because I’m hesitant to test it on a live site in this situation.