Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
9,967
questions
1
vote
0answers
31 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 ...
-1
votes
1answer
16 views
What is the fastest way to remove a key from a jsonb column of Postgres?
I have a table of 10K rows in projects table. Deleting a key using the following operation never finishes.
UPDATE projects SET misc = (misc - 'foo');
Is there anything I can do to make it faster?
...
0
votes
0answers
12 views
Postgres, use via elasticsearch, in php, how to? [on hold]
What is the simplest way to use elasticsearch for postgresql, for example for searching on the web in php?
I browse internet for 2 days, but I still don't understand how is elasticsearch exactly ...
0
votes
1answer
11 views
cron command running postgres at high cpu [on hold]
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
14 views
Create table in Postgresql locks pg_class forever
Hye,
I have a Postgresql cluster with few databases. In one of the DBs, if I try to create a new table, the command is never ended until I kill the the linux proccess. I checked pg_lock and found ...
0
votes
1answer
27 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
13 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
19 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
24 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 ...
0
votes
0answers
25 views
Convert Excel function to PostgreSQL [on hold]
I am looking to convert this Excel function to PostgreSQL.
=INDEX(position!A:A,MATCH(species!C2,position!D:D,-1))
The function uses two tables.
It looks up the for same values between the D column ...
1
vote
1answer
23 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....
1
vote
0answers
10 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
39 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
22 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
45 views
Postgres Restore
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
43 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
36 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
18 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....
0
votes
0answers
27 views
Postgresql HA using patroni [on hold]
We are using master-slave configuration for our PostgreSQL DB with streaming replication.
But now we want to setup high availability using Patroni. I have configured etcd, HAProxy and two Postgres ...
6
votes
1answer
216 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
29 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
157 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
30 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
0answers
52 views
the AND argument, SQL state: 42804 [on hold]
this query, taken individually, works, but in the function in which it is inserted, it gives me an error
(select count(*)
from a
where a.d=
(select max(a.d)
from a,o ...
0
votes
2answers
33 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
23 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
25 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
33 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
18 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
38 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
25 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
25 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
30 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
15 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
26 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
35 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
21 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
21 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
45 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
32 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
31 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
23 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
12 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
26 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
18 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
22 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
25 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
21 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 ...
0
votes
1answer
14 views
How to install pgstattuple extension in postgres 10.6?
Can someone let me know how to install pgstattuple extension in postgres 10.6 running on Ubuntu 16?
I have installed postgres-contrib and can see the following .sql files under extension folder
...
4
votes
0answers
41 views
PostgreSQL 10 backend processes
I have a particular issue with PostgreSQL 10:
Postgres creates backend processes for each connection on different ports. The problem is that some of our apps also use many ports and sometimes they ...
