All Questions
Tagged with postgres-11 or postgresql-11
572 questions
0
votes
1
answer
975
views
how to compare datetime using psycopg2 in python3?
I wish to execute the statement to delete records from a postgresql table older than 45 days in my python script as below:
Consider only the code below:
import psycopg2
from datetime import datetime
...
- 1,619
0
votes
0
answers
105
views
Postgresql - Looping based on timestamp
i am working on a scenario where need to run a particular code block for 5 mins.
I tried various options but no luck till now.
Could you please guide me.
Attaching the half-baked block:
DO
$do$
...
- 172
1
vote
1
answer
5k
views
dropping a partioned table in postgres
I am trying to understand the behavior of Postgres partitions.
I have a partitioned table and I am trying to know what would be the behaviour if I
Drop table
Rename Table
Rename Index
If I do a
DROP ...
- 704
1
vote
1
answer
680
views
First column value from multiple rows in postgres
I am using postgres 11, and have below table
+----------+-------------------+-----------+-----------------------------+
| username | filters | flag | time |
+-----...
- 839
0
votes
0
answers
195
views
Export then Import postgresql database
I'm going to upgrade postgreSQL from the version 9 to 11.
I've exported a database using this command C:\Program Files\PostgreSQL\11\bin\pg_dump.exe --file "C:\\Users\\bnechmi\\DOCUME~1\\db_kd&...
- 47
1
vote
1
answer
1k
views
postgresql improve the query scan/filtering
I have the following table for attributes of different objects
create table attributes(id serial primary key,
object_id int,
attribute_id text,
text_data text,
int_data int,
timestamp_data timestamp,
...
- 343
2
votes
1
answer
5k
views
Promoting a postgres slave to master node
I have two postgresql 11.4 running as master and slave nodes.
I am inserting huge volume of data in master to see it get replicated on the slave.
Trying to simulate the following case,
Insert huge ...
- 1,335
0
votes
1
answer
189
views
How to write Function to copy tables or data from one schema to another schema in PostgreSql
I tried the following function for copying one table records from one schema table to another schema table.
Is it possible to copy data of one table to another table using functions.
If yes can anyone ...
- 15
4
votes
1
answer
2k
views
Benefit to adding an Index for an order by column?
We have a large table (2.8M rows) where we are finding a single row by our device_token column
CREATE TABLE public.rpush_notifications (
id bigint NOT NULL,
device_token character varying,
...
- 2,424
0
votes
1
answer
617
views
How to Write IF ELSE Statements inside RETURN QUERY EXECUTE in PostgreSql
I'm getting syntax error while writing IF ELSE statements inside RETURN QUERY EXECUTE in PostgreSQL 11.
CREATE OR REPLACE FUNCTION tds_master.a_report(
i_entity_id integer,
i_client_id integer,...
- 15
2
votes
1
answer
1k
views
Vacuum stats on read replica
We have a hot_standby replication configured for our postgresDB, master is for read/write and replica is a read only server.
When I try to fetch the dead tuple count on both master and replica (slave) ...
- 585
2
votes
2
answers
5k
views
Transaction commit error - invalid transaction terminations
Can you please guide what is wrong with below code which gives an issue with below error:
ERROR: invalid transaction termination.
CREATE OR REPLACE PROCEDURE "app"."sproc_data_create&...
- 3,409
1
vote
1
answer
2k
views
How to pass schema name dynamically in a function?
I have function called list_customers, taking i_entity_id, i_finyear as input params. The schema name is built from i_finyear, I need to execute the query based on the given schema.
I tried the below ...
- 15
0
votes
0
answers
550
views
How to analyze the long running select query
I have a select query that has a subquery (select),
SELECT
count(*) AS count_1
FROM (SELECT
test.pg_status AS test_pg_status, test.errorcode AS test_errorcode, test.errordetails AS
...
- 585
0
votes
0
answers
250
views
Is there a better Postgres query to fetch top first rows in a group?
I have four tables foo, bar, baz, and qux
foo:
bar:
baz:
qux:
I'm looking for an output like:
I've written this query with minimal joins but I don't know of a way to optimize it.
There's going to ...
- 123
0
votes
0
answers
1k
views
How many log files does postgres retains
I've gone through the postgres documentation and I've enabled the logging using the below configuration settings
log_statement = 'none'
log_min_duration_statement = 500
logging_collector = on
...
- 585
1
vote
1
answer
347
views
Fetch the unique rows from postgres database as per longest unique path in ltree
I am working over postgres in db we have below table where we have PATH column as ltree for storing hierarchy ids.
Table (Location)-
| ID(PK) | User |PATH (parent hierarchy) |
|--------|----------...
- 555
2
votes
1
answer
7k
views
PostgreSQL - How to check the usage of shared_buffers
I am new to PostgreSQL and I would like to understand how can I see the utilization of shared_buffers in my PostgreSQL instance.
Let's assume I have shared_buffers = 8GB in my configuration, is there ...
- 585
0
votes
1
answer
70
views
How to optimize this simple yet slow query
I have a relatively simple table with several columns, two of them are expires_at(Date) and museum_id(BIGINT, FOREIGN). Both indexed, also using a compound index. The table contains around 3 million ...
- 4,519
0
votes
1
answer
938
views
trigger function error in postgres, said that column doesn't exists when it actualy exists
I'm trying to implement full text search and have successfully created a trigger function
CREATE FUNCTION content_vector_generate() RETURNS trigger AS $$
begin
new.content_vector :=
...
- 137
1
vote
0
answers
27
views
PostgreSQL JsonB Update, Delete arbitrary attributes across multiple records
I have a weather-data table that stores hourly records of various sensor readings where the JSONB column holds these readings as a JSON object. There's about 5 million of these records and building ...
- 445
1
vote
1
answer
1k
views
Get all ltree nodes at depth
I have an ltree column containing a tree with a depth of 3. I'm trying to write a query that can select all children at a specific depth (level 1 = get all parents, 2 = get all children, 3 = get all ...
- 310
2
votes
2
answers
809
views
Multiple case in update postgres
I need to update 2 columns in table with same conditions. I know, that each of them would take a lot of time. How can I concatenate 2 updates into 1, which can be faster?
-- first update
update t1
set ...
- 147
0
votes
1
answer
1k
views
Default privileges are not applied after tables are created
SQL for schema
CREATE SCHEMA my_schema AUTHORIZATION as_admin;
GRANT USAGE ON SCHEMA my_schema TO as_user;
GRANT ALL ON SCHEMA my_schema TO as_admin;
GRANT USAGE ON SCHEMA my_schema TO as_admin_read;
...
0
votes
0
answers
87
views
Send notify in another session postgreSQL
I need to terminate queries by
select pg_terminate_backend(pid);
which work in another session.
How can I send notify or message for that user in another session? So I dont want, that user see an ...
- 147