PostgreSQL quick examples

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

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
WHERE m.user_id = 9;


Leave a Reply

Your email address will not be published. Required fields are marked *