Update One column with result from another

Problem

Needed to update one column with the results of another column.

Solution

Create a derived table to get the results that you want and then join the id of the result with that of the table as shown:

update payments
set total = amounts.grandtotal
from
(
select co.orderid, co.grandtotal
from orders co
    inner join customer cu on co.customer_id = cu.customer_id
where co.status = 1 and co.amt > 0
) as amounts
where payments.orderid=amounts.orderid;

Reference

updating table rows in postgres using subquery

Create readonly user postgres

Problem

I have a tool that only needs to connect to a postgres database to read data.

Solution

  • Connect to the database using the postgres super user

imela@whiscardz:~$ sudo -i -u postgres
postgres@whiscardz:~$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

postgres=#

  • Then create a user

postgres=# CREATE USER newuser WITH PASSWORD 'newuserpassword';
CREATE ROLE

  • Now connect using the databaseName user:

postgres@whiscardz:~$ psql -d databaseName -U postgres
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

databaseName=#

  • Now assign privileges to the user. This enables a user to login and then to select tables:

databaseName=# GRANT CONNECT ON DATABASE databaseName TO newuser;
GRANT
databaseName=# GRANT USAGE ON SCHEMA schemaName TO newuser;
GRANT
databaseName=# GRANT SELECT ON ALL TABLES IN SCHEMA schemaName TO newuser;
GRANT
databaseName=#

  • Now connect using the credentials from anothoer machine.

imela@whiscardz2:~$ psql newuser -h 192.168.2.2 -d databaseName
Password for user newuser:
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

databaseName=>

  • Now you can run your select query and see if you can be able to run a query:

databaseName=> select * from schemaName.system;

NB:
If you don’t include the schema that is if you select from just the table name then you will get the following error:

databaseName=> select * from system;
ERROR: relation "system" does not exist

References

https://w3resource.com/PostgreSQL/connect-to-postgresql-database.php
https://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
https://riptutorial.com/postgresql/example/29300/create-read-only-user
https://stackoverflow.com/questions/2172569/how-do-i-login-and-authenticate-to-postgresql-after-a-fresh-install
https://stackoverflow.com/questions/6452956/how-to-create-a-user-with-readonly-privileges-for-all-databases-in-postgresql

Allow Remote Connections

Problem

Remote connections are needed for troubleshooting and also creating reports or you would probably just like to connection your postgres client to the database.

Solution

  • Check the ports that postgres is listening on:

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1336/postgres

  • Then edit the PostgreSQL configuration file and put the IP that you would like to allow. If you want to allow all IPs, then use 0.0.0.0

$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf

  • Then add the following line to end of the file and then save and quit.

host   all     all     0.0.0.0/0       md5

  • Next Allow TCP/IP socket. Open the config file below and change listen directive from localhost to * for allow all.

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

  • Search for the line, listen_addresses. Un-comment the line and then change localhost to * (you can use comma-separated list of addresses; * is all ip address)

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

listen_addresses = '*'          # what IP address(es) to listen on;

  • Restart postgres.

vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 1336 0.0 0.7 293436 24224 ? S Apr24 0:03 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 1359 0.0 1.1 293568 35892 ? Ss Apr24 0:01 postgres: checkpointer process
postgres 1360 0.0 0.1 293436 5596 ? Ss Apr24 0:01 postgres: writer process
postgres 1361 0.0 0.2 293436 8756 ? Ss Apr24 0:01 postgres: wal writer process
postgres 1362 0.0 0.2 293872 6532 ? Ss Apr24 0:01 postgres: autovacuum launcher process
postgres 1363 0.0 0.1 148948 4132 ? Ss Apr24 0:02 postgres: stats collector process
vagrant@ubuntu-xenial:~$ sudo service postgresql stop
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
vagrant 18972 0.0 0.0 12944 936 pts/0 S+ 11:30 0:00 grep --color=auto postgres
vagrant@ubuntu-xenial:~$ sudo service postgresql start
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 19010 0.2 0.7 293436 24344 ? S 11:30 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 19012 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: checkpointer process
postgres 19013 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: writer process
postgres 19014 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: wal writer process
postgres 19015 0.0 0.2 293876 6740 ? Ss 11:30 0:00 postgres: autovacuum launcher process
postgres 19016 0.0 0.1 148948 4100 ? Ss 11:30 0:00 postgres: stats collector process
vagrant 19032 0.0 0.0 12944 1020 pts/0 S+ 11:30 0:00 grep --color=auto postgres

 

  • Then check if postgres is listening to all All connections

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 19010/postgres
tcp6 0 0 :::443 :::* LISTEN 1350/apache2
tcp6 0 0 :::80 :::* LISTEN 1350/apache2
tcp6 0 0 :::8083 :::* LISTEN 1350/apache2
tcp6 0 0 :::22 :::* LISTEN 1104/sshd
tcp6 0 0 :::5432 :::* LISTEN 19010/postgres
udp 0 0 0.0.0.0:68 0.0.0.0:* 856/dhclient

References

How Do I Enable remote access to PostgreSQL database server?

Speed insert or update query running on a large postgres table

Got this tip from the this post:

Problem

When you run a large query (insert/update) on a huge table with several indexes, these indexes can seriously slow the query execution.

Solution

With Postgresql it can be faster to disable the indexes before running the query and reindex all the table afterwards.

You can do it like this :

  1. Disable all table indexes
UPDATE pg_index
SET indisready=false
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Run your query
UPDATE <TABLE_NAME> SET ...;
  1. Re-enable all table indexes
UPDATE pg_index
SET indisready=true
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Re-index table