PostgreSQL Cheat Sheet

PostgreSQL is one of our favorite database engines for a variety of reasons.  Here is our cheat sheet to help you get online and get around Postgres with minimal effort. Database Access Security Database security is handled primarily in two place, from the system service level via a file called pg_hba.conf and within the database metadata files themselves.   The pg_hba.conf file controls what level of credentials are needed based on what IP address the requesting connection is coming from.   The metadata within the engine itself generally controls user level access once they are connected and approved at the system level. Systemwide Configuration via pg_hba.conf This file matches IP address with a set of rules to determine how much data you need to provide in the first place before getting access to the database engine.   It includes the IP address, the username ...

Powerful Data Insertion Features in MySQL

There are several powerful techniques for getting your data into a MySQL database. MySQL supports non-standard extensions to SQL, giving you more flexibility in certain situations. If you want to take full advantage of MySQL, keep these tips in mind. The Problem Say you need to shove some new data into a table.  Naturally, you want to INSERT.  But it so happens that if the data is already there, you want to UPDATE the existing data. You don't care if you have to INSERT or UPDATE - you just want the new data in the table. You decide do one of two things. You might try DELETE FROM yourtable WHERE key='yourkey'; INSERT INTO yourtable (key, data1, data2) VALUES ('yourkey', 1, 2); This is simple and effective. You could also check to see if the record already exists, and if so, UPDATE. What you really wish you had is an "INSERT OR UPDATE" command. Allow me ...