postgresql 9.5 - pgAgent to trigger action based on current timestamp

Hi I started to explore pgAgent recently. Installation and testing had been done. However, there is one thing that bothers me about pgAgent. the thing will only run either SQL or batch code. Suppose, in my database table contains different week number data. for maintenance purpose, the table should store only past 2 weeks data. Would pgAgent capable to do the automation of this task. the workflow will be:1)Get System timestamp2)Calculate week Number3)delete * from table where week= week - 3if this can be done, Would it be on the batch or SQL ki...Read more

postgresql 9.5 - postgres 9.5 create function plpthon3u resets connections to server

I have installed postgresql 9.5 on windows 10, x64.I have created the extension plpython3u with python 3.3.5 on the server's path and it appeared to create the extension successfully:SELECT * FROM pg_available_extensionsWHERE name like '%python%' order by name; name | default_version | installed_version | comment-------------------+-----------------+-------------------+------------------------------------------- hstore_plpython2u | 1.0 | | transform between hstore and plpython2u hstore...Read more

postgresql 9.5 - Operator does not exist: text + integer [Postgres 9.5]

i have an issue with this piece of code coming from SQL :UPDATE resultats_du_jourSET Heure_debut = CONCAT(SUBSTRING(Heure_debut,1,2) + 12,SUBSTRING(Heure_debut,3,3))WHERE Heure_debut LIKE '%PM';This gives me the following output : sql:53: ER ROR: operator does not exist: text + integer LINE 1: ...ET Heure_debut = CONCAT(SUBSTRING(Heure_debut,1,2)+12,SUBSTR... I understand that you cannot add text+integer, but how can i proceed to do that?Many thanks....Read more

postgresql 9.5 - How to sort the text field that contains alpha numeric characters separated by period delimiter?

I have a requirement to sort the field in natural sort fashion but unable to achieve this. Is there a way to achieve this?This is for PostgreSQL code remediation and tried various code snippets but none of them returns the rows as expected-- PostgresSQL 9.5 codeSELECT nameFROM ( SELECT lower('1.1.1.a.1') as name union all SELECT lower('1.1.a.1') as name union all SELECT lower('1.1.1.a.11') as name) foldersORDER BYCOALESCE(SUBSTRING(name FROM '^(\\d+)')::INTEGER, 99999999),SUBSTRING(name FROM '^\\d* *(.*?)( \\d+)?$'),COALESCE(SUBSTRING(name F...Read more

postgresql 9.5 - How to determine UPSERT operation with INSERT .. ON CONFLICT?

I would like to be able to tell which operation (INSERT or UPDATE) took place after execution of an INSERT .. ON CONFLICT. Perhaps as a subquery as part of the RETURNING clause?It seems there was a similar request about a month ago and Peter Geoghegan responded that it might be possible with a hack of somekind. If anyone has any idea, I would love to hear it right about now....Read more

PostgreSQL : cannot insert into table due to permissions

I am using PostgreSQL 9.5.I have a database with several schemas and I want a user to be able to do select, update, insert, delete on all the tables inside one of these schemas.I have used to following PSQL code to create the role :DROP ROLE IF EXISTS myrole ;CREATE ROLE myrole WITH LOGIN PASSWORD 'myrole' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION ;GRANT a_user_group TO myrole;ALTER ROLE myrole VALID UNTIL 'infinity' ; \connect mydatabase ;GRANT ALL PRIVILEGES ON SCHEMA myschema TO myrole;GRANT ALL PRIVILEGES ON AL...Read more

postgresql 9.5 - How to insert the current_timestamp for newly inserted rows

I need to insert the data with timestamp daily three times. I need to insert current_timestamp in null values in the time columnupdate tablename set time = CURRENT_TIMESTAMP WHERE time = null name | marks | time ---------+-------+---------------------------- GANESH | 80 | 2019-01-06 05:03:24.171124 GANESH | 90 | 2019-01-06 05:03:24.171124 GANESH | 100 | 2019-01-06 05:03:24.171124 SURESH | 10 | 2019-01-06 05:03:24.171124 SURESH | 20 | 2019-01-06 05:03:24.171124 SURESH | 30 | 2019-01-06 05:03:24.171124 RAKESH...Read more

postgresql 9.5 - How to retrieve day from single column based on timestamp

I am inserting data daily into the database. I need to retrieve a single column data based on timestamp. I need to display today Usage and yesterday Usage based on the timestamp.My actual table name usage timestampprod 200 2019-01-08 09:22:53.364366Test 100 2019-01-08 09:22:53.364366qality 50 2019-01-08 09:22:53.364366prod 270 2019-01-09 08:22:53.364366Test 300 2019-01-09 08:22:53.364366qality 90 2019-01-09 08:22:53.364366Expecting output:name usage(yesterday)...Read more