Moving from MySQL to PostgreSQL

Using MySQL, MSSQL  for a long time and moving to PostgreSQL would be confusing and tiresome process for some of the developers, as there are some commonly used queries, practices are different in Postgres. Some of these changes seem strange and take sometime to find the existing queries, understand and fix them to make it work again.

Here I’ve compiled some of the changes to get started in fixing the Queries and write new Queries.

MySQL PostgreSQL
Comment Uses Non standard # to begin a comment Line PostgreSQL use ‘–‘ (double dash), as this is the ANSI standard, and both databases understand it.
Quotes Uses ‘ or ” to quote values. in WHERE clause you can use both. This is not ANSI standard for db uses only single quotes for this. in WHERE clause you can only use ‘ single quotes. Double quotes are used to quote system identifiers; field names, table names, etc.
accent/Backtick uses ` (accent mark or backtick) to quote system identifiers This is non standard. so not used/allowed
String Comparison string comparisons are case-insensitive string comparisons are case-sensitive

  • Need to use correct case in queries
  • Need to use case conversion function to search
  • To search / compare case-insensitive use operator, like ILIKE or ~*
Database, table, field and columns names Database, table, field and columns names can be case-sensitive or not, depending on which OS you are using Database, table, field and columns names are case-independent, unless they are created with double-quotes around their name, in which case they are case-sensitive.
Date Handling Both differ most in handling of dates, and the names of functions that handle dates.
Logical Operators uses C-language operators for logic. Ex: ‘foo’ || ‘bar’ means ‘foo’ OR ‘bar’, ‘foo’ && ‘bar’ means ‘foo’ and ‘bar’. This violates database standards and rules in a significant way Follows the standard, uses || for string concatenation Ex: ‘foo’ || ‘bar’ = ‘foobar’.



Installing Apache,PHP5,MySQL on Ubuntu

sudo apt-get install apache2

sudo apt-get install php5
sudo apt-get install libapache2-mod-php5

sudo /etc/init.d/apache2 restart

To install the MySQL Server and Client packages.
sudo apt-get install mysql-server mysql-client
This will install the mysql server and client packages.
By default, recent Ubuntu/Debian systems install a MySQL Server from the 5-branch.

Set MySQL Root Password
By default, the root account of the MySQL Server is empty. To set the mysql root password do the following.
sudo mysqladmin -u root -h localhost password ‘mypassword’ #replace the ‘mypassword’ with your password.

Connect to MySQL from PHP / Ruby
Now you have Apache+PHP already installed, and want to connect to MySQL from PHP scritps.
For that you have to install one library, which is used to connect to mysql from PHP. Use this command to install the library.

sudo apt-get install php5-mysql

In case you are using Ruby, use the command
sudo apt-get install libmysql-ruby to connect to MySQL from Ruby.

Note : while restarting the apache if you get the following error
apache2: Could not reliably determine the server’s fully qualified domain name, using for ServerName

then open the /etc/apache2/apache2.conf file and add the following line
as the last line

ServerName “YourSitename or Servername”

save the file and restart the apache by giving the /etc/init.d/apache2 restart command, you will not get the error anymore.

To Install MySQL Query Browser, an GUI for MySQL

sudo apt-get install mysql-query-browser

After installation, to start MySQL Query Browser go to Applications > Programming > MySQL Query Browser.

Install PHP-GD Library

The GD Graphics Library is useful for dynamically creating, manipulating images. You will need to compile PHP with the GD library of image functions for this to work. However Ubuntu (and Debian) comes with package called php5-gd

To install GD for PHP use the following command.

sudo apt-get install php5-gd

I’ve collected the information from net which are required while installing and configuring Apache, PHP, MySQL and given here. Hope this will help for somebody who can get all information at one place.