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!