CASE

CASE will execute SQL code when certain conditions are met (similar to an IF/ELSE statement).

GENERAL SYNTAX - TYPE 1:

CASE
WHEN condition 1 THEN result1
WHEN condition 2 THEN result2
ELSE some other result
END;

GENERAL SYNTAX - TYPE 2:

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE some other result
END;

With Type 1, you can specify multiple different conditions. With Type 2, you can only specify one condition.



COALESCE

COALESCE evaluates a number of arguments and will return the first result that is not NULL. It is useful for substituting NULL values with another value.

Example:

Item Price Discount
A 100 20
B 300 NULL
C 200 10
  • SELECT item, (price-discount) AS final FROM table
Item final
A 80
B NULL
C 190
  • SELECT item, (price-COALESCE(discount,0)) AS final FROM table
Item final
A 80
B 300
C 190

CAST

CAST lets you convert from one data type into another.

  • SELECT CAST(‘5’ AS INTEGER) - General SQL syntax
  • SELECT ‘5’::INTEGER - Another syntax option in PostgreSQL


NULLIF

NULLIF take two inputs.

  • If both are equal - it returns NULL
  • If they are not equal - it returns the first argument
  • This is valuable if a NULL value would cause an error







VIEWS

You can create a VIEW to quickly see the results of a commonly used query.

  • A VIEW is a stored query
  • It does not store the data





IMPORT AND EXPORT

https://www.postgresql.org/docs/13/sql-copy.html

  • The Import command does not create a table. It assumes that the table is already created.





Python Psycopg2