How ON CONFLICT Works in SQLite

Posted on June 1, 2020June 1, 2020 by Ian

SQLite has the ON CONFLICT clause that allows you to specify how to handle constraint conflicts. It applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints (but not FOREIGN KEY constraints).

There are five possible options you can use with this clause:

ABORT
FAIL
IGNORE
REPLACE
ROLLBACK

This article provides examples and an explanation of each of these options.

The ON CONFLICT clause is used in CREATE TABLE statements, but it can also be used when inserting or updating data by replacing ON CONFLICT with OR.

Continue reading →

How to Skip Rows that Violate Constraints When Inserting Data in SQLite

Posted on May 31, 2020August 9, 2024 by Ian

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 reading →

4 Ways to Get Information about a Table’s Structure in SQLite

Posted on May 30, 2020May 30, 2020 by Ian

Sometimes 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:

The PRAGMA table_info() statement
The PRAGMA table_xinfo() statement (for virtual tables)
The .schema command
The sqlite_master table

Examples of each method are below.

Continue reading →

Extract the Year from a Date in PostgreSQL

Posted on May 29, 2020January 28, 2021 by Ian

In 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.

Continue reading →

3 Ways to Backup an SQLite Database

Posted on May 28, 2020May 28, 2020 by Ian

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:

Use the .backup command to back up a specified database
Use the .dump command to export the database to a .sql file
Use the .clone command to clone the database

Continue reading →

2 Ways to Insert a New Line into a String in SQLite

Posted on May 28, 2020May 28, 2020 by Ian

This 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 reading →

How SQLite Char() Works

Posted on May 28, 2020May 28, 2020 by Ian

In 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 reading →

How to Concatenate Strings in SQLite

Posted on May 28, 2020May 28, 2020 by Ian

SQLite 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.

Continue reading →

Get the Position of a Character in a String in SQLite with Instr()

Posted on May 28, 2020May 28, 2020 by Ian

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 reading →

How last_insert_rowid() Works in SQLite

Posted on May 28, 2020May 28, 2020 by Ian

SQLite has a function called last_insert_rowid() that returns the ROWID of the last row insert from the database connection which invoked the function.

Continue reading →