Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
11,005
questions
0
votes
0answers
17 views
Improve PostgresSQL aggregation query performance
I am aggregating data from a Postgres table, the query is taking approx 2 seconds which I want to reduce to less than a second.
Please find below the execution details:
Query
select
a....
1
vote
1answer
33 views
Could once `max_standby_archive_delay` and `max_standby_streaming_delay` with value -1 can cause replication stop on heavy loads?
In my replication servers on a postgresql master-slave replication scheme on aws RDS I get the following error:
SQLSTATE[40001]: Serialization failure: 7 ERROR: canceling statement due to conflict ...
0
votes
1answer
20 views
paginating over large number of postgresql rows in a sorted order [closed]
The goal is to retrieve many millions of rows ordered by their timestamp. Since there are so many rows, I need to retrieve them in chunks. I've read about paginating over results, but all the ...
2
votes
2answers
106 views
Postgresql return results of dynamic SQL
I'm using Postgresql, version 12.
My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that ...
1
vote
1answer
1k views
When exactly does pg_stat_all_tables.last_autovacuum get updated?
I'm looking into issues with our autovacuum settings and trying to understand last_autoanalyze and last_autovacuum from the pg_stat_all_tables.
I understand autovacuum to work in an incremental way, ...
1
vote
1answer
152 views
structuring media uploads (users, images, videos) using sequelize.js
(using pseudocode to simplify)
say I have a User (users table)
User {
id: PK
}
and I want to have them upload images and videos (media). I want to be able to differentiate between images or ...
0
votes
1answer
50 views
Doubt about Database design (newbie)
I am learning about Database design, I have doubt about some database design conepts
I want to know which is better design between two design
Design 1
there is two tables:
Object and Field
...
5
votes
1answer
3k views
postgres backup for specific table with blob oid
I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 ...
1
vote
1answer
443 views
Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column
I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}).
There're ...
1
vote
2answers
43 views
PostgreSQL remove numbers before first comma
I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers in an array e.g.
{100000,125000,150000,175000,200000}
I want to remove the ...
0
votes
1answer
963 views
Install Only Pgbench and Not PostgreSQL Server
I'm looking to benchmark some custom PostgreSQL statements using pgbench but my problem with this is that upon trying to install pgbench it tries to install PostgreSQL server. The pgbench utility ...
1
vote
1answer
439 views
RDS Postgres - pg_stat_statements is missing queryid column
I upgraded my RDS postgres instances from 9.3 -> 9.4 -> 9.5 several months ago. When installing the pganalyzer-collector tool, I got a funny error that the "queryid" column does not exist in the ...
0
votes
0answers
17 views
Insert multiple values with associations from json
Say we had a schema like this:
create table person (
id serial primary key,
first_name text
);
create table vehicle (
id serial primary key,
name text not null,
person_id int unique not ...
0
votes
0answers
24 views
trigger updating same table
I have an inventory_movements table, and every time a sale is done, a new row is saved through a trigger (called "regularizestockmov").
If you delete or update a row that is not the last row, it has ...
1
vote
1answer
32 views
Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?
I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index
CREATE INDEX tab_x_y ON tab(x) ...
0
votes
1answer
20 views
List foreign keys dependencies that refer to a given table
RDBMS: PostgreSQL 9.6
I desire to list the foreign keys for a given table and schema. I am about 90 % there but I also desire to know what options were used to make the fkey. Example on delete ...
0
votes
0answers
12 views
Best practices for keeping protobuf spec in sync with relational database (e.g. PostgreSQL)
1. We have a schema definition in Google's Protocol Buffers format that we use to generate code for different gRPC implementations (Python, PHP)
2. We have a database (PostreSQL) that is initialized ...
2
votes
1answer
92 views
How to import data from a dump file to a specific schema in postgres?
Let us assume there's a database name myDataBase. There are four schema in this database named
1: schema_1
2: schema_2
3: schema_3
4: schema_4
I have created a dump of schema_1 from this command ...
3
votes
2answers
1k views
PostgreSQL - Enforcing unique constraint on date column parts
I'm trying to enforce a unique month and year combination on a table with a data column. e.g.
create table foo
(
mydate date
);
I want to enforce that only one row per month and year is valid i....
-1
votes
0answers
27 views
Simple inventory management db design - polymorphic association?
I’m designing a web application for internal use to inventory-manage returnable/reusable widgets (a physical product). I anticipate around a 100 or so ‘stock/widget transactions’ per day.
In simple ...
0
votes
1answer
748 views
“undefined function pg_pconnect()” when connecting to Postgres from CodeIgniter
I just installed Postgresql and pgadmin in my Ubuntu system. I can create a database from the pgadmin interface and everything seems ok, but when I try to connect to the database from my CodeIgniter ...
1
vote
2answers
22 views
Does PostgreSQL btree_gin extension uses btree or gin data structure?
In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index.
However I don't ...
1
vote
1answer
150 views
PostgreSQL - Replica Recovery Process Stuck frequently (epoll_wait taking too much time)
We have Master Slave Setup with Asynchronous Streaming Replication (Replication Slots). Frequently our Replica's entire Postgres processes seems to be doing nothing (or stuck?). Since ...
1
vote
3answers
30 views
PostgreSQL (big data): search by tags and order by timestamp
We need to add a search feature on a large table (200M+ rows):
item_id | tags | created_at | ...
-------------------------------------------------------------------
...
0
votes
1answer
29 views
Returning values for Stored procedures in PostgreSQL
I was reading PostgreSQL Tutorials there I found In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned ...
1
vote
1answer
28 views
Slow bitmap heap scan on group/count query in a large table
I run an app with a database of PostgreSQL 10.10
I am optimizing a bunch of queries in our application, with great results so far, but there is one specific query that takes about 1 minute if not ...
1
vote
1answer
15 views
Can I improve the way pg_dump exports inherited tables?
Given a postgresql 12 database with some table inheritance, and the child table adds no additional columns:
CREATE TABLE parent (
id integer NOT NULL PRIMARY KEY,
name text NOT NULL
);
...
0
votes
1answer
22 views
Using pg_dump to create 144 backups per day
Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is).
Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In ...
1
vote
1answer
18 views
Is SELECT FOR … UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?
Consider the following when the transaction isolation level is Read Committed (the default):
UPDATE example
SET foo = 'bar'
WHERE ...
and
DELETE FROM example
WHERE ...
Neither DELETE nor UPDATE ...
1
vote
1answer
593 views
PostgreSQL WAL file database system identifier wrong
I'm having a problem restoring a copy of a PostgreSQL database to another server.
I have taken a backup using the pg_basebackup tool and can restore that but when I restore WAL logs from the archive ...
0
votes
1answer
42 views
Cannot connect to Postgres DB
I'm new to Postgres and I am trying to implement it through the command line. I just used it previously in a project, but that was by downloading Postgres.app and Postico. I wanted to go through the ...
0
votes
2answers
59 views
Avoiding deadlocks in PostgreSQL with multiple clients
I am developing software which launches multiple, concurrent clients to connect to a PostgreSQL (12) database. When each client starts, the first thing it does upon connecting to PostgreSQL is to run ...
0
votes
0answers
25 views
Extract Image URLs from html text using Postgres sql Query
I have a column in postgres db in which html contain stored. Like this
<p style="text-align: justify;"><strong><img alt="" src="https://images.unsplash.com/photo-1579121477063-...
-1
votes
2answers
14 views
How to transfer the incremental MS Access table data to PostrgreSql Tables [closed]
I have an attendance punching data stored in MS Access database. It's increasing day by day basis. Can we convert the MS Access table data to the PostgreSql table on the incremental bases.
What is ...
2
votes
1answer
448 views
aggregating json fields
I have a table that holds JSON data. In a query with a GROUP BY clause, I'd like to get an array of all of the JSON field names in the result set.
I tried a query like this:
SELECT array_agg(...
0
votes
1answer
473 views
How to import json table into PostgreSQL table?
I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data:
[{
"token": "a978f9793a594d11860e9cf7962996d7",
"name": "vehicle.moving",
"description": "...
0
votes
2answers
1k views
Postgres Windows post-install “The database cluster initialization failed”
I've seen many solutions and have been working on this issue for hours now.
After installation the post-installation script fails with the message above.
This is a development installation of ...
-1
votes
0answers
17 views
Postgres: handy way/function to compute bearing change from coordinates
I am going to compute point-based acceleration and bearing relative to true/magnetic north from the sequence of GPS points with timestamp and instantaneous speed.
Here's how the dataset looks like:
...
1
vote
2answers
44 views
Determining Whether Query Load is Too High for PostgreSQL Server
I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second.
...
1
vote
2answers
40 views
Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)
I have the following table and indices defined:
CREATE TABLE IF NOT EXISTS data (
id serial,
job_id bigint NOT NULL,
payload jsonb NOT NULL,
tags jsonb NULL
);
CREATE UNIQUE INDEX IF ...
0
votes
1answer
34 views
Most efficient way to query a date range in Postgresql
I have a table with a timestamp with tz column. I want to perform a count / group by query for rows within a certain date range:
select count(1), url
from app_pageview
where viewed_at between '2019-...
2
votes
2answers
22 views
Calculating tsrange based on parent/child created dates
Given the table:
CREATE TABLE node (
id serial PRIMARY KEY,
created timestamp without time zone NOT null,
path ltree NOT null UNIQUE
);
And the records:
INSERT INTO node (id, path, ...
0
votes
1answer
16 views
Fail to copy all PostgreSQL databases between servers although there is no error message
I want to copy all databases from PostgreSQL 9.3 on Server-1 (Windows 7) to an empty PostgreSQL 12.0 on Server-2 (Windows Server 2012 R2). The steps are as follows:
On Server-1:
C:\Program Files\...
0
votes
1answer
29 views
PostgreSQL Export to CSV. Each element of array in new line [closed]
I have next request
\copy (
SELECT users.name, ARRAY_AGG(emails.email), ARRAY_AGG(addresses.address)
FROM users
LEFT JOIN emails USING(id)
LEFT JOIN addresses USING(id)
GROUP BY users....
14
votes
1answer
10k views
Querying non-ASCII rows from Postgres
Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it.
I have a UTF-8 database, where collation and c_type are en_US.UTF-8, ...
12
votes
2answers
6k views
0
votes
2answers
46 views
Add timestamp to field automatically in PostgreSQL
I created a small database in PostgreSQL to manage the data for my thesis. I just started with Postgres, so this is probably a newbie question. I added a column in several tables, that is supposed to ...
0
votes
1answer
20 views
Table Partitioning Using Inheritance and Triggers
I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I ...
0
votes
1answer
429 views
Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database
I am not a developer or PostgreSQL DB admin, so this may be basic questions.
Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13
I'm using a python script to ingest external data, ...
-2
votes
0answers
7 views
Why PostgreSQL lose connections after checkpointing start on Flink? I'm using a RichSinkFunction [closed]
19:59:21,934 INFO org.apache.flink.runtime.taskmanager.Task - Flat Map -> GeoIsp Freq count -> (Sink: Freq Isp User Sink, Sink: Freq Isp Device Sink) (4/4) (...
