Questions tagged [postgresql-12]
Specifically for PostgreSQL version 12
143
questions
0
votes
0answers
9 views
BUCARDO - PL/Perl function “validate_goat” at /usr/local/bin/bucardo
I'm new to Bucardo, I have been trying to replicate PostgreSQL-12 using Bucardo with no success due to the errors.
DBD::Pg::st execute failed: ERROR: DBI connect('dbname=test1','bucardo',...) failed:
...
0
votes
1answer
16 views
removing dates from generate_Series in postgreSQL
im trying to remove some dates from generate_Series in postgres liie this:-
select min(dt)+'1 day' from generate_series(date_trunc('day',now()) , date_trunc('day',now()+'30 day'),'1 day') as s(dt)
...
0
votes
0answers
14 views
Postgres jsonb field: remove an element from an array by the element id
Figure a Postgres 12.5 jsonb field containing the following:
[
{id:1, name: 'one'},
{id:2, name: 'two'},
{id:3, name: 'three'}
]
How to write the request that will match: "drop the object ...
1
vote
1answer
108 views
Adding a WHERE clause slows down query
We run a database on
PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1, 64-bit
We are trying to get the top rows of a result set where the most recent entry has a ...
11
votes
3answers
3k views
Why is an OR statement slower than UNION?
Database version: PostgreSQL 12.6
I have a table with 600,000 records.
The table has the columns:
name (varchar)
location_type (int) enum values: (1,2,3)
ancestry (varchar)
Indexes:
ancestry (btree)...
2
votes
0answers
27 views
PostgreSQL Replication disconnects after deleting DB
I've got a PostgreSQL Replication set up using Repmgr.
Replication works fine, until one of the databases is deleted on the master node.
Standby replica throws off 'WAL cannot be found' error and ...
2
votes
1answer
44 views
Canonical replacement for OIDs in PostgreSQL 12 onwards
I have an application which relies totally on PostgreSQL OIDs for its operation. It's currently at PostgreSQL 9.6.1, but I'm looking to upgrade to 13. Is there a recommended/standard mechanism (using ...
0
votes
1answer
18 views
Is it possible to exclude a database from WAL and basebackup?
I'm planning to add a GIS database to an existing PostgreSQL 12 cluster with multiple databases, streaming (hot standby) replication and WAL archiving.
Once created the new database will never be ...
0
votes
0answers
10 views
Constraints number is differents after migrating from postgres 9.6 to 12.5
I have 2 postgres versions : 9.6 and 12.5
I have loaded the same database in them ( the famous dvdrental database)
I was checking the differences between the 2 databases running multiple queries for (...
0
votes
1answer
22 views
Exclusion constraint for text values [closed]
I have two tables. A client table and a vehicle table, the two are linked in a unit lookup table.
My constraint is that a vehicle cannot belong to a client if it already is associated with another ...
0
votes
0answers
43 views
PostgreSQL remote connection failing
I've recently installed PostgreSQL on my PC as a requirement for my DB class, and as a practice, I decided to try and setup a remote connection for it so I can access it via online services. But after ...
0
votes
0answers
31 views
PostgreSQL database design concerns
I am having a hard time deciding what's the best approach for my database design (I am using PostgreSQL 12)
We have customers, each customer has a fleet of vehicles, each vehicle is identified as ...
1
vote
1answer
37 views
Different (much worse) query plan when using one view
I am running the following query:
SELECT m.entityid,
m.alias::text AS alias,
m.measurement_ts,
m.volume_m3::double precision AS volume_m3,
m.pressure_mca,
m.battery_volt,
(m....
0
votes
1answer
26 views
In postgresql.conf, does the order of the listed parameters matter?
I'm relatively new to the DBA field and I have read the official documentation/guides on this topic, but I apologize if my question seems weird.
I have deployed PostgreSQL 12 on my AWS instance and ...
0
votes
1answer
34 views
postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third
Lets say that tableA has column columnA with jsonb data in it like the following:
{
"A": 5,
"B": 15,
"C": 20
}
and tableB has columnA with jsonb data in it like ...
-1
votes
1answer
28 views
TimescaleDB wildcard (%) slow
I have a timescaledb hypertable like this:
create table logs
(
time timestamp not null,
partitionkey text not null,
ip inet,
raw text,
transformed ...
0
votes
1answer
66 views
PostgreSQL Continuous Archiving & Point-in-Time Recovery in combination with netbackup
I have the following setup:
OS: CentOS
PostgreSQL 12
Netbackup 8
What I want to achieve:
Configure continuous archiving with the possibility of Point-in-Time Recovery
My current idea:
Generate a ...
0
votes
1answer
40 views
Multiple aggregations in select slow on postgres
I have a table with columns: id, antenna_id, latitude, longitude. There are two composite indexes on (antenna_id, latitude) and (antenna_id, longitude). When I do a max(latitude) for a specific ...
0
votes
1answer
20 views
Query to get count of inserted records hourly wise in postgresql?
I have a table that looks like:
Table name Demo
id creation_date
1 2021-03-06 12:27:48.811
2 2021-03-06 03:56:48.875
3 2021-03-06 03:58:23.567
I need the count of ...
0
votes
1answer
21 views
Poor execution plan leads to query time increasing dramatically
I have a relatively complex query like the one that follows
select * from (
select
distinct on("car")
entity.id as "car",
metric as "last_visit"
...
0
votes
0answers
18 views
LOCK TABLE public.schema_migrations IN ACCESS SHARE MODE when executing pg_dump
I have a cron job that execute a backup on all databases on the sever. The pg_dump gets stuck in one database/table. Tracking the issue on pg_status_activity table I got:
Message:
LOCK TABLE public....
0
votes
1answer
50 views
Convert SQL server stored procedures to postgreSQL
I have a SQL Server database that I am migrating to PostgreSQL 12.0.
I have managed to migrate the schema and the tables. The applications connecting to the database rely a lot on stored procedures, ...
0
votes
1answer
78 views
Unable to connect to postgres inside a docker container
I've started a new postgres:12 docker instance as:
$ docker run -it -d --name newdb \
-p 5444:5432 \
-e POSTGRES_PASSWORD=postgres \
-e POSTGRES_USER=postgres \
--volumes-from dbstore \...
0
votes
1answer
27 views
Conditionally insert a value in an array when creating a new column
I have some data in a jsonb col that I need to clean and insert into another text array column.
What I have:
[
{
"v": "birthday"
},
{
"v": "reference&...
1
vote
4answers
52 views
PostgreSQL query takes a long time (as it's not using the index)
This query takes a long time, as I guess it's doing a full-table scan. It works ok in Oracle, just not in Postgres 12.
select count(*)
FROM EVENT
WHERE PK_EVENT > (select CHECKPOINT1_PKEVENT FROM ...
0
votes
1answer
599 views
How to prevent pg_dump from generating “SET default_table_access_method=heap;”?
I have a big dump file that is generated by pg_dump that comes with PostgreSQL v12 client, and it is in customized format instead of plain sql text. The version of the server is PostgreSQL v12 too.
...
1
vote
0answers
146 views
PostgreSQL 12.5/ pgAdmin - postgis. Error: column p.proisagg does not exist [duplicate]
pgAdminIII reports errors related with a column missing after installing postgis in postgres 12.5 and ubuntu 20.04
Error column proisagg does not exist where perhaps you meant pr.proisagg
people ...
0
votes
0answers
15 views
Trigger PostgreSQL replication
Is it possible in Postgresql 12 to set up master/slave servers so that the replication has to be manually triggered? The database is large, so it’s not feasible to dump and import. The optimal would ...
0
votes
0answers
33 views
I need a script to cycle all tables in postgres to analyze if needed
I need a script to cycle all tables in postgres to analyze if needed.
Something like:
create or replace PROCEDURE analyze_all_tables ()
language plpgsql
as $$
declare
q TEXT;
r RECORD;...
0
votes
1answer
152 views
postgresql copy comand with date in filename
I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12).
COPY
(SELECT * FROM mytable)
TO 'C:\Users\me\mylocation\filename_date_time.csv' ...
1
vote
1answer
29 views
TimescaleDB distributed setup strategy
What is the best setup strategy for TimescaleDB?
My initial thoughts based on the TimescaleDB FAQ was that it is indeed recommended and possible to basically have one database that contains both time ...
1
vote
1answer
400 views
Postgres COPY with on conflict ignore - possible?
I want to add on conflict ignore to the Postgres copy command.
I know I can copy the data to a table without unique index /primary key and then use insert with the on conflict syntax.
But I wanted to ...
-1
votes
1answer
32 views
if multiple users run a query that creates a temp table at the same time, would the data in the table be corrupted in each session? [closed]
I am using postgres12. I have a query that creates a temp table named table_x and returns it. I am wondering if this data will be corrupted since there is multiple users running the same query at the ...
0
votes
1answer
67 views
PostgreSQL extended statistics
I have a 1 TB read-only database where performance is critical. It's difficult to predict queries since they are dynamically generated by the users (the whole thing is basically a visualization ...
0
votes
1answer
86 views
Using function is much slower than running query
I am trying to write a plpgsql function that runs a query where the WHERE part is variable using EXECUTE, like this :
CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$
DECLARE
q ...
-1
votes
3answers
103 views
Is there really no SIMPLE way to achieve “recursive lookup” in PostgreSQL?
I have a table of categories:
id,label,parent_id
1,Animals,null
2,Dogs,1
3,Cats,1
4,Black,3
This means that "Animals" is a top-level category, and "Dogs" and "Cats" are ...
1
vote
1answer
127 views
How to resolve “logical replication target relation schema.table is missing some replicated columns” error in postgresql logical replication?
I'm getting
"logical replication target relation schema.table is missing some replicated columns"
error in logical replication. I'm using all table replication. Due to some changes I ...
0
votes
1answer
186 views
pg_restore importing single table to a file
I have a backup in directory format taken with pg_dump.
When I try to export single table from this dump with the command:
pg_restore -Fd mydirectory -t sometable > table.sql
I get the error :
...
0
votes
1answer
33 views
Geospatial join using ST_Contains or ST_Within seems slow, with costly query and bad estimate
Problem
I have two tables, one which which includes a point column and the other which includes a boundary (table definitions below). I want to select rows from the first column where the point is ...
0
votes
2answers
114 views
Unpacking JSON into 14 columns without crosstab() and tablefunc?
In a Postgres 12 database, I have a simple schema containing a JSON column. The JSON will always contain an object, and inside the object there are always 14 keys like so:
CREATE TABLE json_unpacking ...
1
vote
1answer
66 views
INSERT with ON CONFLICT does not insert rows as expected
We have two tables:
CREATE TABLE listing_master (
id SERIAL PRIMARY KEY,
feed_id character varying(20) NOT NULL,
feed_listing_id character varying(20) NOT NULL,
updated_time timestamp(...
0
votes
0answers
7 views
Postgresql table lock to update constraint
I have a table in PostgreSQL 12.
Some data are not formed properly so I need to introduce a check constraint so it doesn't happen again. I have a running system that updates this data continuously and ...
0
votes
1answer
163 views
Drop tables but space not claimed in postgres 12
I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option.
So basically I am having two data directories i.e. One is old data directory (v9.5) ...
0
votes
3answers
78 views
Postgres 12 drastically slower than postgres 9.5 with identical queries from import/export
I setup a new postgres database (v12) by export/importing from an old one (v9.5).
When I query the new one, it's drastically slower despite being on a more powerful machine. Shared buffers are set to ...
0
votes
0answers
19 views
How can I write a query avoiding intermediate views/steps from different tables in PostgreSQL?
I am working in a large query I would like to eliminate intermediate steps, so I am trying to write the two queries below in just one.
The first query (QUERY 1) select the grid id from a table called ...
1
vote
2answers
179 views
Optimizing postgres sorting by word_similarity
I have a table with 2M rows of hotel names in Postgresql 12. I am building a typeahead so users can quickly find the hotel by name. I have played around with different Postgres options including FTS, ...
1
vote
1answer
47 views
REPMGR - change reconnect_attempts and reconnect_interval not working
I configured PostgreSQL automatic failover with REPMGR. But, in my simulation the sleeping time is 1 second for each reconnect attempt:
If I consult official documentation the default value to ...
0
votes
3answers
74 views
Sort varchar by its numeric fields
I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer.
I have an orders table with a varchar field for the order ...
0
votes
0answers
14 views
How do I enable pgadmin4 (PostgreSQL12) to read and write files in directories of other Linux Users on RHEL8/CentOS8 [duplicate]
While trying to export a query result set from PGAdmin4 on PostgreSQL12 to a CSV I am encountering the following error:
ERROR: could not open file "/home/linuxusername/Downloads/data.csv" ...
0
votes
0answers
70 views
CentOS 8 Unable to connect to PostgreSQL 12 Server
When I attempt to connect to postgres in from my linux command line, I receive the following error:
[lloyd@localhost ~]$ sudo -u postgres psql
could not change directory to "/home/lloyd": ...
