How to_char() Works in PostgreSQL

In Postgres, to_char() is a data type formatting function that converts its first argument to a string.

The format of the string is determined by the second argument.

The to_char() function can be used to do the following conversions:

  • time stamp to string
  • interval to string
  • integer to string
  • real/double precision to string
  • numeric to string
Continue reading

How ON CONFLICT Works in SQLite

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 AUTOINCREMENT Works in SQLite

In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that’s inserted into the table.

There are a couple of ways you can create an AUTOINCREMENT column:

  • You can create it implicitly when you define the column as INTEGER PRIMARY KEY.
  • You can create it explicitly with the AUTOINCREMENT keyword. One downside of this method is that it uses extra CPU, memory, disk space, and disk I/O overhead.

Both methods cause the column to use an incrementing value each time a new row is inserted with NULL in that column.

However, there are some subtle differences between how each method works.

Continue reading