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

Questions tagged [postgresql]

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

0
votes
1answer
6 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
26 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 ...
2
votes
1answer
49 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
27 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
22 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
24 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
31 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
24 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
14 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
38 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
40 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
29 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
20 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
33 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
25 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
68 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
36 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
18 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
31 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
22 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
30 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
14 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
29 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
31 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
20 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 ...
1
vote
0answers
30 views

Restrict postgresql's public schema's tables visibility in tableua

My requirement is to display only one particular table or materialised view when user give db_host, db_port, db_name, db_username, and db_password to tableau. Currently tableau is showing all the ...
0
votes
0answers
25 views

Grant select on function gives “Error relation does not exist”, but function is defined in information schema tables and can be selected from [closed]

I have a strange situation. I have created a function using a super user role. When I select from that function using the super user, it returns data fine. When I try to grant select on the function ...
0
votes
1answer
16 views

How to know or recover my pg_dump password? [on hold]

I would like to backup my Postgre db using pg_dump. However, the first question asked by the prompt is the password. But I have no idea what it is. Indeed, it is different from my admin account ...
0
votes
1answer
45 views

Ensuring unique value for group of rows in table with trigger and concurrent transactions

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
1
vote
1answer
20 views

How to fetch those records who appears in many records in many to many relationship in PostgreSQL?

I have the following three tables: create table users(id int primary key, name varchar); create table groups(id int primary key, name varchar); create table user_groups(uid int references users(id),...
0
votes
1answer
20 views

Caching results of query for part of table that almost never change

In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below): Query1 ...
1
vote
1answer
23 views

Better way to select a matching subset of rows?

DB Fiddle link. I have a many to many table like this: CREATE TABLE house_to_cats ( id SERIAL PRIMARY KEY, house_id INTEGER, cat_id INTEGER ); -- house with cats 1 and 2: too small INSERT INTO ...
4
votes
0answers
51 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
-1
votes
1answer
30 views

Load a database into a host or in place to which I can access from different applications [closed]

I've just started working with databases. I create them and modify them using Python as a means to create dinamically a good amount of data. Moreover all the applications that I develop are written ...
0
votes
2answers
31 views

PostgreSQL: WHERE ID IN file_with_ids

I have a file with ids which looks like this: 10555 16452 17005 17511 17512 17513 17515 17516 17517 17518 17520 17521 17522 17523 17524 17525 This file was created with unix tools like grep and cut. ...
0
votes
1answer
24 views

Trouble updating column with matching data from another table (3 diff. attempts: 1 w/out errors but no results, 2 with errors I don't understand) [closed]

Table 1 is 'z_scrapestorm_rentals' and table 2 is 'pcpao_cols'. They both have address columns named 'address_1_line_trunc' and 'address_one_line' respectively. Table z_scrapestorm_rentals: column ...
0
votes
0answers
28 views

Postgresql 9.6 to 10 upgrade issues [on hold]

Trying to upgrade 9.6->10. I have run the following pg_createcluster 10 main /usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin/ -B /usr/lib/postgresql/10/bin/ -d /var/lib/...
0
votes
0answers
19 views

postgresql: live tuples not matching table row count

We're experiencing poor performance with a table. The table is regularly updated with insert/updates, and is a critical table in an application. In looking into the problem I've noticed that the row ...
0
votes
0answers
19 views

PostgreSQL decrease performance after migration

After postgresql database server v9.3 migration to v9.6 I noticed a decreace in the performance of the entire system. The config parameters are the same as in v9.3 taking in to account the next ...
1
vote
1answer
54 views

What is the status of the PostQUEL source code?

Recently as part of my answer to "Is the keyword “ALIAS” actually used?" I wanted to go back further than Postgres95. Postgres95 is the first version of Postgres that was migrated to use SQL rather ...
8
votes
2answers
1k views

Is the keyword “ALIAS” actually used?

According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved ...
1
vote
3answers
76 views

PostgreSQL: Correct way for subset calculations

I have a huge table (around 10 million items). For simplicity, let's say it has only 2 columns: user_id and activity_id like this user_id | activity_id --------------------- 1 | 1 1 | 2 1 ...
0
votes
0answers
14 views

Failed to populate indices of table table_name

I need to design my PostgreSQL database and I use SQL Power Architect to do it. But after I import it to SQL Power Architect and drag the table to workspace, I got error message : Failed to ...