Search This Blog

Tuesday, June 5, 2018

PostgreSQL Lateral Joins

Example from this this webpage: 

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
  (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 |                            |


Monday, June 4, 2018

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. 

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;

Wednesday, January 31, 2018

Raspberry Pi 3 and I2C issues with Pulsed Light v2 -- returning 0 from Lidar-Lite

Lidar-Lite v3 return always zero with raspberry pi 3

Trying to get an Adafruit servo hat to work on Raspberry Pi 3... and a PulsedLight Lidar-Lite v2 unit.

Using I2C for comms - servo hat on 0x40 and Lidar on 0x62.

Tried on Pi2 and the Lidar was fine but the same code returns 0 for distance/velocity on Pi3.

Running   sudo i2cdetect -y 1 returned the correct addresses (0x62 for the Lidar)
 (if I2C tools not installed then first  run these commands)
sudo apt-get install python-smbus
sudo apt-get install i2c-tools

Issue is that the latest Raspbian kernel is auto restarting the I2C and the LidarLite unit doesn't like this.. hence python code returns 0 for distance and 0 for speed - even though the I2C detect has demonstrated the device is connected correctly.


Bump the kernel back to an earlier version - I'm using 4.4.38 which works.  

You copy the hex code (in this case af9cb14d5053f89857225bd18d1df59a089c171e)  and run it like this:

If rpi-update not installed first run this:       sudo apt-get install rpi-update

sudo rpi-update af9cb14d5053f89857225bd18d1df59a089c171e 

... it'll take a while then ask you to reboot your Pi. If you get an error message then try running the command again (possibly a few times) as it could be a missing packet of data from the network corrupting the file - this happened to me twice - 3rd time it worked fine. 

Thanks to Isa Hatipoglu for the information on rpi-update: