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
1answer
38 views

What happens if transaction rollback fails?

So failure while committing leads to rollback. But what if rollback also fails due to some reasons and the database has changed due to partial commitment. How is the database restored to its original ...
0
votes
0answers
23 views

PostgreSQL Streaming Replication with Switchover

I'm trying to provide streaming replication between a master (T1) and slave (T2) and swapping their roles when necessary (i.e letting T1 be a slave to T2). So far I am able to get this working if I'm ...
1
vote
1answer
43 views

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view: resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), ...
0
votes
1answer
41 views

Is it possible to set timezone for a schema - Postgresql

Is it possible to set the timezone for one schema? Do not want to set Postgresql(v.12) server level. Please suggest if any ways to this.
0
votes
1answer
24 views

How to import values of a JSON to PostgreSQL?

I have a JSON file gugu.json that looks like this: { "key1": { "value1": "normal", "value2": [ "PogU" ] }, "key2": ... } I want ...
0
votes
2answers
22 views

How to get the type of geometry column in postgres?

I need to determine the type of geometry column and doing it with the following query: SELECT type FROM geometry_columns WHERE f_table_schema = 'public' AND f_table_name = 'table_name' ...
0
votes
1answer
21 views

Create sequence starting with maximum id from a table [duplicate]

I'm trying to create a sequence in the following way: create sequence some_seq start with (select max(id) + 1 from some_table); But I'm getting the following error: ERROR: syntax error at or near "(...
0
votes
3answers
40 views

update one column based on query containing another (joined) table

Consider the following select query: SELECT s.id, s.event, s.column, e.oldcolumn FROM section as s INNER JOIN event as e ON s.event = e.id WHERE s.id = 1 Now I wish to update the section table so ...
0
votes
0answers
29 views

Is it possible to use the log of tracking changes to revert a table to a previous state in PostgreSQL?

This thread (Track all modifications to a PostgreSQL table) discussed how to generate a log to track changes in PostgreSQL. How to use the log to revert a table to its older version programmatically?
0
votes
1answer
20 views

Postgresql 4 table joins

I have one doubt in posgresql joins and filters. Please find below sql code snippets. 1) select count(*) FROM lfa1 a LEFT JOIN lfb1 b ON a.lifnr = b.lifnr and a.hvr_is_deleted = 0 and b....
0
votes
0answers
17 views

Removing postgres connection installed with EnterpriseDB on MacOS

I had earlier installed Postgres using Enterprise DB which is installed by default at location: /Library/PostgreSQL/10 However, because of some database issues I had to uninstall it. I followed all ...
1
vote
1answer
26 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
0
votes
1answer
42 views

How to import multiple CSV files to a postgres table using pgadmin or other method?

I'm looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs? COPY schema.table ...
0
votes
1answer
22 views

Alter default privileges in postgres for other roles

Let's say I have a role called user1, user2 and postgres. Is it possible to alter the default privileges while logged in as postgres for objects created by user1 so that user2 can see them? This is ...
0
votes
1answer
34 views

Feasible to have thousands of foreign data wrappers in Postgres?

We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/...
1
vote
1answer
53 views

How to filter multiple many to many relationship based on multiple tags?

I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like: Problem table: ----+---------------------------...
0
votes
1answer
20 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
0
votes
0answers
21 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
17 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 ...
0
votes
0answers
25 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 ...
1
vote
1answer
32 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
17 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
44 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, ...
2
votes
1answer
39 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
35 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
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
28 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
25 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
28 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
21 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
46 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
vote
3answers
38 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
155 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
41 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
25 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
21 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
45 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
16 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
8 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
31 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":...

1
5 6
7
8 9
239