In SQLite, when you try to insert multiple rows into a table, and any of those rows violates a constraint on that table, the operation will fail.
This is to be expected, after all, that’s what the constraint is for.
But what if you just want to ignore any rows that violate constraints? In other words, if a row violates a constraint, you want SQLite to skip that row, then carry on processing the next row, and so on.
Fortunately, there’s an easy way to do this in SQLite.
Continue readingSometimes you just want to see the basic structure of a table.
In SQLite, there are several ways to retrieve information about a given table. In particular, here are four options:
PRAGMA table_info() statementPRAGMA table_xinfo() statement (for virtual tables).schema commandsqlite_master tableExamples of each method are below.
Continue readingIn PostgreSQL you can use the extract() function to get the year from a date.
You can also use the date_part() function to do the same thing.
When it comes to backing up your databases in SQLite, you have a few options as to how to go about it.
In particular, you can use one of the following methods:
.backup command to back up a specified database.dump command to export the database to a .sql file.clone command to clone the databaseThis article presents two ways to insert a new line character into a string in SQLite.
This means you can have some text on one line, more text on another line, etc, rather than it being one long line.
Continue readingIn SQLite, the char() function returns a string based on the unicode code points provided as arguments.
You can provide one or more arguments, each of which is a unicode code point. The function then returns a string based on those code points.
Continue readingSQLite doesn’t have a concat() function like many other database management systems, such as SQL Server’s concat() and MySQL’s concat().
However, that doesn’t mean you can’t concatenate two strings in SQLite.
SQLite has a concatenation operator (||) that you can use to concatenate two strings.
In SQLite, you can use the instr() function to return the position of a given character within a string.
The function only returns the position of the first occurrence of the character (if any).
If the character isn’t found, then it returns 0.
If either of the arguments are NULL, then it returns NULL.
Continue readingSQLite has a function called last_insert_rowid() that returns the ROWID of the last row insert from the database connection which invoked the function.
The SQLite length() function returns the number of characters in a string, number, or blob.
If there are any NUL characters, it returns the number of characters before the first NUL character.
Continue reading