Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
0answers
18 views

PostgreSQL limiting number of rows for a certain condition

arena_stat_id | user_id | date_set | level | trophies ---------------+--------------------+----------------------------+-------+---------- 1 | ...
0
votes
0answers
13 views

Function in Postgres that uses the table where I'm passing the function inside the function body?

I've got two tables, one with street_codes and street_numbers (addresses) and another with sidewalks_keys that correspond to each address (address_sidewalks_keys). I would like to create a function in ...
0
votes
0answers
16 views

Why isn't autovaccum running/completing?

My PostgreSQL instance (AWS Aurora) isn't autovacuuming. Or it is, but it is somehow being interrupted or stopped. I'm not canceling any autovacuum queries (though I suppose AWS itself could be). The ...
1
vote
1answer
10 views

Can PostgresSQL cluster have synchronus and asyncronus standbys at the same time for the same dbs?

In this question, Write concern in PostgreSQL Laurenz Albe explained how to control the set of standby servers when data is committed to PostgresSQL, and that gave birth to another question. What if I ...
-1
votes
1answer
9 views

how can I fake/mock the postgres now function to behave like freeze_time

Basically would like to be able to mock out the time functions to return a time I want for unittesting.
0
votes
1answer
26 views

PostgreSQL covering index [closed]

I'm using Postgres 12. Let's say I have a table player with 2 columns: first_name and last_name (along with the RowID) with 3 rows first_name. last_name RowID paul pogba ...
0
votes
0answers
23 views

Postgresql - slow distinct on million records table

I have a table with a million of records, i need to select the distinct values of log_type. In other queries i also need to filter log_types result of the distinct and limit the result. But the query ...
0
votes
0answers
9 views

Polymorphic association in PostgreSQL [closed]

I have three entities video_guide text_guide and discussion that represent type of learning material available on the platform. A student can create topic-wise boards and add learning material to them....
1
vote
1answer
30 views

Speed up PostgreSQL logical decoding plugin

I'm writing a new logical decoding output plugin for PostgreSQL inspired by wal2json. But mine produces JSON by using SPI to repeatedly invoke to_json(...) on column values (instead of wal2json's ...
0
votes
1answer
18 views

Data migration: copy data with insert select and fill unique column

I have some tables in Postgres like this. create table foo ( txt text ); insert into foo (txt) values ('a'); insert into foo (txt) values ('b'); insert into foo (txt) values ('c'); create table ...
0
votes
1answer
7 views

Postgres Server staying in read only mode after a barman recover command is executed

All, I am trying to perform a PITR restore using barman using recover command. The problem is that the server remains in the read-only state once the barman recover is done. Is there a way by which ...
1
vote
0answers
22 views

How to return most recent element with distinct subquery

I have such query but it returns duplicated rows for same speaker SELECT "speakers"."id", "speakers"."name", "speakers"."surname", "speakers"."photo_url" as "photoURL", "talks"."...
2
votes
2answers
40 views

How to determine or predict space on disk per table row?

I'm very new to Postgres so my math could be off here ... This is my table: CREATE TABLE audit ( id BIGSERIAL PRIMARY KEY, content_id VARCHAR (50) NULL, type VARCHAR (100) NOT NULL, ...
1
vote
1answer
32 views

Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the ...
1
vote
2answers
23 views

Show values from list that are NOT returned by query

I have been given a list of values that may or may not be found in a table's column. I just need the subset of the list that is NOT found in a SELECT query. Pretending that the list I was given is: '...
0
votes
0answers
7 views

Library that can generate random geospatial data in GeoJSON

Is there a library that can help to quickly generate random geospatial features in GeoJSON format (preferably, but can be anything), based on the parameters that we pass in. For example - generate ...
1
vote
1answer
28 views

Write concern in PostgreSQL

In MongoDB it is called write concern, that is the number of the servers in a replica set, that the data should be committed before the cluster acknowledges the write request. How is it called in the ...
0
votes
1answer
15 views

Using count on two coumns yields same values in both columns (Postgresql) [closed]

I am looking at agent call data and want to calculate how many agents worked at a certain time bracket and how many calls where done in the same time bracket to get an average call volume per agent ...
0
votes
1answer
19 views

Manually trigger “Write current results to disk” during query

Using PostgreSQL 12, I have the requirement that some data needs to be "refreshed" each night. That means: The database has to do some calculations based on the current date and write the result to ...
0
votes
1answer
27 views

Prevent execution of heavy-loading and / or runaway queries in postgres

Is there a way to stop/kill queries being executed on a postgres database based on criteria such as: maximum execution time load (CPU and/or memory) imposed on the server?
0
votes
1answer
22 views

Migration path to unify column names in backward compatible way

We have several naming schema for columns (like id/key or created/creation_date/create_date) and want to unify it. Also we want to do it in backward compatible way. We want to be able to run old code ...
0
votes
0answers
25 views

How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?

We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place ...
0
votes
1answer
18 views

Is there an efficient way to use 3rd party packages in PL/Python?

I've found examples of people importing 3rd party packages into PL/Python scripts, but it seems to me that re-importing libraries upon every run of a stored procedure is terribly inefficient. Does ...
0
votes
1answer
24 views

Postgres foreign data wrappers weird join behavior

So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins. Keep in mind the following snippets are ...
0
votes
2answers
43 views

Want to delete then insert with single statement using a CTE in Postgres

I want to delete then insert with single statement using a CTE in Postgres. We could use an explicit transaction and 2 statements, but want to avoid that if possible. I don't know if there will be ...
-1
votes
0answers
11 views

How to connect Jira to remote PostgreSQL database? [closed]

I have installed Jira on my Windows 10 using local PostgreSQL, but now I have installed PostgreSQL on a VPS and want to connect my Jira to it. But I don't know how to change following configuration ...
1
vote
3answers
37 views

Pre-filtering on right table in a LEFT JOIN

I have the following schema/query where I basically need to filter parent table by some criteria and then collect aggregated data from its child table(s): create table parent (id integer primary key, ...
0
votes
0answers
32 views

“Failed to load SQL modules into the database cluster” when installing PostgreSQL 12.3 in a drive other than C

When I try to install PostgreSQL 12.3 in a drive other than C, I get the following error message: "Failed to load SQL modules into the database cluster" I have previously raised a related issue (...
0
votes
1answer
30 views

Insert a row into the table omitting all values?

Suppose I have a table with a serial as primary key and couple of other columns, that are all nullable. Is there any way to insert a new row in that table by omitting all the columns in the insert ...
0
votes
2answers
38 views

Explaining recurring crash in postgresql deployment

I'm having trouble with unexpected system crashes emanating from a postgresql deployment on a moderately busy production server (Django 1.8 app deployed on DO droplet with a gunicorn application ...
0
votes
0answers
22 views

How to find the column value with the biggest storage size in the DB?

Is there any way to find out which column value has the largest storage out of all tables in PostgreSQL DB? I'm looking for currently used storage, information_schema.column only shows the length of ...
0
votes
2answers
37 views

How to lock entire Postgres table but allow reads

Sorry for the noob question, but what is the correct table level lock mode to allow reading from a table but not writing to it? The scenario that I have is that I want to prevent two tables from ...
0
votes
0answers
20 views

Unable to connect to postgresql EC2 instance from VPN

I have an EC2 instance of timescaledb running on AWS. For security purposes, my team members and I are using OpenVPN so that we have access to it and the same IP address has been defined in the ...
2
votes
1answer
44 views

Best indexing strategy for spatial join and ranged lookup

Context We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing): Incidents_2 (Columns of interest are a geom, and a reported_at (int8)) ...
0
votes
0answers
14 views

postgres install from homebrew not creating `postgres` user

So, I'm trying to fix my local postgreSQL install to save log4j2 entries. Because the former has been bungled, I decided to reinstall. I ran the following to uninstall: brew uninstall postgresql rm -...
0
votes
1answer
7 views

SQL optional where clauses without exponentially large number of only slightly different queries

I'm working with postgres in python and I want to write a clear query that optionally allows WHERE clause subsetting, but doesn't require me writing an exponential explosion of whether the subsetting ...
0
votes
1answer
24 views

How to pass a newly INSERTed row to a function?

I have following tables: table1: table2: | uid | did | | uid | did | |-----+-----| |-----+-----| | 1 | 2 | | '1' | 2 | | 4 | 2 | | '4' | 2 | | 2 | 3 | | '2' | 3 | | 1 | 8 ...
0
votes
1answer
28 views

Better jsonb schema for querying in postgres

I have a JSON with the following structure: uid | item 1 |[{"id":1, "m":"123", "s":1},{"id":2, "msg":"on","s":2},{"id":3, "msg":"of","s":1}] 2 |[{"id":1, "m":"yes", "s":2},{"id":3, "msg":"gh","s":...
0
votes
1answer
9 views

Kill concurrent materialized view refresh PID

Concurrent refreshes of materialized views don't have a record in pg_stat_activity, other than the initial (~immediately completing) query to start them. Is there some other way to obtain the PID, in ...
1
vote
1answer
28 views

Unable to convert a table to hypertable due to unique index error

I am creating a table using Flask SQL Alchemy as follows: class Price(db.Model): __tablename__ = "prices" id = db.Column(db.Integer, primary_key=True) country_code = db.Column(db.String(2)...
0
votes
0answers
14 views

PostgreSQL: serial is set to 1, despite of some rows already being inserted in the table

I was getting duplicate primary key error while inserting a row in a table, despite of the primary key being a serial and me not passing any predefined value for it. So I went down to see what was ...
0
votes
0answers
16 views

How to change array_agg() to return empty array on empty input in PostgreSQL?

I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried ...
0
votes
0answers
13 views

Postgres ltree has-a-child query

I have the following tables: CREATE TABLE public.permissions ( "user" varchar , "action" permissions_action_enum, -- 'read'/'write' id ltree ); CREATE TABLE public.items ( "path" ...
0
votes
1answer
29 views

Best way to model a string matrix (postgreSQL)

My goal is to make this as convenient as possible (i.e. least external tables lookups in application) and efficient (least redundant indexes) I need to model data that, without any optimization, ...
0
votes
1answer
32 views

problem with optimizing SQL query

I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query. Here are the relations: CREATE ...
1
vote
2answers
32 views

postgres includes listed column in sort key and thus fails to use index

This is a query that occurs reasonably frequently: SELECT DISTINCT id, published_year, first_author FROM publication ORDER BY published_year DESC NULLS LAST LIMIT 10; There is an index that could ...
0
votes
0answers
13 views

PostgreSQL - problem with INSERT trigger enabled when performing COPY FROM STDIN bulk insert (hangs/locks)

So I work with a database that regularly imports a significantly amount of records- on the orders of millions. To do this the most efficient way possible, I use the PostgreSQL COPY FROM method, which ...
0
votes
1answer
27 views

Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already

I'm having a bit of trouble wrapping my head around a way to make this JOIN work. Postgres 11.5 I have two tables: Table1: customer_id date (various other fields) Table2: customer_id date (...
0
votes
1answer
21 views

possible to specify field enclosure in psql command line?

In psql if I have a field separator of ",", but there are commas in some fields, is it possible to specify a field enclosure character? I have looked through the documentation but I can't find it. ...
0
votes
0answers
21 views

Using a full text index with PostgreSQL and Doctrine

I received the following error: SQLSTATE[54000]: Program limit exceeded: 7 ERROR: index row size 2728 exceeds btree version 4 maximum 2704 for index "fulltext" DETAIL: Index row references tuple (1,...