PostgreSQL Lateral Joins

Example from this this webpage:
https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df 


The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

Lateral joins are like running an iteration over the joined subquery.

id | user_id | created_at ----+---------+---------------------------- 1 | 1 | 2017-06-20 04:35:03.582895 2 | 2 | 2017-06-20 04:35:07.564973 3 | 3 | 2017-06-20 04:35:10.986712 4 | 1 | 2017-06-20 04:58:10.137503 5 | 3 | 2017-06-20 04:58:17.905277 6 | 3 | 2017-06-20 04:58:25.289122

SELECT user_id, first_order_time, next_order_time, id FROM
  (SELECT user_id, min(created_at) AS first_order_time 
       FROM orders GROUP BY user_id) o1
  LEFT JOIN LATERAL
  (SELECT id, created_at AS next_order_time
   FROM orders
   WHERE user_id = o1.user_id AND created_at > o1.first_order_time
   ORDER BY created_at ASC LIMIT 1)
   o2 ON true;

 user_id |      first_order_time      |      next_order_time       | id
---------+----------------------------+----------------------------+----
       1 | 2017-06-20 04:35:03.582895 | 2017-06-20 04:58:10.137503 |  4
       3 | 2017-06-20 04:35:10.986712 | 2017-06-20 04:58:17.905277 |  5
       2 | 2017-06-20 04:35:07.564973 |                            |

Source: https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df  

Moving data folder for PostgreSQL on Windows

If you run out of space on a server you may need to move the data folder for PostgreSQL to a new drive. On Windows the easiest way to do this is stop the service, copy the data folder, then unregister the service and re-resister it pointing to the new data folder space. This is answered in the following STACKOVERFLOW answer.


https://stackoverflow.com/questions/22596301/how-to-change-postgresql-data-directory 

(Note: for Linux be aware the drive mounting will need to allow the postgres user to access it... so even if the folder has chown -R postgres:postgres /mnt/datadrive/pgdb   if the /mnt/data drive doesn't allow the postgres user then it won't work.. so run sudo chmod 777 /mnt/datadrive first.  For other folders use chmod 700 -Rv /mnt/datadrive/pgdata  and chown postgres:postgres -Rv /mnt/datadrive/pgdata)

To move the directory, use these steps:

1. Stop Postgres (you can use the control panel to find the correct service name)

   net stop 
   

2. Make sure Postgres is not running (e.g. using ProcessMonitor)
3. Remove the Windows service using

   pg_ctl unregister -N 
   

4. make sure Postgres is not running
5. move the data directory to the new location (or maybe only copy it, so that you have a backup)
6. re-create the service using (this assigns postgres as the service name)

   pg_ctl register -N postgres -D c:\new\path\to\datadir
   

7. start the service

   net start postgres 
   

8. run psql to verify that Postgres is up and running

   psql -U postgres
   

9. Verify the running server is using the new data directory

   show data_directory