PostgreSQL quick examples – Karantan's blog

My cheat sheet for postgresql. Also pgcli is a really nice tool to use. IMO with it you don’t need any “phpMyAdmin” tools.

Update query:

UPDATE blogs
SET posts=0,
pages=0,
plugins=0,
health_score=0
WHERE server_id IN (185,113,1,3,4);

Select query:

SELECT posts, pages, plugins, health_score
FROM blogs
WHERE server_id IN (185,113,1,3,4);

Check if entity is multiple times in table:

SELECT blog_id, COUNT(*) 
FROM cleanup_notifications 
GROUP BY blog_id 
HAVING COUNT(*) > 1;

Get datatypes for a table:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = '<your_table>';

or \d <table_name> in psql / pgcli command line

Set public_url to ‘http://…’ + secure token from the specific camera for all monitors that belongs to user with ID = 9

UPDATE monitors as m 
SET public_url = 'http://...' || (SELECT secure_token FROM cameras as d WHERE m.domain_id=d.id)
WHERE m.user_id = 9;