Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

5
votes
1answer
70 views

Best index for column where values are mostly the same

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to ...
1
vote
0answers
34 views

Noob Questions - Database approach

I have a database of "records" that relate to photos - title, location, text, tags, etc. I am now adding more fields for other media types, videos for example will get an transcript, audio will get ...
-1
votes
1answer
19 views

Can't compare value of a JSON key with a text value

SELECT id, body->>'process_code' as process_code, body->'filePaths' as files FROM masters."PROCESS" where body->'filePaths' @> '[{"access_type": "Public"}]' or body-...
0
votes
0answers
20 views

How does GRANT privileges function when recursive privileges?

I'm trying to understand how does granting privileges work on SQL. According to what I understand, one user has privilege as long as said user has been granted privilege by someone or owns the ...
0
votes
1answer
15 views

PostgreSQL's PGP_SYM_ENCRYPT log security

In one of our project, I tried to use PostgreSQL's security extension pgcrypto. I found that is function, PGP_SYM_ENCRYPT is quite handy. Using this function enable me to query encrypted data with ...
0
votes
1answer
25 views

group by like string

I have a table employee in this table i have id company_name employee_id 1 abc 2015_acb_1 2 abc 2015_acb_2 3 abc 2015_acb_3 4 abc 2015_acb_4 5 xyz 2016_xyz_9 5 xyz 2017_xyz_2 5 xyz ...
1
vote
0answers
20 views

PostgreSQL permissions

I'm trying to achieve a simple architecture: An organization has many users A user belongs to many organizations A user can connect A user can join and leave an organization A user can read-only ...
0
votes
1answer
17 views

Postgres 8.3 correct deletion of files in pgsql_tmp

We are talking about the PostgreSQL 8.3 RDBMS here. So, no pg_terminate_backend() is possible with this version. Sometimes we have to kill running processes at Operating System level (kill -9 PID) to ...
0
votes
1answer
42 views

Generate data delta between two databases PostgreSQL

I have a big database with a lot of data ~50GB that spans around 40 tables - running PostgreSQL 9.6. This database is copied and sits in number of client sites without an Internet connection. ...
0
votes
1answer
20 views

get inherit keys inside json

I have JSON field with values like in the example, and I need to get all data from the key 'stat' inside it. The list of keys inside the data not constant. WITH test_data AS ( SELECT 1 as id, '{ ...
0
votes
1answer
12 views

Is the genetic query optimiser enabled by default?

I read with interest about postgres' genetic query optimiser. Is this enabled by default? Is there a setting to enable it? Are there good rules of thumb of when to use it versus when now? Doc link ...
2
votes
1answer
36 views

SELECT very slow when JOIN and LIMIT are specified but no/few results found

I am running into an issue where the query planner does not take into account how a JOIN effects the frequency of particular values. For some context: I am storing sessions in an append only log ...
0
votes
0answers
17 views

Formula to change ID in table of postgresql

I have table in which my id's are completely random. I would like to update table and set id according to type_id. The formula should looks like that: (type_id * 100) + nextval. Nextval should be ...
0
votes
0answers
35 views

Postgresql: Select a table from different database and different host

I know this is not new, but I have not found a solution, even though I have followed several posts in this forum. I'm trying to show table from different database using dblink extension. Installing ...
0
votes
2answers
42 views

Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor

I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux: "C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --...
0
votes
0answers
23 views

Creating a relationship for a database model based on that model's date range

I have a database of Schedule objects in PSQL (schedule_id, user_id, date_range ( [date, date) ), and a few other fields not necessary for this discussion). Each of these Schedule objects is ...
0
votes
1answer
27 views

Migrate complete postgres database cluster to another machine using pg_dump

I am referring to How to get a working and complete PostgreSQL DB backup and test In this example/solution, which part of the restore process does actually the "CREATE DATABASE sandbox"? I have been ...
0
votes
2answers
116 views

pg_resetxlog command not found

I can't find pg_resetxlog under /usr/bin. postgres (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1) cd /usr/bin postgres@node-1:/usr/bin$ ./pg_resetxlog -su: ./pg_resetxlog: No such file or directory
1
vote
1answer
19 views

calculate days spent of all visitors from encounters

I have an encounters table CREATE TABLE visitors( id serial PRIMARY KEY, start_date TIMESTAMP NOT NULL, -- visitor_id bigint NOT NULL -- skip for now, end_date TIMESTAMP ); (end_date can be ...
1
vote
1answer
72 views

Relation does not exist while scanning schema

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS ...
2
votes
1answer
110 views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
0
votes
0answers
12 views

Syncing DDL accross Postgres shards

Our database writing needs exceed the capacity of a single Postgres cluster (since each cluster can only support 1 writer server). To combat this, we've sharded our data across 8 separate Postgres ...
0
votes
2answers
22 views

Postgres Procedure Returns Table

We have a table structure returned by this function: RETURNS TABLE (ticker VARCHAR, current_recommendation INTEGER, previous_approved_recommendation INTEGER, ...
1
vote
1answer
67 views

How to Prevent heavy queries on one database, affecting the performance of other databases on same server?

I have a linux server running just Postgres with PostGIS Extension. There are several databases on this instance of the Postrges Server. One of the databases has some computationally intensive ...
0
votes
0answers
18 views

PgAdmin3 connection to database failed with no error messages

I have a local postgres database with PgAdmin3 LTS by BigSQL on my laptop that until today I could access without any problems. When I tried to access it today by typing the password I get only a "...
0
votes
0answers
49 views

Is it possible to queue PostgreSQL logical replication messages from multiple sources?

I want to use multiple "forward" PostgrSQL instances which serve distinct applications but share overlapping subsets of the same schema to update a single master which holds a full version of the ...
1
vote
1answer
43 views

How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
0
votes
2answers
81 views

postges: psql csv import fails although file exists

Trying to import a csv file as follows: psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;" Password for user admin: ERROR: could ...
1
vote
0answers
30 views

Order of logical decoding output on Postgres 11

Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows? CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ,name TEXT ) ; CREATE TABLE t2 ( t2_id INTEGER ...
0
votes
1answer
32 views

PostgreSQL. Shared_memory and sessions

I have a standalone server which is running Jira and PostgreSQL 9.6. I have noticed that PostgreSQL has shared_buffers parameter is equal 128MB where RAM is 32GB. Because server is shared with ...
0
votes
1answer
22 views

Why do I get an error when filtering subSelect?

select "masters".*, (select avg (rating) from "reviews" where "master_id" = "masters"."id") as rating from "masters" where rating > 3 [42703] ERROR: column "rating" does not exist
3
votes
1answer
110 views

pg_dump custom archive and create backup date

I have archive file with custom format created by pg_dump. Does it contain the backup creation date? I can't create backups by myself. I receive the archived files over specific network channel. ...
0
votes
1answer
23 views

Sequence according to value from table in postgresql [duplicate]

I have dictionary_type and dictionary_value tables. Value is connected with type. I would like to create sequence which will set the id of values according to type id. Expected dictionary_type: id ...
1
vote
0answers
31 views

Is Big data solutions a good option for 500 million temporal records?

So I have about 250k items x 2000 days (2010 to 2019) ~= 500 million records. For each item I have variable number of fields. To begin with we have 50 fields defined for each item but in the future ...
0
votes
1answer
93 views

Why do I receive the error message “'<column name>' must appear in the GROUP BY clause or be used in an aggregate function”?

Where am I going wrong? Why am I getting that message when I add the MAX function? The query involved: WITH references_cve AS ( SELECT vulnerability_id, reference FROM ...
0
votes
1answer
34 views

Migrating triggers, functions and procedures from Oracle to PostgreSQL

We run a Java based application which uses Oracle as its backend. While they are rather simple and don't overly use Oracle specific functions, we have lots of triggers, function and procedures written ...
0
votes
1answer
17 views

Insert into value as generate row with different minutes

I try backup and restore using WAL-E/WAL-G. I can backup and restore. But i want check recovery_target_time. During recovery, by default WAL-E will attempt to pull and restore all WAL segments ...
0
votes
1answer
30 views

SELECT vs COPY (SELECT) TO STDOUT

I need to read more rows than fit in memory, resonably fast. I see two options: SELECT ... where my bindings use a cursor to stream the rows; COPY (SELECT ...) TO STDOUT (FORMAT binary) where my ...
0
votes
0answers
21 views

Running REINDEX in a live environment (POSTGRESQL 9.6.10)

I have a live app where I have some big tables (~100+M rows), which I recently truncated to ~20M rows. One behavior I've noticed after making the said change is that DB response time has escalated in ...
0
votes
1answer
20 views

jdbc equivalent to libpq “hostaddr” connection string parameter

Is there an equivalent to libpq's hostaddr parameter in the JDBC connection string? I want to connect via a local TCP proxy (on 127.0.0.1), but use ssl=verify-full with the CA bundle of the remote ...
0
votes
0answers
23 views

Adding LIMIT slows down query

SQL CREATE TABLE actions ( action_id bigint GENERATED ALWAYS AS IDENTITY, block_time timestamptz NOT NULL DEFAULT now(), block_num int NOT NULL, trx_id text NOT NULL, ...
0
votes
0answers
15 views

Weird results when querying PostgreSQL (lower performance when requesting a small string than requesting a larger string)

I’m developing an experiment where I want to analyze the impact of the database in a web application. The experiment consists of searching for 1 string on the database according to an ID and returning ...
0
votes
0answers
13 views

Your preferred HA replicated setup of PostgreSQL on Kubernetes - does this even make sense?

I'm currently looking to run a new production level PostgreSQL instance on a Kubernetes cluster. I'm just a software developer who only uses PostgreSQL on the local machine during application ...
1
vote
1answer
26 views

Sort output of plpgsql function

I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples ...
0
votes
1answer
41 views

Postgres how to drop table owned by another user without mirroring that user's Permissions

User A owns all of our tables and has access to all of our schemas User B needs full access including Drop and Alter permissions to some of those tables and schemas but not all of them. As is well ...
0
votes
1answer
26 views

postgresql materialized views vs versioning

I came across a pickle when dealing with question from one of our dev concerning postgresql and materialized views: can I refresh materialized view with my application user? well according to docs (...
1
vote
0answers
21 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
0
votes
1answer
34 views

pg_hba error while setting up repmgr

Please help me to figure it out, help is much appreciated! ERROR: FATAL: no pg_hba.conf entry for replication connection from host "172.18.0.4", user "repmgr", SSL on The following is a list of my ...
0
votes
0answers
19 views

Correct config wal-e s3 for minio

Try run PostgreSQL + WAL-E + Minio. Minio: https://github.com/minio/ yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm yum install -...
0
votes
1answer
23 views

how to refresh table in pg-admin 4?

When i select table with view/edit data => all Rows then i try to refresh table for newly updated data in table from object tab. it seems it's not working.this works perfectly in pg-admin 3 but in pg-...