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
1
vote
0answers
16 views

Using AWS RDS Postgres: Create a superuser with access to all tables (also future created ones)

In AWS RDS (A managed Database service), we cannot create real superusers. There is a user RDS calls Master User, which can create new users, databases, tables, etc. However, when another user has ...
0
votes
2answers
22 views

Prevent overwriting when using COPY

As the title said. I found it inconvenient when accidentally rerun a copy (time-consuming query) to '\tmp\foo.csv'. I tried setting set -o noclobber in .bashrc but it did not help. How can I prevent ...
0
votes
1answer
22 views

How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. ...
0
votes
1answer
82 views

Sql, get category tree for values from one table, names and ids

What I need I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category). Example On web there is product with category tree. In table "category" I have ...
0
votes
0answers
19 views

Zabbix on Postgresql - very slow deletion of event

Were using Zabbix for monitoring and I'm having problems understanding why deleting events is so slow. Zabbix: 4.2 (never mind the name of the db - it is 4.2) Postgresql: 10.8 nvps: ~400 hosts: ~600 ...
0
votes
0answers
7 views

Django - Database design improvement

In a simple scenario, where I have many measurements table that describe the measurements that comes from a specific protocol and every measurement is linked to the respective parent device, I was ...
0
votes
1answer
37 views

PostgreSQL update column B with values generated from function that uses values from column A

Pretty new to SQL. I have a table (client_info) in PostgreSQL (v11.1) that includes the following columns: +----------+------------------+ | clientip | clientip_country | +----------+----------------...
1
vote
0answers
48 views

Group by equidistant integer intervals

I am using TimescaleDB (an extension to PostgreSQL) to store altitude data from a GPS sensor. The table looks like: CREATE TABLE IF NOT EXISTS altitude ( time TIMESTAMPTZ NOT ...
0
votes
1answer
40 views

cron command running postgres at high cpu [closed]

I can see that a cron command is running under a postgres user and killing the CPU but I have no idea how it's being executed or how to stop it !
0
votes
1answer
35 views

CREATE TABLE in Postgres locks pg_class forever

I have a PostgreSQL cluster with few databases. In one of the DBs, if I try to create a new table, the command never ends until I kill the the linux proccess. I checked pg_lock and found that pg_class ...
0
votes
1answer
32 views

Indexes for combined time and location data

I have a multi-TB table with geolocation data for a certain item id and timestamp. It is partitioned by time with a partition for every month (~500GB), and clustered (including the partitions) on the ...
0
votes
0answers
16 views

Search for partial and full matches in two tables

I have two tables users and products: 1) users: id | username | ... | ---------------------- 1 | name1cat | ... | 2 | cat | ... | 3 | name4 | ... | 4 | test | ... | 2) ...
1
vote
2answers
30 views

Connection pooling - NodeJS or Database products?

Consider the mysql package. The description covers connection pooling thus, Rather than creating and managing connections one-by-one, this module also provides built-in connection pooling using ...
1
vote
2answers
33 views

What's the problem with PostgreSQL temporary files?

I'm studying PostgreSQL architecture and in almost every book or blog post I read people say that we have to track any increasing of temp_files numbers and find the best work_mem size to avoid new ...
1
vote
1answer
42 views

How to restore a postgres database created via an older version of postgres?

I have a file db.backup, that I would like to restore to PostgreSQL version 11. When I tried to install via Azure Data Studio I got an error: Restore: pg_restore: [archiver] unsupported version (1....
2
votes
0answers
52 views

Postgres Service Not start using via HomeBrew

I have to install homebrew in my macOS then after I tried to install Postgres on my mac using the homebrew. but when I checked my Postgres status is it not running. /usr/local/Cellar/postgresql/11.4/...
-1
votes
2answers
56 views

Importing CSV file into a PostgreSQL table

I am using Python with pandas to import a CSV file into a table in Postgres import pandas as pd import psycopg2 from sqlalchemy import create_engine df = pd.read_csv('products.csv', sep=';', ...
2
votes
1answer
64 views

How to install pgcrypto?

While trying to answer Create integer id columns from existing string columns (integer coding?) it turned out that I needed my own version of postgres to play with. I already had a decent postgres ...
-3
votes
1answer
49 views

Postgres Restore [closed]

I need to restore a single schema from production postgres to test. Test database and schema already exists. The users/roles & sequences are different on prod to test and after schema is ...
1
vote
1answer
91 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
0
votes
2answers
40 views

Create integer id columns from existing string columns (integer coding?)

I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this: | ID_STRING_A | ID_STRING_B | | 'AAAA' | 'BBBB' | | ...
0
votes
1answer
23 views

How to count number of occurrences for all different values in database column and obtain 0 if are there empty result?

I'am newby with postgresSQL. I have a table with this structured: city | type | quantity | sellout | sellout_update "ACIREALE" | "Man" | "3" |"1100.0" |"366....
6
votes
1answer
238 views

Where are database specific settings stored?

Postgres documentation says ALTER SYSTEM writes the given parameter values to the postgresql.auto.conf file, which is read in addition to postgresql.conf. I am curious about where the alter database ...
0
votes
1answer
36 views

Problem with How to join with subquery

I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
4
votes
1answer
176 views

Postgresql numeric and decimal is automatically rounding off

CREATE TABLE IF NOT EXISTS ttable ( tcol decimal(9,7) ); insert into ttable(tcol) values(17.4604786); the value is getting stored as 17.46 Happens the same if I use decimal/numeric type I am ...
0
votes
0answers
42 views

Increase shared_buffers without restarting PostgreSQL

According to the official PostgreSQL documentation: shared_buffers (integer) Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 ...
0
votes
2answers
36 views

How can I obtain the last inserted row with INSERT … SELECT in PostgreSQL?

I'm trying to batch-copy data in a specific order from one table to another in PostgreSQL 12-beta2. The table is not using sequences, but contains an composite unique Primary Key (user_id, object_id). ...
0
votes
1answer
31 views

Postgres DB is extremely fast with a query with a certain parameter, but extremely slow for another

I have a table called stats, where I store every action on a certain website (basically when the user clicks a link, data is saved in the db). The data is modeled with PostgreSQL as ( id integer NOT ...
0
votes
1answer
30 views

How to add days to a date in PostgreSQL?

I have two tables and I want to execute an update similar to this: UPDATE table1 set table1.date = table2.date + table2.numberOfDays FROM tabel2 WHERE table1.id = table2.table1_id Edit: table1.date ...
0
votes
2answers
37 views

How to convert UPDATE operation into INSERT using triggers

Given I have a table User with column status (active/inactive) these users may be attached with some other entities, so I do not want them to get modified unless explicitly specified. and for ...
1
vote
1answer
31 views

archive command repeatly failing for one particular file

I am seeing many pg_log files full of following errors 2019-07-08 00:04:06 CEST DETAIL: The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/...
-1
votes
2answers
44 views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
0
votes
1answer
26 views

Get the 1st record and last record of a day in PostgreSQL

Below is the table structure: | Createdtime | Value ---------------------------|-------------- |2019-06-02 00:25:35.86+08 |7805165.31 Mb |2019-06-02 00:03:52.645+08|7796650 Mb |2019-06-...
3
votes
1answer
32 views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
1
vote
1answer
32 views

Disallow superusers and single-user mode in POSTGRESQL

I have a doubt about superusers and single-user mode. I want to restrict the access to a certain database, in a way that only those users which I gave privileges can see the structure of my database....
0
votes
1answer
25 views

PostgreSQL: Find row with JSON object attribute in array

I have a postgres database which is set up like this: CREATE TABLE arr_test ( id serial primary key, data jsonb ); INSERT INTO arr_test (data) VALUES ('{"test_row": 1, "my_arr": [{"serial":"...
1
vote
1answer
27 views

Predict how much hard drive space a query might cost?

Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My ...
1
vote
1answer
41 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
1
vote
1answer
28 views

Does pg_export_snapshot work across replicas?

This is probably of little importance, but it's possible in PostgreSQL to retrieve the transaction snapshot with pg_export_snapshot and restore it with set transaction to start a second transaction ...
1
vote
1answer
48 views

RDS: permission denied for relation spatial_ref_sys when copying

I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get, ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS
0
votes
1answer
73 views

Prepare statements plan invalidation in postgresql

Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-...
2
votes
1answer
38 views

How to reduce foreign key performance impact in PostgreSQL

Foreign keys add significant overhead...more than +600% in the example below (Docker PostgreSQL 11.4): CREATE TABLE example_1 (a int PRIMARY KEY); CREATE TABLE example_2 (a int NOT NULL REFERENCES ...
1
vote
1answer
36 views

Postgres How to securely initialize the database?

I'm initializing a Postgres DB and would like to know what is the best way to do it. Objectives: 1. We would like to use default database (postgres) unless there's a strong reason not to do. 2. We ...
0
votes
0answers
27 views

Storing earth observation derived attributes for polygons

I want to generate a number of vegetation related indices (NDVI, NDTI, etc., min/max/avg/std (pixel values)) from earth observation data for a large number of polygons during the entire growing season,...
0
votes
0answers
14 views

How to store Postgres password in configuration hibernate.properties

what is the correct way to provide PostgreSQL password in hibernate.properties(encrypted or plain). If encrypted, who is responsible for encryption and decryption Please post any doc which is helpful. ...
0
votes
1answer
35 views

Unable to restore a PostgreSQL database from a .backup file

I am using Ubuntu 18.04 and PostgreSQL. For the GUI, i am using pgadmin4 (browser version). I created a new database and when trying to restore, the following error message comes up: <!DOCTYPE ...
0
votes
1answer
23 views

How to verify data integrity of data folders after data recovery in MySQL and PostgreSQL?

I wonder if there is a way to verify data integrity of data folders from a data-recovered hard disk, both MySQL and PostgreSQL?
0
votes
1answer
28 views

Append JSON array to an object in PostgreSQL

I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure. +----+------------+----------+----...
0
votes
1answer
34 views

What is the proper way to relate many to many?

I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
0
votes
1answer
22 views

Is there a way to search the earliest or latest postgres partition for table?

BACKGROUND I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good. However, based on the data I'm querying, we don't have a ...