Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
9,303 questions
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 ...
