{"id":93475,"date":"2022-03-01T17:20:56","date_gmt":"2022-03-01T17:20:56","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93475"},"modified":"2024-04-16T18:58:46","modified_gmt":"2024-04-16T18:58:46","slug":"data-types-in-postgresql-learning-postgresql-with-grant","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/data-types-in-postgresql-learning-postgresql-with-grant\/","title":{"rendered":"Data Types in PostgreSQL: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

When I decided that the next topic to cover would be data types, little did I know what I was getting into. According to the official documentation<\/a> for PostgreSQL, there are twenty categories<\/em> of data types. SQL Server only has 35 data types total. I thought about how to go about both learning the data types and writing this article. I\u2019ve decided this article will be just about the categories of data types, and then any interesting bits within those categories. Likewise, I\u2019m not going to drill down on specific data types now. There\u2019s simply too much to cover.<\/p>\n

This would also be a good time for a note about my process for learning PostgreSQL and my ability to translate that learning process to you. Understand that while I truly am just learning PostgreSQL, I have a large amount of experience with, and knowledge in, relational data structures. I started out in Paradox back in the 1980s before progressing to work professionally with Sybase, Oracle, Access, and SQL Server. So, while I am learning PostgreSQL from scratch, I\u2019m not learning a lot of the concepts from scratch. As such, I may gloss over information that would be vital to you if this is your first RDBMS.<\/p>\n

So, for the data types, I\u2019m going to discuss the 20 categories and call out some interesting facts. However, even at this level, there\u2019s probably some more fundamental aspects of data types you\u2019ll need if you\u2019re just learning for the first time.<\/p>\n

One point I want to make up front, and it\u2019s been true about every single data platform I\u2019ve worked with, you really should use the data types as they are intended and not try to force behaviors where they are not appropriate. What do I mean? I mean that a string is a string and that is different from a date, which is different from a number. Can you interchange these reliably? Yes. Should you? No. Stick to using the appropriate data type. That\u2019s as important in PostgreSQL as it is anywhere else.<\/p>\n

With that, let\u2019s get started.<\/p>\n

Overview of PostgresSQL data types<\/p>\n\n\n\n
\u00a0<\/p>\n

Numeric types<\/a><\/p>\n

Monetary types<\/a><\/p>\n

Character types<\/a><\/p>\n

Binary data types<\/a><\/p>\n

Date\/Time types<\/a><\/p>\n

Boolean types<\/a><\/p>\n

Enumerated types<\/a><\/p>\n

Geometric types<\/a><\/p>\n

Network address types<\/a><\/p>\n

Bit string types<\/a><\/p>\n<\/td>\n

\u00a0<\/p>\n

Text search types<\/a><\/p>\n

UUID types<\/a><\/p>\n

JSON types<\/a><\/p>\n

Array types<\/a><\/p>\n

Composite types<\/a><\/p>\n

Range types<\/a><\/p>\n

Object identifier types<\/a><\/p>\n

Domain types<\/a><\/p>\n

Pg_lsn type<\/a><\/p>\n

Pseudo types<\/a><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Numeric types<\/h2>\n

The numeric types are very straightforward. There are three integer types for holding whole numbers: smallint<\/code>, integer<\/code>, and bigint<\/code>. There are two precision decimal types: numeric<\/code> and decimal<\/code>. Then there are the floating types: real<\/code> and double<\/code> precision, which map to the mathematical constructs of singles and doubles. Finally, there is a set of numeric data types called serial<\/code>, which are not really data types. These are just automatically incrementing integers but set up so you can easily use them when defining a table. They\u2019re based on using a SEQUENCE<\/code> operation, which is available within PostgreSQL.<\/p>\n

I love the explanation that, even though these are auto-incrementing columns, you can, and likely will, see gaps. It\u2019s good for that information to be clear right up front.<\/p>\n

Except for the serial<\/code> type, the Numeric data types are very straightforward.<\/p>\n

Monetary types<\/h2>\n

Well, that\u2019s misleading. There is exactly one, money<\/code>. The only interesting thing here is that the decimal in money<\/code> is fixed, but you can change it. It\u2019s a database level setting you can adjust within a set range.<\/p>\n

Character types<\/h2>\n

I expected a very long list here and ended up with three: char<\/code>, varchar<\/code> and text<\/code>. The char<\/code> data type is fixed length with padding. This means if you define a column as pettype char(50)<\/code> and you put in the value cat<\/em>, there will be 47 spaces added. The varchar<\/code> data type is the same as variable character in other database systems. The column pettype varchar(50)<\/code> and a value of cat<\/em> means it stores just cat<\/em> without padding. The varchar<\/code> data type has a variable length as opposed to a fixed length string (another term for character). It\u2019s also possible to omit the size for the varchar<\/code> data type. In this case, it can hold any number of characters up to 1 GB.<\/p>\n

The text<\/code> type is meant for unlimited data up to 1 GB. It comes with a bunch of wrinkles, same as the similar data type in SQL Server.<\/p>\n

The most interesting thing is that there are not separate Unicode data types. Instead, each of these three data types supports Unicode. That\u2019s it. Easy. Honestly, I love this. I\u2019ve always found the VARCHAR\/NVARCHAR<\/code> found in SQL Server construct to be a pain.<\/p>\n

Binary Data types<\/h2>\n

Again, there is only one, the bytea<\/code> data type. I think I see where this is going. Some of the \u201ctypes\u201d are simply going to be a \u201ctype\u201d. Someone decided that defining them this way was better than just listing \u201cOther\u201d as a type with a bucket full of disparate data types.<\/p>\n

Anyway, it\u2019s a binary string called bytea<\/code>. There are some rules and behaviors around escape characters, but basically, it\u2019s for storing binaries like images within your database.<\/p>\n

Date\/Time types<\/h2>\n

I like the naming standards in PostgreSQL. It makes things much more straightforward than other systems I\u2019ve worked with (<cough>SQL Server<\/cough). The timestamp data type stores both date and time. The date data type stores dates, but not times. The time data type stores time, but not dates. Finally, there is an interval data type. The interval data type stores a time interval.<\/p>\n

I could spend the rest of this article talking about the interval data type. It\u2019s fascinating. You can define the precise interval, from microseconds to millennia. You can also add a direction by adding ago<\/code> to the input, effectively making it a negative interval.<\/p>\n

There are a number of configuration settings for controlling time zone, special values and other behaviors. There are also date and time specific functions as well as date\/time mathematics.<\/p>\n

This is one of those data types that frequently get abused. People want a specific format on their dates. Rather than fixing that at the server or application level, they store dates as strings, which is a bad mistake; it removes tons of functionality. Use these data types as intended.<\/p>\n

Boolean type<\/h2>\n

There is one Boolean type. Bool<\/code>. It\u2019s on or it\u2019s off. It\u2019s 1 or it\u2019s 0. That\u2019s it.<\/p>\n

Enumerated types<\/h2>\n

This one is interesting. There is only one enumerated<\/code> type, or there are an infinite number of enumerated types, depending on your point of view. Basically, this is an enum<\/code>, an enumerated list. Let\u2019s define one:<\/p>\n

CREATE TYPE pets AS ENUM ('cat', 'dog', 'horse', 'cockroach');\r\nCREATE TABLE mypets\r\n(mypet pets)<\/pre>\n
You can define a data type as a defined list. In this case, only values that make up the enum<\/code> pets can be inserted into the mypet<\/code> column.<\/p>\n
I\u2019m going to have to spend some time with this one. It could be amazing, or it could be a performance hellscape. It\u2019s unclear which, but maybe it\u2019s both.<\/p>\n
Geometric types<\/h2>\n
The geometric types make up another interesting category. There are a several, from point<\/code>, to line<\/code> segment<\/code>, to circle<\/code>, to the really fun one, path. You have a number of choices. There are also a several geometric functions<\/a> that go with these data types. Not much to add to this one. These are only two-dimensional types though. All the types are built off an X\/Y coordinate system.<\/p>\n
Network Address types<\/h2>\n
This is fascinating. Up to this point in the article, all the data types we\u2019ve seen have been pretty standard across database systems I\u2019ve worked with. Oh sure, a few small additions, but nothing quite like these network address types. They\u2019ve defined IPv4, IPv6 and MAC addresses as distinct data types. That ensures that you\u2019ll always input them correctly because of type checking. However, you also get specialized networking functions<\/a>, built right into the database.<\/p>\n
Honestly, this is cool. Actually, I\u2019ll go one further, this is what you get from open source software. Sure, you could probably build rules and user-defined data types into, say, a SQL Server database to emulate this. However, the work is on you to do it. There may also be software packages out there to help. Instead, built right into the database is a pretty standard set of needs for computer people.<\/p>\n
You get three types:<\/p>\n