I am still learning new ways to do different things and how am still learning about the different nuances of PostgreSQL and MySQL. 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. At least if you don't it will not explicitly return an error. 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. The TRUNCATE() function truncates a number to the specified number of decimal places. If you don't, you will see this error returned to you : You must specify the number of decimal places when using the Truncate function in MySQL. The MySQL TRUNCATE() function truncates a number to the specified number of decimal places. Subtle Differences between Trunc and Truncate in MySQL and PostgreSQLĪs 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. Here is a reference for what the symbols mean MySQL Date references Upon reading the above, it may not be clear how the function would actually convert the date. MySQL also seems less intuitive in terms of writing the date format Things work a bit differently in MySQL as the date_format function is used instead of the to_char. The results as displayed are in the format 2006 Apr thursday the 20 SELECT hire_date, to_char (hire_date, 'yyyy Mon day the dd') My favorite part about working with dates in PostgreSQL is how flexible and intuitive it seems to be, just check out the example below: They can also be separated by a dash(-) or a space. The year, month and day do not need to be separated by a slash(/).

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: Here we see the CONCAT can take multiple expressions and they are separated by commas, not | |.ĭifferences in date in MySQL and PostgreSQL SELECT CONCAT (hire_date,' ', '09:00') AS Date In MySQL, we use use the CONCAT function to concatenate: PostgreSQL also makes use of the CONCAT function in the same way that MySQL, offering even further flexibility. I included the ||' '|| to put some space between the date and the time. SELECT hire_date ||' '|| '09:00' FROM employees In PostgreSQL it is used to concatenate like so:

In PostgreSQL we concatenate strings or columns by using 2 pipe symbols(in some programming languages like javascript, the || means 'or'. I will continue adding here as I encounter more differences between the two databases.ĭifferences in Concatenation Between PostgreSQL and MySQLĬoncatenation is briefly mentioned above but I thought I would go into more in depth here.

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. 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. SELECT CONCAT(UPPER(SUBSTRING('hello',1,1)),LOWER(SUBSTRING('hello',2))) In MySQL however, it gets a bit more complicated: 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. 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). 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.

Mysql vs postgresql 2017