Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
9,311 questions
0
votes
0answers
10 views
Variable length Work Histories in a RDB
Am building a database for CVs/Job Applicants in Postgres (via Django), which would naturally include work histories for every person.
Have to consider the variable length of people's careers, which ...
0
votes
0answers
10 views
Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database
We have one master table and multiple child tables (dependent tables) associated with it.
While taking the backup of the master table , I would like to take the backup of all its child (dependent) ...
0
votes
0answers
15 views
Sync two postgres databases from two sperate locations with no network
I have 2 postgres databases running the same schemes on two different computers, that are not guaranteed to have any network connection while having to handle read/write requests.
Because they ...
0
votes
0answers
10 views
Postgresql - Usage of index on an inherit table but not for the original table (with the index)
My Postgresql database (postgres version 11.1 and postgis version 2.3) consists of hundreds of tables and each table contains 10 of millions of rows. Data is divided by month and then each month table ...
0
votes
0answers
12 views
How to Set Up a Shadow Production Database for PostgreSQL?
I'm working on a Django project and I'd like to avoid the common pitfall of using SQLite for development and PostgreSQL for production referenced in Two Scoops of Django.
I'd like to take this a ...
4
votes
2answers
157 views
Different syntax to add/substract interval
Till now I used the following syntax to add an interval to a timestamp:
select now() + '5 year';
This worked fine till I tried to subtract the interval which results in a syntax error.
invalid ...
0
votes
0answers
13 views
Fixing problems with postgreSQL wal archives
i just took over working postgre sql server, and i noticed in the logs, that are in: /var/log/postgresql/postgresql-9.4-main.log spitting out this warnings:
cp: cannot stat 'pg_xlog/...
2
votes
1answer
22 views
How to “group” based on consecutive occurrences of an element in a column in postgres
I have a table that, when ordered, describes a path by point descriptions in each row.
A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts ...
0
votes
1answer
15 views
PostgreSQL how to return data from anonymous code block?
I have a table mytable (id, "someLongURI", status, "userId") and a working query:
UPDATE mytable
SET status = 'IN_WORK'
WHERE "someLongURI" IN (
SELECT "...
-1
votes
1answer
28 views
pls give the solution . this query will not work [on hold]
select *
from masters."RCM"
where id in (select rcm_id
from masters."TASK"
where id in (select audit_details->>'task_id'
from masters."...
0
votes
0answers
8 views
Partitioning Postgres table that have references to and queried without date range filters
As I understand partitioning is useful only for fact tables that isn't referenced by other tables and when you issue range select queries in case if you also have retention policy.
Spring Batch ...
0
votes
2answers
22 views
How to update column with ascending value column
I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?
...
1
vote
0answers
21 views
pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)
Problem
I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since ...
-2
votes
1answer
32 views
Postgresql - LEFT JOIN error
Apologies ahead of time for the novice question as I am working through building my first database. I've come to joining my tables (I have multiple), but have decided to test one first. I am trying to ...
0
votes
0answers
10 views
Postgresql streaming replication sticky read queries
I have setup 1 master node and 1 slave node with postgresql streaming replication, is it possible to configure at database level to allow certain read queries to be executed in slave node only instead ...
1
vote
1answer
43 views
Use NEW in FROM clause in Postgres trigger?
I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.
SELECT
unnest(something)
FROM NEW
However, this seems to lead to an error:
relation '...
0
votes
2answers
38 views
Slow Sum/Group By over large amount of rows
Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10.
SELECT mike_romeo, SUM(hotel) as counts
FROM whiskey_lima
WHERE
yankee = 'kilo'
...
0
votes
1answer
17 views
Joining three tables with intermediate nullable option and filter condition
Sorry if this seems like a dumb question, but I've read through tens of answers and tried many different options, yet my query results were either overly inclusive or exclusive.
The basic scenario is,...
0
votes
2answers
29 views
Logical Replication to different schema name
We have PostgreSQL 11 database with structure one database one schema (public).
dbname : production_one
schema : public
dbname : production_two
schema : public
I would like to set up a logical ...
0
votes
0answers
33 views
Slow PostgreSQL query - how to improve it?
[Beginners question :-) hopefully on-topic here.]
In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to ...
3
votes
2answers
82 views
GRANT SELECT ON SEQUENCE succeeds, but to no effect?
I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the ...
0
votes
0answers
33 views
Postgres pg_dumpall failing
Recently on my debian server with postgres 10.6, pg_dumpall has started to fail with the following error:
su - postgres -c pg_dumpall | gzip > /temp/directory/postgres_backup.gz
pg_dumpall: query ...
1
vote
1answer
23 views
Replication to subscriber with different name
Is it possible in PostgreSQL to set up the following replication scenarios?
Replication between 2 servers with different database name. For example, replicate FROM server_A (primary) database name ...
2
votes
0answers
29 views
Getting unfamiliar error when using terminal command to update database
I'm a bit in over my head when it comes to using and managing large databases. I run a small startup that uses a PostGreSQL and pgAdmin to administer and query the database, which is housed on Herkou.
...
1
vote
2answers
33 views
Count items on condition
In an application where a user can have copies of a mobile application installed on multiple devices I'd like to know how many iphone and how many android apps each user has.
I tried a query such as
...
-2
votes
1answer
25 views
PostgreSQL won't start [on hold]
I installed Postgresql on Ubuntu 18.10 (on a VPN) and was unable to connect to it. At this point I did:
ps -f -u postgres
and got the shocking result of.... absolutely nothing. I ran htop to double ...
0
votes
1answer
16 views
ERROR: invalid GeoJson representation
the GeoJSON Spec show,
{
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [125.6, 10.1]
},
"properties": {
"name": "Dinagat Islands"
}
}
But when I wrap that in ...
1
vote
1answer
30 views
How to get the size of an index created in a plpgsql block
I'm trying to benchmark something. I want to create an index and get it's size in a plpgsql block.
This works:
haki=# create index ix on t(n);
CREATE INDEX
haki=# select pg_relation_size('ix');
...
1
vote
1answer
42 views
Query to obtain weighted percentile
Trying generate SQL to compute a weighted continuous value at a given set of percentile values (the 25%,50%,and 75% levels used below, but solution should allow for an arbitrary parameter level). In ...
0
votes
2answers
41 views
A table with many null values VS many tables
Basically my level of DB knowledge enables me to create, select, delete and some simple things. When it comes to designing for performance, I have absolutely 0 knowledge.
So my question here is let ...
0
votes
2answers
30 views
Get value increment during an hour of a day
I query YouTube Data Api for a list of most popular videos on a channel and then get their statistics, 4 times per hour (each 15 minutes, by cron). The data is stored in Postgres, but dumping it and ...
1
vote
1answer
21 views
How to Add Check Constraint between Tables (or change DDL)?
Given the following PostgreSQL table definitions:
postgres=# create table if not exists messages(
postgres(# id serial primary key,
postgres(# max_length smallint not null check (max_length &...
0
votes
1answer
19 views
Getting error on a particular query on a table in Postgres
I am getting the following error on one query in Postgres:
select * from sampletable where sample_id = 1234;
Error is :
ERROR: could not access status of transaction <some-id>
Detail: CLOG ...
0
votes
0answers
35 views
What makes ALTER TABLE X SET UNLOGGED; to be slow?
We develop and maintain a JEE Application with Postgres (multiple version of it as we've got different customers using different versions). The Code evolves over time; database changes are organized ...
0
votes
0answers
26 views
Aggregate per Group returns Duplicates
I have the following scenario a user belongs to several Groups. The Groups are organized in a hierachy:
I want to aggregate all the Person Information of the Groups the User Belongs to. Eg. Person 1 ...
1
vote
1answer
23 views
What happened to pg_xlog?
There uesd to be a directory called pg_xlog that stored all of the WAL logs in PostgreSQL? Part of restoring a basebackup under the archiving scheme required me to copy the WAL into DATA_DIR/pg_xlog. ...
0
votes
2answers
32 views
Fastest tool or technique for occasionally syncing Postgres 9.6 databases
Occasionally I need to sync latest data and schema from production to staging db server. What tools or techniques do you recommend if syncing speed is a priority? The sync doesn't need to be in ...
2
votes
2answers
74 views
Get the max value from a slice of a JSON Array?
I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw.
The limits Start & End of the slice are ...
1
vote
2answers
37 views
postgres parse_indent() function hang [on hold]
select parse_ident('abc,efi&xyz');
Will stay there for ever, not returning. But a similar command will finish.
select regexp_split_to_array('abc,efi&xyz', '[,&]');
...
-1
votes
0answers
20 views
Postgres Sql Stored Function suddnely slows after first five queries?
postgres SQL Stored procs suddenly slow down to ~1.2 min after first five queries,
When recompiled it speeds up to ~ 200 ms for the next five then slows down afterward.
the above behavior is seen ...
0
votes
2answers
32 views
Postgres jsonb vs composite type performance differences
What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?
For example, consider a column like that used in the Postgres documentation:
...
0
votes
1answer
26 views
How to repair corrupted PostgreSQL pg_toast error
The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I ...
2
votes
2answers
33 views
How to select blocks with sequential data and aggregate the ids
I have the following table:
id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int)
--------------------------------------------------------------...
2
votes
1answer
36 views
What's an “object”?
The Postgres documentation frequently uses the term "object". For instance, the descriptions of various \d commands at https://www.postgresql.org/docs/current/app-psql.html (like \dt for listing ...
0
votes
0answers
15 views
replication slot: FAILED (slot 'barman_the_backupper' not initialised: is 'receive-wal' running?)
I am running a postgres cluster with point in time restore using barman.
It stopped streaming WAL. it says slot "barman_the_backupper" not initialized. how can I initilize this slot?
I am getting ...
3
votes
1answer
32 views
Delete rule on view does not execute every statement
When I have more than one statements in a rule on a view, the second one is not executed in case the first one deletes the original entry. Even when the second statement does not have anything to do ...
0
votes
1answer
27 views
Optimize very simple Postgresql query (and understand Query Plan)
I have a DB with many tables created using the following structure :
CREATE TABLE table1 (id serial primary key, col1 real, col2 real, ..., coln real)
with about 50 columns and 400k rows. The tables ...
0
votes
1answer
42 views
Merge jsonb objects and sum their values over multiple rows
I save visits to my database. In my database I have a table with aggregations of visits called visits_agg. This table has one row per hour per hostname. Both are fields in the table which will be used ...
1
vote
1answer
32 views
Update rows in table with id from row before in PostgreSQL
Say I have a table like this:
| id | grp | time_added | previous_id |
|----|-------|------------|-------------|
| 1 | 1 | 5 | null |
| 2 | 1 | 8 | null |
| ...
1
vote
1answer
22 views
Streaming replication archive folder taking all space- Postgresql- 9.4
We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine.
We ...
