Here are two ways to convert a given Julian day to its date value in PostgreSQL.
The first method directly inputs the Julian day into the date data type.
The second method uses the to_date() function to return the same result.
Julian day is the number of days since Monday, January 1, 4713 BC using the proleptic Julian calendar, which is November 24, 4714 BC, in the proleptic Gregorian calendar.
Continue readingSQLite 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.
In PostgreSQL, you can convert a given date to its Julian day representation by using the to_char() function.
To do this, pass the date as the first argument, and 'J' as the second.
When working with PostgreSQL, you may occasionally find yourself in the situation where you need a list of timezones that Postgres recognises.
For example, you could be trying to set the timezone for your current session, or you could be using one of the datetime functions that allow you to manipulate the timezone.
Below are two views and two functions that return a list of timezones.
Continue readingIn Postgres, you can use the to_char() function to return the century, based on a given date.
To do this, use CC as the second argument. This returns the two-digit century based on the date provided.
In PostgreSQL, you can use the to_char() function to output numbers in a given format. This includes appending the number with the ordinal indicator.
For example 1, 2, 3 becomes 1st, 2nd, 3rd.
Continue readingThe PostgreSQL to_char() function provides us with the option of adding the ordinal number suffix to the result.
This means we can add the ordinal indicator when formatting dates.
For example, instead of outputting 10 Feb we could output 10th Feb.
Continue readingThe easiest way to install PostgreSQL on a Mac is by installing Postgres.app. Postgres.app is a full-featured PostgreSQL installation packaged as a standard Mac application.
You simply download it and drag it to your Applications folder, just like with any other application.
Continue readingWhen working with Postgres, you can use the to_char() function to output numbers in a given format.
The way it works is that you provide two arguments. The first argument is the number to be formatted. The second argument determines how it is formatted.
The return value is text.
Depending on your requirements, you might prefer to convert the number to the appropriate data type, for example using the cast() function.
Examples of both methods are below.
Continue readingIn PostgreSQL, you can use the to_char() to output a number in a certain format, including with a plus or minus sign to indicate whether it’s a positive or negative number.
To do this, use one of S, MI, PL, or SG in your format string.
Another option is to use PR in order to enclose any negative values in angle brackets.