Stack Exchange Network

Stack Exchange network consists of 174 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
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql]

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

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 ...