I'm creating a statistic reporting tool and facing an issue related to memory due to a high query result (application server not responding due to RAM is full). I can't restrict the query result by limit or where clause due…
I have troubles creating certain aggregations. I'm using this table in Postgres: CREATE TABLE public.customer_courier_chat_messages ( sender_app_type character varying(255), customer_id integer, from_id integer, to_id integer, chat_started_by_message boolean, order_id integer, order_stage character varying(255), courier_id integer, message_sent_time timestamp with time zone );…
I have created scenario in fiddle where I have to remove duplicate records and keep only 1 record example : for spotted table mfg_lz.icc_axbi_kanban_job there are 2 records so need to remove duplicate and keep only 1. select field1 ,unnest(…
I wand to select record from one table and insert into other . Here is my table 1 create table temp ( seq varchar(20), prefix varchar(20) ); insert into temp values ('AAA','A'); // counter should be 1 as combo of…
I'm prepping a legacy PostgreSQL 9.5 database for upgrade. I need to remove OIDs from tables without locking the tables for long periods of time. I have developed a strategy that seems to work, but I would like the experts…
On a server with Postgresql 10 did a dump and put it to rsync.net pg_dump -Z1 -Fc <db_name> | ssh <user>@s<server> "dd of=dump" On the other server with Postgresql 9.5 downgraded created dump and tried to restore database. Steps were…
I have a Postgres addresses table with rows such as: id data 1 {"name": "Scott", "addresses": [{"city": "London", "street": "street 1"}, {"city": "Berlin", "street": "street 2"}]} 2 {"name": "Tiger", "addresses": [{"city": "Tokyo", "street": "street 3"}, {"city": "Zurich", "street": "street 4"}]}…
I have the following function which I want to pass anyelement and it should return whether it is a valid URL by matching the regex: CREATE OR REPLACE FUNCTION isURL(data anyelement) RETURNS BOOLEAN AS $$ BEGIN RETURN data::text ~ '^https?://[-a-zA-Z0-9@:%._+~#=]{2,255}.[a-z]{2,6}(/[-a-zA-Z0-9@:%._+~#=]*)*(?[-a-zA-Z0-9@:%_+.~#()?&//=]*)?$';…
I'm evaluating a query performance and came to a curious finding.... I'm trying to get the count of items created today grouped by a column Pretty simple SQL query: SELECT count(id), total_units_sold FROM public.items x WHERE created_at > current_date GROUP…
In Postgres, is there a need to lock a row in a table using FOR UPDATE if the query is contained in a transaction with serializable isolation level? In other words, does serializable transaction lock the rows by itself until…