There are many flavors of SQL – Oracle, SQL Server, MySQL, BigQuery, PostgreSQL – and just like that there are various version of SQL for each flavor. This isn’t about what is different though, this is about SQL data types and what to do if you need to make change types on the fly.
Let’s dive right in!
Like I said above there are many flavors of Sql and there is a long list of things that are very similar in each of these, but there is also just as long a list that is different between them.
I have personally used SQL Server, PostgreSQL, MySQL, and Google BigQuery in my professional and freelance life and every time I feel something should work, it doesn’t, and I have to consult the almighty Google.
This article is not about all that is different between the flavors. In this article I want to highlight the data types present in all SQL flavors.
Standard SQL language does have a governing body which is names the standard dialect of SQL ANSI SQL. You can peruse the documentation, but what it says is that there needs to be a standard Dialect that all platforms must comply with. That’s is why this article will focus on such.
There is a wonderful resource out that that shows you what is present in each of the version and also a variation to use that works with the platform you are using. You can find that here.
There are 4 categories all with their own repertoire of useful data types. The 4 categories are:
- Strings
- Numeric
- Date and Time
- Boolean
String Data Type
Easily on of the most common data types that is present is all programming languages and is also called character data type. It is also probably part the first program that you ever wrote in the form of "Hello World!"
CHAR() and VARCHAR() are two of the character types in ANSI SQL. CHAR() and VARCHAR() are able to hold the same size sting length of 8,000. The main difference between the two is what happens after the table is created.
When you create a table with a defined column of CHAR(4) then the column is fixed with a string length of only 4 characters (ex. ‘abcd’) which takes up the same storage no matter what. When you defined a table with a column of VARCHAR(4), then this can hold up to 4 characters as well, but will take up a variable size if there are less characters. In essence, CHAR() is a fixed size and VARCHAR() can vary up to the amount specified.
Numeric Data Type
There are two numeric data types, INT() and DECIMAL(). The main difference between the two is INT() has not decimals while DECIMAL() has them, obviously.
For INT() you can store a number from -2³¹ to 2³¹-1. That is a pretty big number! DECIMAL() numbers can store a number up to 38 including the left and right of the decimal.
When creating a column with INT there is no need to specify anything, it automagically can hold the max. When creating a DECIMAL column, however, you do have to specify two things. The first is how many digits to the left of the decimal and the second is how many digits to the right of the decimal up to 38 total.
Date and Time Data Type
I want to start with the obvious data types here, DATE() and TIME(). When specifying a column as a date, you will get dates in the form of YYYY-MM-DD, and when specifying a column as TIME(), you will get times in the form of hh:mm:ss.
There are a some other useful functions that reside in this category that will return the current date and time of your computer. NOW(), TODAY(), and GETDATE() are a few of them.
Boolean Data Type
This is a unique data type that contains either TRUE of FALSE. You might be asking why this is on here because SQL does not have this sort of data type. You would be right in most instances. There is one, however, that has it and that is PostgreSQL.
In PostgreSQL a column is defined as a boolean by simply writing BOOLEAN and returns either a ‘t’ or an ‘f.’
All other SQL flavors do not have a boolean value per se, but they do have workarounds. One of the workarounds in by defining a column as a NUMBER(1) meaning that it can only take a 1 or a 0 where 1 is true and 0 is false. There is another one called BIT() that will do that same thing.
Converting Data Types
One more useful technique is having the ability to change data types on the fly. You achieve this by use the CAST() or CONVERT function(), but they do the same thing.
The CAST function is called in the following way CAST('2020-06-11' AS DATE)
. You can also use CONVERT(), CONVERT(DATE, '2020-06-11')
.
While this is just a taste of data types in SQL, I would encourage you to conduct a google search about data types in SQL to really get a feel for what is out there. Keep in mind too that I did my best to include data types that are present in all flavors of SQL, but if there is one that is not standard in all of the flavors, again, a quick google search will do some good.
In case you missed this great resource, I will link it again here. This give a brief overview of data types and what is available in each flavor of SQL and what to use instead if something does not work for what you are using.
SQL compatibility table (nils85.github.io)
I hope you learned a little bit about data types and thank you for reading.