On the outside, they seem harmless. In fact, they even appear familiar and well-known to you. However, this illusion lasts only until you check them. This is when their treacherous nature becomes apparent, acting completely contrary to your expectations. Sometimes they pull stunts that make your hair stand on end — for instance, losing the confidential data entrusted to them. When confronted directly, they claim not to know each other, yet they diligently work under the same cap in the shadows. It's about time we exposed them. Let’s take a closer look at these suspicious types.
Data typing in PostgreSQL, despite its logic, often brings about very strange surprises. In this article, we'll aim to clarify some of its quirks, explore the reasons behind their odd behavior, and understand how to avoid pitfalls in everyday practice. To be honest, I created this article partly as a reference for myself, a guide to which I could easily refer in contentious cases. Therefore, it will be updated as new surprises from suspicious types are discovered. So, let's set off, tireless database explorers!
Dossier Number One. real/double precision/numeric/money
It would seem that numeric types are the least problematic in terms of behavioral surprises. But that's not the case. So let's start with them.
Lost the ability to count
SELECT 0.1::real = 0.1
?column?
boolean
---------
fWhat's the issue? It's that PostgreSQL converts the untyped constant 0.1 to the double precision type and tries to compare it with the 0.1 of the real type. And these are completely different values! The crux of the matter lies in the representation of floating-point numbers in machine memory. Since 0.1 cannot be represented as a finite binary fraction (it becomes 0.0(0011) in binary), numbers of different precisions will differ, leading to the result that they are not equal. In fact, this topic deserves a separate article; I won't elaborate here.
Where's the error?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Error **********
ERROR: syntax error at or near "("
SQL state: 42601
Character: 24Many people know that PostgreSQL allows functional type casting. This means you can write not only 1::int but also int(1), which is equivalent. However, this does not apply to types whose names consist of multiple words! Therefore, if you want to cast a numeric value to the double precision type functionally, use its alias float8, that is, SELECT float8(1).
What is greater than infinity?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tWell, it turns out that there is something greater than infinity, and that is NaN! At the same time, the PostgreSQL documentation looks us in the eye and claims that NaN is inherently greater than any other number and, consequently, infinity. The reverse is also true for -NaN. Hello, lovers of mathematical analysis! But remember that all this operates in the context of real numbers.
Rounding eyes
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Another unexpected greeting from the database. And again, it should be noted that double precision and numeric types have different rounding behaviors. For numeric, the normal rounding is applied, where 0.5 rounds up, while for double precision, the rounding of 0.5 goes towards the nearest even integer.
Money is something special
SELECT '10'::money::float8
ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
^
********** Error **********
ERROR: cannot cast type money to double precision
SQL state: 42846
Character: 19According to PostgreSQL, money is not a real number. Some individuals think so too. However, we need to remember that casting the money type is only possible to the numeric type, just as the money type can only be cast from the numeric type. But then you can play with it as you wish. But that's no longer true money.
Smallint and sequence generation
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERROR: function generate_series(smallint, smallint, smallint) is not unique
LINE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
********** Error **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Character: 18PostgreSQL doesn't do things halfway. What sequences are based on smallint? At least int! Therefore, when attempting to execute the aforementioned query, the database tries to cast smallint to some other integer type, and sees that there can be several such casts. Which cast should it choose? It cannot decide, and thus fails with an error.
Dossier number two. "char"/char/varchar/text
There are also some oddities with character types. Let's get acquainted with them.
What kind of tricks are these?
SELECT 'PETYA'::"char"
, 'PETYA'::"char"::bytea
, 'PETYA'::char
, 'PETYA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fWhat is this type "char", what kind of clown is this? We don't need that... Because it pretends to be a regular char, even though it's in quotes. And it differs from a regular char (without quotes) in that it only outputs the first byte of the string representation, while a normal char outputs the first character. In our case, the first character is the letter П, which in Unicode representation occupies 2 bytes, as evidenced by the conversion of the result to the bytea type. The type "char" only takes the first byte of this Unicode representation. So why is this type needed? The PostgreSQL documentation states that this is a special type used for specific needs. So it is unlikely we will need it. But look it in the eye and don't mistake it when you encounter its peculiar behavior.
Extra spaces. Out of sight, out of mind.
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Take a look at the example provided. I specifically converted all results to the bytea type to clearly show what is there. Where are the trailing spaces after the conversion to varchar(6)? The documentation succinctly states: "When converting a character value to another character type, trailing spaces are discarded." This aversion needs to be remembered. And note that if a string constant in quotes is immediately cast to varchar(6), the trailing spaces are preserved. Such wonders.
Dossier number three. json/jsonb
JSON is a separate structure that has its own life. Therefore, its entities and those of PostgreSQL are somewhat different. Here are some examples.
Johnson & Johnson. Feel the difference
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fThe thing is, JSON has its own null entity, which is not equivalent to NULL in PostgreSQL. At the same time, the JSON object itself can very well have a value of NULL, so the expression SELECT null::jsonb IS NULL (note the absence of single quotes) will return true this time.
One letter changes everything
SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json
json
json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}
---
SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb
jsonb
jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}The fact is that json and jsonb are completely different structures. In json, the object is stored as is, while in jsonb it is stored as a parsed indexed structure. This is why, in the second case, the object's value for key 1 was replaced from [1, 2, 3] to [7, 8, 9], which came into the structure at the very end with the same key.
You can't drink from the face of the water
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL in its JSONB implementation changes the formatting of floating-point numbers to their classic form. This does not happen for the JSON type. It's a bit strange, but it's their right.
Dossier number four. date/time/timestamp
There are also some oddities with date/time types. Let's take a look at them. I should immediately note that some of the behavioral peculiarities become clear if you understand how time zones work. But that is also a topic for another article.
Your understanding is different from mine
SELECT '08-Jan-99'::date
ERROR: date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Perhaps you need a different "datestyle" setting.
********** Error **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Symbol: 8What seems unclear here? But still, the database does not understand whether we placed the year or the day first? It decides that it is January 99, 2008, which blows its mind. Generally speaking, when passing dates in text format, one must be very careful to check how correctly the database recognized them (in particular, analyze the datestyle parameter with the command SHOW datestyle), as ambiguities in this matter can be very costly.
Where did you come from?
SELECT '04:05 Europe/Moscow'::time
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
^
********** Error **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL state: 22007
Character: 8Why can't the database understand the explicitly specified time? Because the time zone is specified not by an abbreviation, but by its full name, which only makes sense in the context of a date, as it takes into account the history of time zone changes, which does not work without a date. Moreover, the phrasing of the time string raises questions—what did the programmer actually mean? Therefore, it all makes sense if you think about it.
What’s wrong with it?
Imagine a situation. You have a column in your table with the type timestamptz. You want to index it. But you realize that building an index on this column is not always justified due to its high selectivity (almost all values of this type will be unique). So, you decide to reduce the selectivity of the index by casting this type to date. And you get a surprise:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
ERROR: functions in index expression must be marked IMMUTABLE
********** Error **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL state: 42P17What’s the issue? The problem is that casting from timestamptz to date uses the system parameter TimeZone, which makes the type casting function dependent on a configurable parameter, i.e., volatile. Such functions are not allowed in an index. In this case, you need to explicitly specify which time zone the type conversion is performed in.
When now isn’t really now
We are used to now() returning the current date/time with regard to the time zone. But look at the following queries:
START TRANSACTION;
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
COMMIT;The date/time returns the same regardless of how much time has passed since the last request! What's the deal? The fact is that now() is not the current time, but the start time of the current transaction. Therefore, it does not change within the transaction. Any request executed outside of the transaction is implicitly wrapped in a transaction, so we do not notice that the time returned by a simple SELECT now(); is actually not current... If you want to get the honest current time, you need to use the clock_timestamp() function.
File number five. bit
Strange a little bit
SELECT '111'::bit(4)
bit
bit(4)
------
1110From which side should bits be added in case of type expansion? It seems it should be from the left. But the database has a different opinion on this matter. Be careful: if the number of bits does not match when casting types, you will get something completely different than you wanted. This applies both to adding bits on the right and trimming bits. The same goes for trimming on the right...
File number six. Arrays
Even NULL didn’t fire
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Like normal people raised on SQL, we expect that the result of this expression will be NULL. But not so fast. An array is returned. Why? Because in this case, the database casts NULL to an integer array and implicitly calls the array_cat function. However, it remains unclear why this "array kitty" does not nullify the array. This behavior should also just be memorized.
Let's summarize. There are plenty of oddities. Most of them are certainly not critical enough to speak of outrageously inadequate behavior. Others are explained by ease of use or their frequency of applicability in certain situations. But at the same time, there are many surprises. Therefore, it is important to be aware of them. If you find anything strange or unusual in the behavior of any types, please write in the comments, I will gladly add to the existing files.
Source: habr.com
