PostgreSQL - Study Notes 5

Cross join:

Cross join is the default for an unqualified join.

     chr()

          

 

Grouping:

      any_value()

 

     array_agg()

 

              json_agg(), jsonb_agg()

 

     bool_and()

 

     bool_or()

 

 

     filter

 

     string_agg()

 

count(*) counts the number of rows in the result set. count() of a column name gives the number of non-null values in that column. You are not penalized for counting the number of rows, it's not consuming the entire of rows. It is optimized to count rows, so you don't worry about should you use count(*) or should I use count(id). Use count(*) because that is going to be optimized to get back exactly what you want in the fast way as possible. If you need count non-null values, use count(column_name).

 

 

Grouping sets:

 

 

Rollups:

Beyond grouping sets, we have another option - rollups. Roolups under the hood is just grouping sets, but it's a way to write grouping sets very quickly.

 

 

Cubes:

 

 

Window functions:

 

 

     first_value()

......

 

     last_value()

 

 

 

     unbounded preceding, unbounded following

 

     row_number()