PostgreSQL data types and more
When writing a program that manipulates these objects, the programmer must create a database to save all the work performed on the aforementioned objects by the program– these objects are also referred to as data types.
In object-oriented programming [OOP], what a programmer does ultimately can be summarized by the manipulation of objects. These objects are various data types that interact with each other while respecting a specific set of rules – themselves depending on the programming language or framework in use.
When writing a program that manipulates these objects, the programmer must create a database to save all the work performed on the aforementioned objects by the program– these objects are also referred to as data types.
Although there are many different databases, we will focus on one only here : PostgreSQL – and more specifically on the types of data it can store.
Let's start by addressing the elephant – pun intended – in the room: what exactly is Structured Query Language [SQL]?
What is SQL?
SQL is a programming language used to interact with and manipulate databases. It enables to create, modify and query databases, as well as to control access to them.
It has become the standard language in the development world.
Anything that has to do with databases, such as web applications, data warehouses and business intelligence systems is often handled in SQL.
SQL is a powerful and flexible language that allows users to perform a wide range of tasks, including creating and altering database structures, inserting and updating data, and querying data for specific information.
There are many SQL databases tools to be used, but some rise in popularity faster than others.
Why is Postgres so popular?
Postgres has undeniably become one of – if not the one – most popular relational database management system [RDBMS].
The reasons why are many but, if we’re to name a few, one could argue that it is largely due to the facts that it is secure, scalable, protects data integrity and because it complies with ACID principles [Atomicity, Consistency, Isolation, Durability] to ensure safe transactions.
Alternatives to Postgres include but are not limited to MySQL, MariaDB, Microsoft SQL Server or MongoDb – all of which Forest Admin integrates at ease.
There are several factors that can influence the choice of a database for a particular project. The Data model, performance, cost and the possibility to integrate the database with other systems if needed.
Getting into the meat of it now, what data types can be stored in PostgreSQL?
Data types
In PostgreSQL, a data type is a specific kind of object that can hold a particular type of data.
There are data types families and then the actual data itself.
When it comes to families, here they are, from the most basic to the most special:
- Boolean
- Character Types [char, varchar, and text]
- Numeric Types [integer and floating-point number]
- Temporal Types [date, time, timestamp, and interval]
- UUID [Universally Unique Identifiers]
- Array [ array strings, numbers, etc.]
- JSON [JSON data]
- hstore [key-value pair]
- Special Types [network address and geometric data]
Here is a list of some common data types in PostgreSQL with a detail of what it is, and an example – again, each of them belonging to a family mentioned above:
- integer: stores a 32-bit signed integer – 42
- bigint: stores a 64-bit signed integer – 8092233477720368557
- smallint: stores a 16-bit signed integer – 26727
- decimal: stores a fixed-point decimal number – 56.11
- numeric: stores a variable-precision decimal number – 12345.678
- real: stores a single-precision floating-point number – 3.14
- double precision: stores a double-precision floating-point number – 7.14159232384665358979
- boolean: stores a true or false value – true
- char: stores a single character – ’J’
- varchar: stores a variable-length character string – ’We are Forest Admin’
- text: stores a variable-length character string – ’ We are a back office solution that helps businesses manipulate and better understand their data.’
- date: stores a date (year, month, day) – '2022-12-16'
- time: stores a time (hour, minute, second) – '18:45:22'
- timestamp: stores a date and time (year, month, day, hour, minute, second) – '2022-12-16 18:45:22'
- interval: stores a duration of time (e.g. 2 days, 3 hours) – ’5 days 7 hours’
- array: a collection of items that are stored in a contiguous block of memory – [‘Hello’, 4, ‘World’, 89]
Note that these are just examples and may not necessarily represent the exact syntax for using these data types in a PostgreSQL database.
Char may require a length specifier and timestamp most often than not has additional formatting options.
Each data type has its own set of properties and limitations.
Alternatives to SQL
Although we covered PostgreSQL, there are various alternatives to SQL that you may want to consider depending on your use case and, let’s face it, your inclinations.
Alternatives such as NoSQL [MongoDB, Cassandra], NewSQL [Google Cloud Spanner], Graph [Neo4j, Apache TinkerPop], Key-value [DynamoDB], Column-oriented [Apache HBase] or Object-oriented [GemStone].
If you feel like chatting with us because you're curious about what we do and how we can help you, please reach out to us.
If you want to know more about various Databases, here are a few articles we’ve written on the subject: