Tag: data types

The 6 SERIAL Data Types in PostgreSQL

Posted on by Ian

The SERIAL data type in PostgreSQL is a pseudo-type used to create an auto-incrementing sequence of integers for a column. It is commonly used for primary keys, as it eliminates the need to manually assign unique identifiers for each new record. PostgreSQL handles this by automatically creating a sequence object that supplies a unique number each time a new row is inserted.

PostgreSQL provides three SERIAL types, each with two options for usage; which effectively equates to six different types. So basically, we have six options to choose from when creating a SERIAL column.

Continue reading →

Understanding the SERIAL Type in MySQL

Posted on by Ian

If you work with MySQL databases, you may have encountered the SERIAL type in a table’s definition. And if you’ve come over from PostgreSQL, you might have a false expectation about how MySQL’s SERIAL works – unless you already know 😉

Below is a quick overview of MySQL’s SERIAL type, including an explanation of how it differs from PostgreSQL’s SERIAL type and similar functionality from other DBMSs.

Continue reading →

How to List all Domains in PostgreSQL

Posted on by Ian

In PostgreSQL, domains are basically data types with optional constraints. We can create them as a kind of user-defined data type, and then reuse them in our columns going forward.

As with any user-created object, we sometimes need to see a list of existing domains in a PostgreSQL database. Listing all domains can be useful for database management, documentation, or troubleshooting.

This article will show you how to retrieve a list of all domains in your PostgreSQL database.

Continue reading →

Example of PostgreSQL Automatically Creating a Nested Composite Type

Posted on by Ian

Whenever we create a table in PostgreSQL, a composite type is automatically created behind the scenes. This composite type is based on the table that we created. Each column in the table becomes a field in the composite type.

If the table already uses a composite type, then the composite type that PostgreSQL creates will include that type in its definition, thereby creating a situation where we effectively have a nested composite type.

Continue reading →

Fix “Column, parameter, or variable #1: Cannot find data type” in SQL Server (Error 2715)

Posted on by Ian

If you’re getting SQL Server error 2715, which reads something like “Column, parameter, or variable #1: Cannot find data type SERIAL“, it appears that you’re trying to define a column to have a data type that’s not supported in SQL Server.

To fix this issue, be sure to use a supported data type for all columns.

Continue reading →

Understanding SQL Data Types: A Comprehensive Guide

Posted on by Ian

Structured Query Language (SQL) is the backbone of relational database management systems (RDBMSs), enabling users to query, manipulate, and define data. One of the most fundamental concepts in SQL, and one that all SQL developers should understand, is the data type.

Whenever we create a column in SQL, we must define its data type. Similarly, when we create a variable, we define its data type.

So, why is the data type so important? Let’s find out.

Continue reading →

4 Ways to Insert Composite Data in PostgreSQL

Posted on by Ian

When we have a column that’s defined as a composite type in PostgreSQL, we have some options when it comes to inserting data. For example, we can explicitly specify each individual field of the composite type, or we can use a row constructor to insert all fields at once.

Below are four different options for inserting composite values into a column in PostgreSQL.

Continue reading →

Create an ENUM Type in PostgreSQL

Posted on by Ian

Some RDBMSs support an enum type, which comprise a static, ordered set of values. Also known as enumerated types, enum types can be handy for making a column accept just a small set of values, such as days in the week, predefined clothing sizes, or any number of other preset values.

In PostgreSQL, if we want to create a table that uses an enum type, we need to create the enum type first, then apply it against the table. This is a bit different to other RDBMSs such as MySQL, where we don’t need to create the enum type first.

Continue reading →