I am currently working through a Udemy course where the instructor is using PostgreSQL and I am using MySQL with the result being that I have to do things a bit differently in order to achieve the same results. I thought this would be an efficient way to learn the nuances of both systems simultaneously (I also have access to PostgreSQL on my MAC). Currently I am learning about single line functions, and encountered a pretty big difference with how both databases handle the capitalization of the first letter of a word. With PostgreSQL it is as easy as:
SELECT INITCAP('hello')
result is 'Hello'
In MySQL however, it gets a bit more complicated:
SELECT CONCAT(UPPER(SUBSTRING('hello',1,1)),LOWER(SUBSTRING('hello',2)))
Result is also Hello.
The image below shows a running example in MySQL where instead of using a string I have used the name of one of columns in the employees table.
I decided to keep track of the differences to use for future references since I spent several minutes googling how one would capitalize the first letter of a word in MySQL. Future Jo will thank me I'm sure.
I will continue adding here as I encounter more differences between the two databases.
Differences in Concatenation Between PostgreSQL and MySQL
Concatenation is briefly mentioned above but I thought I would go into more in depth here.
In PostgreSQL we concatenate strings or columns by using 2 pipe symbols(in some programming languages like javascript, the || means 'or'.
In PostgreSQL it is used to concatenate like so:
SELECT hire_date ||' '|| '09:00' FROM employees;
I included the ||' '|| to put some space between the date and the time.
PostgreSQL also makes use of the CONCAT function in the same way that MySQL, offering even further flexibility.
In MySQL, we use use the CONCAT function to concatenate:
SELECT CONCAT (hire_date,' ', '09:00') AS Date
FROM employees;
Here we see the CONCAT can take multiple expressions and they are separated by commas, not | |.
Differences in date in MySQL and PostgreSQL
When converting dates in Postgresql, we use the to_char function which accepts the column we want to convert, as well as the format with which we wish to convert as demonstrated by so:
SELECT to_char(hire_date, 'yyyy/mm/dd')
FROM employees
The year, month and day do not need to be separated by a slash(/). They can also be separated by a dash(-) or a space.
My favorite part about working with dates in PostgreSQL is how flexible and intuitive it seems to be, just check out the example below:
The code is:
SELECT hire_date, to_char (hire_date, 'yyyy Mon day the dd')
FROM employees;
The results as displayed are in the format 2006 Apr thursday the 20
Things work a bit differently in MySQL as the date_format function is used instead of the to_char. MySQL also seems less intuitive in terms of writing the date format
SELECT date_format(hire_date,'%b %D %Y')
from employees;
Upon reading the above, it may not be clear how the function would actually convert the date.
Here is a reference for what the symbols mean MySQL Date references
Subtle Differences between Trunc and Truncate in MySQL and PostgreSQL
As I was testing out my sql queries in my two different databases, I realized that there are subtle differences in the way both databases handle the trunc function that may be a bit confusing initially.
The MySQL TRUNCATE() function truncates a number to the specified number of decimal places.
You must specify the number of decimal places when using the Truncate function in MySQL. If you don't, you will see this error returned to you :
The TRUNCATE() function truncates a number to the specified number of decimal places.
In Postgresql, the function we use to accomplish the same thing is called TRUNC(), and you do not need to specify decimal numbers the way you do in MySQL. At least if you don't it will not explicitly return an error.
Boolean Expressions
I have started my THIRD Udemy SQL course (applause please) and while some of the information has become repetitive, in a lot of ways, it isn't. I am still learning new ways to do different things and how am still learning about the different nuances of PostgreSQL and MySQL.
In this particular course, I was introduced to Boolean expressions which I think is a really helpful way of displaying information. The query used to display Boolean expression is exactly the same in both databases but the results are slightly different (at least, they are aesthetically different in how they're represented) But, the mean the same thing:
SELECT first_name, last_name, salary, (salary > 140000)
AS SalaryOver140K
FROM employees
Order By SalaryOver140K;
Here are the results in mySQL
Conversely, here are the records in PostgreSQL
In mySQL, the 0 represents false and the 1 represents True. This representation is a lot more clear in PostgreSQL, which tells you clearly if the condition is "True" or "False"
Top comments (2)
Postgres, MySQL, MS-SQL, SQLite and Oracle all implement the ANSI-92 standards but also have proprietary extensions as you have discovered. If you want your database code to be portable between platforms you need to stick to the ANSI standards but I have never seen it in real life. The cost of delevoping portable database code is probably more expensive than refactoring when a migration is required and you will lose out on platform specific optimisations
The other option is to abstract away the SQL queries with a generator that is acutely aware of subtleties in different database systems. That's one of the main goals of PUDL! :) github.com/darkain/pudl