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:
In MySQL however, it gets a bit more complicated:
Result is also Hello.
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.
Concatenation is briefly mentioned above but I thought I would go into more in depth here.
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
Here we see the CONCAT can take multiple expressions and they are separated by commas, not | |.
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')
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')
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')
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
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.
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)
Order By SalaryOver140K;
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"