All Questions
Tagged with postgres-9.6 or postgresql-9.6
934 questions
0
votes
1
answer
758
views
VACUUMs executed in parallel
In my application n microservices are running on m nodes. Each microservice has its own database and is responsible for issuing VACUUM ANALYZE once in a while.
Since the scheduling of this routine job ...
- 81
1
vote
2
answers
1k
views
SQL group by a field and only return one joined row for each grouping
Table data
+-----+----------------+--------+----------------+
| ID | Required_by | Name | Another_Field |
+-----+----------------+--------+----------------+
| 1 | 7 August | cat | X ...
- 279
1
vote
2
answers
607
views
Changing password of postgres user led imidiate prompt of wrong password without providing a password
So I forgot the password of the postgres user on my PostgreSQL server. I then changed all the md5 settings in the pg_hba.conf file to trust, restarted the server and then changed the password of the ...
- 1,310
0
votes
1
answer
67
views
pgsql EXECUTE sql not take effect in version 9.6
I want to give all privileges to each table in the database。
sql as follows：
CREATE OR REPLACE FUNCTION test()
RETURNS void
AS $$
DECLARE
tb RECORD;
sql1 TEXT;
BEGIN
FOR tb IN (select ...
0
votes
0
answers
1k
views
Do you need to install an ODBC driver on a Mac to query a PostgreSQL (pgadmin 4) database?
Yesterday I sat the whole day installing ODBC drivers on my work computer.
Why? so that I could query our pgadmin 4 database using postgresql 9.6, and populate my Excel workbook with info.
Now, my ...
- 39
0
votes
1
answer
1k
views
Where do i begin diagnosing PostgreSQL performance problems?
I have a table called modifications with 42 columns and 84M rows. Total size is 64GB.
I am running Postgres 9.6.11 on Amazon RDS with 16GB of RAM on a db.m4.xlarge instance.
When i run a simple SELECT ...
- 1,093
0
votes
2
answers
2k
views
How to aggregate multiple rows from subquery?
I want to get array of chd_id's for each prod_id,
query below works but only with limit 1 i want exact result but with multiple rows
select
prod_id,
array_agg((select * from ...
- 2,746
2
votes
0
answers
461
views
Upgrade PostgreSQL from 9.6 to 11
I'm using the PostgreSQL v9.6 database for my Django (v1.11) application and I want to move to PostgreSQL v11.
Are there any changes that need to be made before? Any risks for my Django application? ...
- 55
2
votes
2
answers
2k
views
Get all triggers that call a certain function
So an insert violates a constraint because a trigger modifies the data when it shouldn't for this case.
Thing is, I already disabled the triggers that I thought were causing this, and the function ...
- 8,239
2
votes
1
answer
2k
views
Postgres aggregate with case
I have a some query that returns a table "code_1"(id - distinct).
|---------------------|------------------|
| id | status |
|---------------------|----------------...
4
votes
0
answers
779
views
Using QuerySet.update() to update specific keys in nested JSONField
So let's say I have a Model defined as below:
from django.contrib.postgres.fields import JSONField
class Job(models.Model):
...
json_data = JSONField(default=dict, blank=True)
The json_data ...
- 121
1
vote
1
answer
1k
views
Does deleting a table delete its triggers as well as functions?
I have created a table score and also created a function and a trigger along with it.
CREATE TABLE scores(
--fields
);
CREATE FUNCTION scores_before_save() RETURNS trigger AS $$
BEGIN
-- ...
- 3,654
13
votes
1
answer
17k
views
When and how does Postgres use "transactionid" locks
I've ran into a need to figure this out on 9.6, but information on anything 9.6 or later would be appreciated.
I'm having an issue with my application being blocked on a database call because it's ...
- 4,273
0
votes
0
answers
701
views
Cancelling statement due to timeout PostgreSQL
I am trying to execute the below SQL query, but it is giving me an error, because of large number of items I'm putting in the where clause.
For column a 85,000 items.
SELECT distinct a,b,count(*)
FROM ...
- 79
0
votes
1
answer
1k
views
Most optimal way to backup postgresql database
I have a medium-sized database (postgresql 9.6) with moderate traffic. The database is located on a virtual server, described as having 4cpu cores and 8192mb of ram.
Currently I am backing up the ...
- 356
0
votes
1
answer
636
views
Error key already exists appears only while executing function
I have 2 tables, in one I create an element, in the other I save the details of said element in different languages.
The first table:
CREATE TABLE public.element
(
idelement integer NOT NULL ...
- 67
0
votes
1
answer
67
views
Should Postgres COPY FROM be updating BRIN index?
Imagine a table like...
create table study_value (
id serial primary key,
study_id int not null references study (id),
category text not null,
subcategory int not null,
p_value ...
- 1,161
0
votes
1
answer
462
views
Are these Postgres Subquery Subselect InitPlans Run in Parallel?
I'm trying to understand how Postgres works. After reading this, https://www.postgresql.org/message-id/4572.1280671706%40sss.pgh.pa.us These subqueries are subselects because they don't reference ...
- 333
0
votes
0
answers
67
views
Postgresql randomly starting to complain about grouping clause
For almost a year, I have been using this sql to report on the rank of game profiles, based on the number of days that a game has been above the rank of 10:
SELECT P.id, P.name, P.rank, COUNT(P.id)
...
- 356
0
votes
0
answers
75
views
PostgreSQL query plan changed
I recently did a full vacuum on a range of tables, and a specific monitoring query suddenly became really slow. This used to be a query we used for monitoring, so would happily run every 10sec for the ...
- 811
3
votes
0
answers
2k
views
Best way to rotate encryption keys in postgresql
We need to encrypt some columns in a postgresql 9.6 database. We are planning to use pgcrytpo's asymmetric key method for our requirement.
we keep daily, weekly, monthly and yearly backups using ...
- 1,776
0
votes
1
answer
198
views
PostgreSQL 9.6: Why is this pg_restore command working for me but not my colleague?
We've got a brand new coder on board. As one does, we'd like for him to have a local version of our PostgreSQL database to mess about with.
Dumping the production database as follows worked without ...
- 176
1
vote
1
answer
155
views
Fetch records where json contains a particular object
I have a postgres 9.6 table which has a json field config. I want to fetch records from this table where the json has a particular key value pair.
My table is as follows
CREATE TABLE features(
...
- 3,654
0
votes
1
answer
81
views
Inserting a number in a sequence with missing numbers
I have a table containing info of tree nodes.
element_type_id | root_id | parent_id | number_in_parent
4 1 1 1
4 1 1 ...
- 632
0
votes
1
answer
4k
views
Changing client_min_messages and log_min_messages
Hi Im trying to change the default value of client_min_messages and log_min_messages from postgres user, it works in the session but then if I restart it comes back to default settings.
Do I need to ...
