PostgresSQL database size sensor

Hi all!

I tried adding the database size sensor per the SQL component instructions:

sensor:
 - platform: sql
   db_url: postgresql://user:password@host/hass
   queries:
     - name: DB size
       query: "SELECT (pg_database_size('hass')/1024/1024) as db_size;"
       column: "db_size"
       unit_of_measurement: MB

But I am getting this error:

Error executing query SELECT (pg_database_size(‘hass’)/1024/1024) as db_size LIMIT 1;: (sqlite3.OperationalError) no such function: pg_database_size [SQL: “SELECT (pg_database_size(‘hass’)/1024/1024) as db_size LIMIT 1;”] (Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation

Does anyone have this working?
Was something changed in the SQL sensor and the documentation needs updated?
Thanks!

2 Likes

I copy past your code and changed only the url - it works.
db_url: postgresql://MYLOGIN:[email protected]:5432/homeassistant?client_encoding=utf8

I will post an answer 3 years after the question because this is the first link from Google *

1 Like

I want to achive the same thing. But in my case nothing happens. There is no log entry nor is there a sensor created.

Edit: I was able to achive this by creating a Node-Red flow. Here it is:

[{"id":"504e3da292d2f4ae","type":"postgresql","z":"bf6a2db4.23112","name":"","query":"SELECT (pg_database_size('homeassistant')) as db_size;","postgreSQLConfig":"08b008a3ecfea0a1","split":false,"rowsPerMsg":1,"outputs":1,"x":390,"y":4720,"wires":[["ef37742e355f320a"]]},{"id":"ef37742e355f320a","type":"function","z":"bf6a2db4.23112","name":"byte in GB umwandeln","func":"// Mal und durch 100 um auf zwei Nachkommastellen zu kommen.\nmsg.payload = Math.round(msg.payload[0].db_size/1024/1024/1024*100)/100;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":600,"y":4720,"wires":[["77a5154714e83388"]]},{"id":"ebd8e2bd90bf8093","type":"inject","z":"bf6a2db4.23112","name":"On connect & alle 60s","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":true,"onceDelay":"5","topic":"output on connect","payload":"true","payloadType":"str","x":170,"y":4720,"wires":[["504e3da292d2f4ae"]]},{"id":"77a5154714e83388","type":"ha-entity","z":"bf6a2db4.23112","name":"haos_database_size","server":"be104a93.6e2ed8","version":1,"debugenabled":false,"outputs":1,"entityType":"sensor","config":[{"property":"name","value":"haos database size"},{"property":"device_class","value":""},{"property":"icon","value":""},{"property":"unit_of_measurement","value":"GB"}],"state":"payload","stateType":"msg","attributes":[],"resend":true,"outputLocation":"payload","outputLocationType":"none","inputOverride":"allow","x":840,"y":4720,"wires":[[]]},{"id":"08b008a3ecfea0a1","type":"postgreSQLConfig","name":"","host":"77b2833f-timescaledb","hostFieldType":"str","port":"5432","portFieldType":"num","database":"homeassistant","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","applicationName":"","applicationNameType":"str","max":"10","maxFieldType":"num","idle":"1000","idleFieldType":"num","connectionTimeout":"10000","connectionTimeoutFieldType":"num","user":"XXXXX","userFieldType":"str","password":"XXXX","passwordFieldType":"str"},{"id":"be104a93.6e2ed8","type":"server","name":"Home Assistant","legacy":false,"addon":true,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":false,"cacheJson":true}]