Using Common Table Expressions CTE or With Clause in SQL
https://www.postgresql.org/docs/current/queries-with.html
https://www.postgresql.org/docs/current/queries-with.html
Run the two commands below and you will see what I mean: select now() AT TIME ZONE current_setting(‘timezone’); 2023-09-30 09:07:31.251 select now(); 2023-09-30 09:17:51.338 -0400 select current_timestamp; select current_timestamp AT TIME ZONE current_setting(‘timezone’); select localtimestamp; So if you have a pl/pgsql procedure that accepts a TIMESTAMP data type, you can’t pass in just “now()” as …
when dropping, recreating, granting access to a pl/sql procedure the entire call specification (including parameters or parameter data types) is required to insure you are affecting the appropriate procedure – this is also referred to as footprint, and or procedure specification or signature.
Just want to ignore all the permission denied and find the file? sudo cd / sudo find . -name connect-console-sink.properties 2>/dev/null The “2>/dev/null” just writes all error messages to the bitbucket/null device
select a.oid as user_role_id , a.rolname as user_role_name , b.roleid as other_role_id , c.rolname as other_role_name from pg_roles a inner join pg_auth_members b on a.oid=b.member inner join pg_roles c on b.roleid=c.oid where a.rolname = ‘postgres’
https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-to-postgresql/ in other words how to write a error log to a table and separately commit that record
Here is the entire list for native on-prem PostgreSQL: https://www.postgresql.org/docs/current/monitoring.html And here are my favorites from that list. PG_STAT_ACTIVITY – shows background activity for a running connection – not service by a connection pooler https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW PG_STAT_REPLICATION – the publication side view shows status of a publications subscribers – lag to them – must be queried …
SELECT pg_size_pretty (pg_relation_size (‘table_1’)) ;
The Best of Series momjian.us/presentations The presentation above addresses requirements open source (mostly free solutions) for PostgreSQL functionality to: – High Availability – No application reconfigure after fail-over/switch – RepMgr (an open source tool for managing PostgreSQL replication and failover) – RPO (recovery point objective / maximum amount of committed data lost) = 0 after …
PostgreSQL optimization – the best of YouTube series Read More »
On-prem? PgBouncer AWS RDS Postgres or AWS RDS Aurora/PostgreSQL? AWS RDS Proxy