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
2answers
44 views

Postgresql 11 - slow insert performance when table reaches ~5 million records

I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD). It seems to be a I/O problem, since there are free memory and processing ...
0
votes
0answers
40 views

OpenProject pg_restore - input file does not appear to be a valid archive

I'm trying to restore my OpenProject's database created with the command sudo openproject run backup this command created the file postgresql-dump-20190722131834.pgdump which I'm trying to restore ...
0
votes
1answer
45 views

slow count queries - what are we doing wrong?

Using Postgresql 9.6.14, running the following query: SELECT COUNT(*) AS "__count" FROM "crm_client" INNER JOIN "crm_role" ON ("crm_client"."role_ptr_id" = "crm_role"."id") INNER ...
0
votes
1answer
14 views

How to check and change function volatility in Postgres?

I have a function that I think is defined as volatile, but I now know I want it stable. How can I check the function volatility (volatile, stable, immutable) of a certain function? When a function ...
0
votes
3answers
42 views

Should I create a new table or save “static” data in all the tables? [closed]

I'm using Postgres as the database management system. Currently, I have a countries table, which has only four countries, i.e. four rows (might add one or two more countries, i.e. rows, later). --...
1
vote
2answers
35 views

How to find resource intensive SQL operations in Postgresql? High CPU usage spike

I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period. ...
-2
votes
2answers
51 views

How do writers not block reads in PostgreSql

Since readers do not block writers and writers do not block readers is what MVCC is all about, how does postgresql prevent writers who are in the process of committing their changes to blocks/pages ...
0
votes
2answers
33 views

How do I find a list of customers that have not placed an order in the past 1 year?

I'm having trouble figuring out how to write this query: We have a sales table but the sales table only represents customers that have purchased. We have a customers table that represents all of the ...
0
votes
0answers
27 views

PANIC HELP Locked out of Postgres Databases and unable to find pg_hba.conf

I am using PgAdmin 4 to access a number of local databases created and hosted on postgresql 11. In a recent update, I set a global password for my databases, but forgot to set passwords for my user ...
0
votes
0answers
13 views

Rename big Postgres table?

I have a table with 800 millions rows in data warehouse. And there is a time when it is not used. Is it time consuming operation to rename it with ALTER TABLE table_name RENAME TO new_table_name; ...
0
votes
1answer
32 views

Select with if statement return 'column does not exist'

Database, application. PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1), Dbeaver 6.1.1. What I need? Return right values. What I do. Here is part of code. select if(available_count < 5, "neni skladem"...
1
vote
1answer
32 views

DELETE and INSERT - is this simple query statement sufficient to preserve atomicity?

I intend to update a table by going for a DELETE then INSERT on postgres in a single query. Obviously I want the DELETE to only take place if the INSERT is successful, i.e. preserve atomicity. Is ...
-2
votes
1answer
35 views

Performance using a jsonb column in PostgreSQL? [closed]

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
0
votes
0answers
16 views

store Json field in an external file (.json) by python [migrated]

my table in postgresql v11 contains a json field, i would to store this data in an external file JSON using python (lib: SQLALSHEMY). i try to do it but i have a difficulty: import psycopg2 from app ...
0
votes
1answer
45 views

Postgresql 11 SSPI authentication failing

On windows 2016 in AD setup when I try to use SSPI with postgresql 11 and following settings : pg_hba.conf: host all all 127.0.0.1/32 sspi map=newmap #IP4 pg_ident.conf: newmap ...
0
votes
2answers
35 views

Running a task without waiting for the results in Postgres 11 on RDS

I've got a question about spawning stand-alone threads on Postgres. Here's what I'd ideally like: Have a postgres-side solution to automatically launch tasks on a schedule in their own threads. ...
0
votes
0answers
20 views

How to implement row count for REST API?

I’m building a REST API backed by PostgreSQL. I'm encountering something I don’t think is very niche, but I’m having trouble finding any discussion of it. Good REST API practice dictates that if a ...
0
votes
1answer
27 views

Consequences of cascade drop on sequences/functions/views

I am migrating some data to another server while dropping all sequences/functions/views. When I try to do simply DROP VIEW | SEQUENCE | FUNCTION | AGGREGATE without CASCADE, errors are thrown to ...
0
votes
1answer
30 views

Getting Values of the two rows before the selected row POSTRESQL

I want to update an row according to the values of two rows before the row how can i do that? Let's assume my table looks like this ID amount 1 1 2 3 7 5 And ...
0
votes
0answers
27 views

Query to reduce column length

I'm new to PostgreSQL so sorry if I haven't explained myself clearly! I've used the below query to find out the max character length of each column in my table. I now want to be able to shorten each ...
0
votes
1answer
18 views

Query to get the number of errors reported by PostgreSQL

Is there any internal table recording the number of errors produced by Postgres? I was expecting something like: select database, count(*) from pg_errors where type='error' and date ...
0
votes
2answers
42 views

How to move old data to cheap hard drive

I have a quite big PostgreSQL database (with timescale plugin). Right now it is consuming about 500Gb on a SSD. Most of the data is in the form of time series. In most cases data older than a few ...
0
votes
1answer
20 views

Options and best practices for Multiple Result Sets in Postgres11

I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database. SQL Server allows a stored procedure to contain multiple select statements. SELECT u....
0
votes
1answer
31 views

How can I find the longest record (in characters) for each column in a table in PostgreSQL

I couldn't find the answer to this on here so sorry if repeated. I'm looking to query a table to find the longest record in characters for each column in a specific table. I have a table which is ...
0
votes
2answers
34 views

In PostgreSQL URL I can't use a password containing special characters

I have a postgresql database in Azure The passwd string has special characters like these: )mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU psql --dbname=postgresql://db_user@mydemoserver:)mvd99/iyH_=ag=...
0
votes
0answers
12 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
0
votes
0answers
17 views

Date between intervals dynamic

I have a table with all employee licenses and two dates in relation to a period of 2 years. I have to verify if have someone period sum of 30 days of license in one range 12 months For example: data ...
1
vote
0answers
15 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 ...
-1
votes
0answers
55 views

how to install debugger [closed]

how to install debugger postgresql 10 on windows 7 with pgadmin 4? To the page https://www.postgresql.org/message-id/1328980004.2250.14.camel@localhost.localdomain it's written to remove .dll when ...
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
21 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
18 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
33 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
31 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
40 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
49 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
55 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
59 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
86 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
22 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....