PostgreSQL update timestamp when row is updated

In PostgreSQL, if you want to set current timestamp as default value, you can simply keep a column's default expression as 'now()'. However, by default there is no function defined to update the timestamp when a particular row (or multiple rows) are updated.

In such scenario, you may create your custom method and trigger it using PostgreSQL's Triggers. Following snippet will make it more clear:

Here, we are creating a new method, 'method_get_updated_at()'

CREATE OR REPLACE FUNCTION method_get_updated_at() RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.<column_name> = now();
  RETURN NEW;
END;
$$;


Once it is created, this is the snippet for triggering it:

CREATE TRIGGER trigger_<column_name>
BEFORE UPDATE ON <table_name>
FOR EACH ROW
EXECUTE PROCEDURE method_get_updated_at();


If you want to delete a Trigger, you can use this simple command:

DROP TRIGGER IF EXISTS trigger_<column_name> ON <table_name>

Note: Please update the table_name and column_name accordingly and execute the code for your database. Also, note that, some web frameworks like Rails, manage such columns(created_at, updated_at) automatically.

Accessing PostgreSQL server through a SSH Tunnel

Step 1: Check the SSH connectivity with the server, verify username and password.

Step 2: Create the tunnel in your local system by executing the following command (It will prompt for password):
$ ssh -fNg -L 5555:localhost:5432 <user>@<server>

Step 3: Now, open your PostgreSQL client (eg, pgAdmin 3 / DBeaver / Postico for OS X / Terminal) and fill in the connection details as usual. Check the image below.
Postico for OS X


Fixing the VNC's blank/grey screen with three checkboxes problem (~/.vnc/xstartup in Ubuntu)

After installing VNC server on Ubuntu (usually occurs on servers), sometimes one may face a problem in which after logging in by a VNC client/viewer, it shows just a blank screen with three checkboxes. (See the image at the end of post)

To fix this issue, first of all, Check if you have any Desktop Environment (eg, Unity, Gnome, KDE, xfce, lxde etc) installed on your system. You may use: echo $DESKTOP_SESSION or you can verify from env.

In my case, I have installed LXDE on Ubuntu server. It is important now to find the exact executable path of lxde, which is /usr/bin/startlxde in my case.

Replace the content of your ~/.vnc/xstartup and paste following lines:
#!/bin/sh
xrdb $HOME/.Xresources
xsetroot -solid grey
export XKL_XMODMAP_DISABLE=1
/usr/bin/startlxde
Note: Above code is only valid for lxde.

Should work fine now! :)