Raspberry Pi display problems

Having successfully installed a new Raspberry Pi with Raspbian OS, it is common that the next reboot the screen display just leaves a blank screen. You just know its the display setting run when at the boot screen the system messages can be seen.

Firstly, the most important file you must know is the /boot/config.txt

This contains many configuration, that require the file to be save and RPi to be restarted. 

If your display is working, but the resolution is wrong, then access the start menu And look for Configuration, or directly access the resolution setting, type 

raspi-config

When the display is a touch screen, it would be a good idea to install a virtual keyboard. Do this by

sudo apt install florence

Back to troubleshooting a display that's not showing. Always check your display is powered, and that system messages appear at boot before proceeding here.

Step 1.

Ensure RPi is connected to your local network, then SSH to your RPi. On MS Windows there is a software named Putty. 

Step 2. 

Determine your device supported resolutions. Use either of following commands;

tvservice -m CEA

tvservice -m DMT

Depending on whether you are connected to a TV (CEA) or computer monitor (DMT). Generally, a TV with VGA cable will use CEA. 

Step 3.

Edit the file /boot/config.txt

Use the settings identified in Step 2. If you are using CEA then the value of hdmi_group=1,

if its DMT then hdmi_group=2. 

Eg 1. when connected to a monitor with resolution 1280×720 and frequency 60Hz, these are the settings;

hdmi_safe=1

hdmi_group=2

hdmi_mode=85

Eg 2. when connected to a TV through HDMI with resolution 640x480 and frequency 60Hz. Since the TV have a standard HDMI connector, hdmi_safe will be commented out. These are the settings;

#hdmi_safe=1

hdmi_group=2

hdmi_mode=4

Reference: Raspberry site

How to alter Postgresql table owner

 All this while I have been changing table owners individually in a specific schema. One fine day, there was a requirement to transfer all tables to another owner without changing the database ownership.

The existing tables where having owner as "postgres", but to allow other application to access, a separate user is created and allowed access. Here is how its done to assign the user;

\c mydatabase;

ALTER TABLE public.users OWNER TO contractors;

In the case where ALL tables are to have a new owner, then following can be done

select 'ALTER TABLE ' || table_name || ' OWNER TO contractors;' from information_schema.tables where table_schema = 'public' \gexec ;

Select Query with JSON column in Postgresql

 Recently I encountered a problem to use JSONB type column with Reportserver and log it in their forum. Since I have made the example, why not post it here? 

Here is an example to use JSONB type column in Postgresql 10 and newer.

Step 1: Create the sample database

CREATE TABLE public.products (
    id int4 NOT NULL PRIMARY KEY,
    "name" varchar NOT NULL,
    "product" jsonb NULL
);

Step 2: Add sample data

id|name       |product                                                               |
--|-----------|----------------------------------------------------------------------|
1|Orange bod |[{"id": 18, "name": "Orange Gala", "value": "1Bll-1-99-aaa"}]         |
2|Chicken Pie|[{"id": 4, "name": "Downtown Chicken Pie", "value": "1Bll-1-201-aaa"}]|
3|Apple Pie  |[{"id": 5, "name": "Apple Pie", "value": "1All-1-1000-xzp"}]          |

Step 3: Run an SQL query

SELECT name, jsonb_agg(t->'name') AS brand, jsonb_agg(t->'value') AS code 
FROM products, jsonb_array_elements(products.product) t GROUP BY name; 

The results

name       |brand                   |code               |
-----------|------------------------|-------------------|
Apple Pie  |["Apple Pie"]           |["1All-1-1000-xzp"]|
Orange bod |["Orange Gala"]         |["1Bll-1-99-aaa"]  |
Chicken Pie|["Downtown Chicken Pie"]|["1Bll-1-201-aaa"] |

Another method is to include a WHERE clause as shown below;

SELECT name, jsonb_agg(t->'name') AS brand, jsonb_agg(t->'value') AS code 
FROM products, jsonb_array_elements(products.product) t
WHERE t->>'name'='Orange Gala'
GROUP BY name

I hope this simple example will benefit you.