Postgresql Basics

I’m back to tell you a few basic things about Postgresql for anyone struggling or just looking for answers in general.

The main basics still apply!  SELECT, FROM, JOINs, WHERE, HAVING, GROUP BY, ORDER BY.

SUBQUERIES:  All subqueries need an alias.

COALESCE:  Used like ‘NVL’ or ‘ISNULL’.  Here’s the syntax:  COALESCE(<column name>, <value to be used if it is null>, <next value to be used if that value is null>).  Here’s an example:  COALESCE(Invoice_Amount, 0).  So this example means in the event the ‘Invoice_Amount’ column is null, you would like a zero placed there.  You can also use cascading COALESCE values.  Here’s the syntax:  COALESCE(<column name>, COALESCE(<value to be used if it is null>, <next value to be used if that value is null>)).  Here’s an example:  COALESCE(Trip_Purpose, COALESCE(Exempt_Reason, ‘None’)).  Note:  Don’t enclose the field names in single quotes; only do this with a value you are identifying like ‘None’ in this example.

REPLACE:  Super helpful.  Allows you to replace something in a field with something else.  When to use?  Well, if you’re going to export the query to a csv file and some of your fields have commas, you’ll want to use this handy feature.  Here’s the syntax:  REPLACE(<column name>, ‘<value in field to replace>’, ‘<value to put in its place>’).  Here’s an example:  REPLACE(Job_Title, ‘,’, ‘ ‘).  May be hard to see but in this example we want to replace the comma with a space.  The value to be replaced and the value to replace it with need to be enclosed in single quotes.  An example of a before and after:  Value before REPLACE:  Manager, Operations.  Value after REPLACE:  Manager Operations.

Well that’s all I have time for today.  Stay tuned for more helpful info on various topics including Postgresql, Business Objects, Crystal Reports, Cognos, and Tableau!

BITietje 🙂