Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
9,365 questions
0
votes
0answers
6 views
What is the optimal pipeline for updating table on FK value in Postgresql?
Hi I have the following schema in my app.
-- Table: Investment_round
CREATE TABLE Investment_round (
id serial NOT NULL,
round_type varchar(50) NOT NULL,
money bigint NOT NULL,
...
0
votes
1answer
8 views
Postgres Deadlock - what does “while rechecking updated tuple” mean?
Question: Can someone please decipher while rechecking updated tuple for me?
ERROR: deadlock detected
DETAIL:
Process 31391 waits for ShareLock on transaction 770573675; blocked by process 25788....
0
votes
0answers
3 views
Uninstall pgaudit?
I have installed pgaudit for my RDS instance in AWS following the instructions here. Now Im not satisfied and move the audit to the application layer and want to uninstall pgaudit. How can I do this?
...
1
vote
0answers
6 views
Optimizing query performance with aggregation tables and partitioning
Our setup is a bit complex, so I will try to simplify what our tables look like but still try to describe the real problem:
We have a large table (items) with about 500 million rows (grows with ~1....
2
votes
1answer
13 views
Is it possible to add a spatial index to a partition with Postgres 10?
I am creating declarative partitions like:
CREATE TABLE mydata.my_features (
id bigint,
my_feature1 character(1),
my_feature2 integer,
...
my_geom geometry
) PARTITION BY ...
0
votes
1answer
24 views
query for each row from another arbitrary query, and union results
I'm trying to build a query that will take another query. Then, perform another query based on those results. Union the results so that there are no duplicates. All on the same table. I've seen some ...
4
votes
1answer
31 views
Can a non-owner of a table be allowed to disable the table's triggers?
I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?
-2
votes
1answer
13 views
how to get database information Postgres
how to get database username and password?
I create one but only information that I have is the name of the Database and Owner name
2
votes
1answer
25 views
Join tables but keep matches in a single row
I have the following tables structure in a PostgreSQL DB:
These tables have this content:
So if I want to retrieve the SB associated to each server I'll need to join these 3 tables:
Server with ...
0
votes
2answers
24 views
Most efficient way of updating table from another - Postgres
I need to populate a foreign key column based on the contents of another table, and both methods I've tried are prohibitively slow.
I have the below tables (some columns omitted):
...
2
votes
1answer
36 views
Make a DBMS-enforced multi-column “UNIQUE” constraint, with a “match all” wildcard
Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour.
I have multiple columns. Now I wish to make sure each entry into the ...
1
vote
1answer
13 views
Wraparound Transaction ID
Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
1
vote
1answer
31 views
\COPY not returning count psql
I am trying to copy CSV data into tables. Also I want to print the count.
The command I'm using is:
echo "begin; delete from a; \copy a from a.csv CSV HEADER; end;" | psql -hlocalhost -dpostgres -...
2
votes
1answer
24 views
Is it possible to have unique together constraint on a SET of columns in PostgreSQL?
What I want to achieve is the following:
CREATE TABLE foo (
bar1 int,
bar2 int,
type varchar(16),
FOREIGN KEY bar1 REFERENCES bar(id),
FOREIGN KEY bar2 REFERENCES bar(id)
);
...
1
vote
0answers
14 views
Force psqlODBC Postgres drivers to use Windows Trusted Root Certificate Store
I need to configure my client software to use SSL when connecting to a postgres database (sslmode=verify-full). The configuration requires me to specify a root certificate file in the following ...
3
votes
0answers
33 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
31 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
13 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
17 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
7 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
17 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
13 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
40 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
15 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
11 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
32 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
24 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
26 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
20 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
20 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
31 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
0
votes
0answers
17 views
How can I get user rank using sequelize? [on hold]
I've a basic table called users which have these columns id xp
and I want to have to get user rank based on the xp.
for example:
ID XP
------------
123 100
567 150
378 400
378 ...
1
vote
1answer
18 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
31 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
74 views
+100
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
19 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
63 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
15 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
32 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
40 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
31 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
0answers
20 views
How to implement database versioning just like Rest API using dotnet core postgresql? [on hold]
We are working on a Social App and wanted to implement back-end API with versioning feature and hoping database versioning as well. We decided to write the back-end with Dot.Net Core and the database ...
0
votes
1answer
28 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
0answers
37 views
How to utilize an INDEX when filtering a VIEW? [on hold]
edit: Double-posted this to Stack Overflow where I received some great input. Worth a look.
I have a very simple query that executed surprisingly slow and the reasons turned out that it was doing a ...
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
97 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. ...
