Cacher is the code snippet organizer for pro developers

Using CASE Statements in PSQL

• Using a CASE statement in the SELECT clause creates a column which you can alias:

SELECT a,
  (CASE WHEN column_name = some_value THEN "COLUMN VALUE"
     WHEN column_name = another_value THEN "ANOTHER COLUMN VALUE"
  END) AS 'PARM'
FROM ...

• MUST WRAP IN PARENTHESES!

---

SubQueries

• You can create a column using a subquery (A SELECT within a SELECT)

SELECT *,
  SELECT (name FROM ...) AS "PARM"
  ...

• You can also insert a SELECT inside a FROM clause!
  • Remember, a SELECT can return a table which is what you are passing to when using the FROM clause!

 SELECT column_name1, column_name2
 FROM (SELECT col_n1, col_n2, column_name1
       FROM table
       GROUP BY column_name1) AS "..."
ORDER BY column_name2

REPLACE

• Use the REPLACE function to remove part of the text entry in a record's column field
• Returns a column

SELECT col_1,
  REPLACE(column_name, string_you_want_to_replace, string_replacing_the_removed_string) AS ''
  ...

---

Obtaining all the column names in a table

SELECT * 
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'table_name'

• information_schema is an actual table in the postgresql database
• table_schema = 'public' is the name of the schema in your psql database

---

Converting a list of items into a table

• This will return a table that contains table names not found in the unnest(ARRAY) list from the information_schema.tables

SELECT * FROM unnest(ARRAY['services', 'order_logs', 'art_elements'])
WHERE unnest NOT IN (
    select table_name
    FROM information_schema.tables
    where table_schema = 'public'
)

---

TIP

• If, when you add a filter in the WHERE clause reduces the amountof records obtained, but you still need all the records try moving that filter to the ON part of the JOIN table clause