How transaction_timestamp() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, the transaction_timestamp() function returns the current date and time (including the time zone offset), at the start of the current transaction.

It’s the equivalent of the traditional Postgres function now().

It’s also similar to the current_timestamp function (when called without an argument), except that it’s named to clearly reflect what it does.

The transaction_timestamp() function doesn’t accept any parameters, so you can’t specify its precision, whereas current_timestamp can be called with or without a precision parameter.

Also, transaction_timestamp() is a non-SQL-standard function.

Continue reading →

How Now() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, the now() function returns the current date and time (including the time zone offset), at the start of the current transaction.

It’s the equivalent of the transaction_timestamp() function.

It’s also similar to the current_timestamp function (when called without an argument).

The now() function doesn’t accept any parameters, so you can’t specify its precision, whereas current_timestamp can be called with or without a precision parameter.

Also, now() is non-SQL-standard (as is the transaction_timestamp() function).

Continue reading →

How timeofday() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, timeofday() is a non-SQL-standard time function that returns the current date and time, with the time zone abbreviation.

It’s similar to the clock_timestamp() function, except that it returns its result as a formatted text string rather than a timestamp with time zone value. 

The result of both functions changes throughout the execution of a statement. Therefore, you could get a different result in different parts of the statement if you call the functions multiple times within a single statement.

Continue reading →

How the to_number() Function Works in PostgreSQL

Posted on by Ian

In PostgreSQL, you can use the to_number() function to convert a string to a numeric value.

More specifically, it converts the string representation of a number to a numeric value.

For example, if you have $1,234.50 as a string, you can use to_number() to convert that to an actual number that uses the numeric data type.

Continue reading →

Dealing with Primary Key Conflicts when Inserting Data in SQLite

Posted on by Ian

SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.

In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.

This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.

By “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. By default, when you try to do this, the operation will be aborted and SQLite will return an error.

But you can use the ON CONFLICT clause to change the way SQLite deals with these situations.

One option is to use this clause in the CREATE TABLE statement when creating the table. Doing that will determine how all INSERT operations are treated.

Another option is to use the clause on the INSERT statement whenever you try to insert data into the table. This allows you to take advantage of the clause even when the table wasn’t created with it. When you use this option, the syntax is different; you use OR instead of ON CONFLICT.

The examples on this page use the second option – I create the table without the ON CONFLICT clause, and I instead specify OR on the INSERT statement.

Continue reading →