Questions tagged [postgresql]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
46 views

Postgres_fdw Transaction isolation issue

My case I have connected to another GP DB to import data into my PostgreSQL tables and written Java schedulers to refresh it Daily. But when I'm trying to fetch the records everyday by using SQL ...
0
votes
1answer
50 views

Prepare statements plan invalidation in postgresql

Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-...
0
votes
1answer
21 views

Is there a way to search the earliest or latest postgres partition for table?

BACKGROUND I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good. However, based on the data I'm querying, we don't have a ...
1
vote
1answer
33 views

Postgres How to securely initialize the database?

I'm initializing a Postgres DB and would like to know what is the best way to do it. Objectives: 1. We would like to use default database (postgres) unless there's a strong reason not to do. 2. We ...
2
votes
1answer
34 views

How to reduce foreign key performance impact in PostgreSQL

Foreign keys add significant overhead...more than +600% in the example below (Docker PostgreSQL 11.4): CREATE TABLE example_1 (a int PRIMARY KEY); CREATE TABLE example_2 (a int NOT NULL REFERENCES ...
3
votes
2answers
731 views

Which index type is best suited for exact-match against a list of values?

I have this table: CREATE TABLE public.foos ( id integer NOT NULL, bar_string character varying ); I want to do this query: SELECT id from foos where bar_string in ('one', 'two', 'three') ...
3
votes
2answers
1k views

How to refer to individual columns of a record returned by a function?

I have a function that returns setof record type. CREATE TYPE CIR_TYPE AS ( ID integer, path text, cycle boolean ); CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $...
0
votes
0answers
23 views

Storing earth observation derived attributes for polygons

I want to generate a number of vegetation related indices (NDVI, NDTI, etc., min/max/avg/std (pixel values)) from earth observation data for a large number of polygons during the entire growing season,...
0
votes
1answer
22 views

Append JSON array to an object in PostgreSQL

I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure. +----+------------+----------+----...
0
votes
1answer
29 views

Unable to restore a PostgreSQL database from a .backup file

I am using Ubuntu 18.04 and PostgreSQL. For the GUI, i am using pgadmin4 (browser version). I created a new database and when trying to restore, the following error message comes up: <!DOCTYPE ...
0
votes
0answers
12 views

How to store Postgres password in configuration hibernate.properties

what is the correct way to provide PostgreSQL password in hibernate.properties(encrypted or plain). If encrypted, who is responsible for encryption and decryption Please post any doc which is helpful. ...
0
votes
1answer
18 views

How to verify data integrity of data folders after data recovery in MySQL and PostgreSQL?

I wonder if there is a way to verify data integrity of data folders from a data-recovered hard disk, both MySQL and PostgreSQL?
2
votes
1answer
125 views

Implications of many materialized views in Postgres?

I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be ...
0
votes
1answer
27 views

What is the proper way to relate many to many?

I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
19
votes
2answers
11k views

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
0
votes
1answer
124 views

CHECK constraint to limit certain rows

I have the task to create an EAV table with some constraints. Attributes are foreign keys and some values are also from other tables (not realized as foreign key, but as a CHECK constraint). On this ...
1
vote
1answer
166 views

Does bool_and short-circuit function calls with arguments?

Suppose I have the following query: SELECT a.* FROM a CROSS JOIN b GROUP BY a.id -- Primary key HAVING bool_and(expensive_comparison(a, b)) What will PostgreSQL do with this query? Will it process ...
0
votes
1answer
14 views

How to install pgstattuple extension in postgres 10.6?

Can someone let me know how to install pgstattuple extension in postgres 10.6 running on Ubuntu 16? I have installed postgres-contrib and can see the following .sql files under extension folder ...
0
votes
1answer
108 views

Schema replication Postgresql AWS

Is there a way to replicate Postgres Schemas in AWS into one location for reporting purposes? I have 3 postgres databases (up to date with current version) running in AWS that I would like to ...
1
vote
1answer
14 views

repmgr 4.4 upgrade

I did apt-get install repmgr and tried to upgrade repmgr from 4.3 to 4.4 root@node-01:~# apt-get install repmgr Reading package lists... Done Building dependency tree Reading state information... ...
4
votes
0answers
41 views

PostgreSQL 10 backend processes

I have a particular issue with PostgreSQL 10: Postgres creates backend processes for each connection on different ports. The problem is that some of our apps also use many ports and sometimes they ...
0
votes
1answer
33 views

PostgreSQL CREATE TYPE using multiple relations?

I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax. It appears to me that this type does not ...
1
vote
0answers
14 views

Syntax Error on Set Clause in Update Query [closed]

This seems like a simple query, but currently it returns a syntax error: UPDATE tablename SET description = "a nice description" WHERE tableid = 2; The error I get says: ERROR: column "a nice ...
2
votes
1answer
16 views

pg_restore “CREATE CAST” not executed if using public or pg_catalog schemas

While moving database to another instance I'm using pg_dump and pg_restore with intermediate file. Dump file was created like this: pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$...
0
votes
2answers
25 views

Best Way To Create Time Series from Postgres Table Update History? [closed]

I have a Postgres Table (500 rows, 10 columns) that gets updated every 6 hours by a Daemon (that performs some calculations) and executes a series of row-by-row upsert operations. How can I construct ...
-1
votes
0answers
15 views

Is there a postgres extension or tool to dump out all objects in a database into a set of files? [closed]

In our development process we use flyway to create a sequence of 'migrations' (change scripts) that are applied in order to our database. This mirrors nicely the deployment process. However, when we ...
1
vote
2answers
1k views

Keeping version history of functions in PostgreSQL

For many of my tables, I've added auditing triggers (based on this wiki page). It works very well and has helped me several times figure out who changed what in the system. We have a Python/Django ...
3
votes
3answers
328 views

Postgres trigram match acting strange for specific characters

I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query: SELECT my_column, similarity('$ Hello', my_column) AS sml ...
1
vote
2answers
15 views

How to query a postgresql database to determine it's cluster and version (and port)?

I can quickly query the database name with SELECT current_database(); but I've been searching for way to also query the cluster name and version with no luck - hoping someone can suggest a solution.
-1
votes
0answers
18 views

CONTEXT: PL/Perl function “remote_select”

I am trying to run my view and getting following error. CONTEXT: PL/Perl function "remote_select" View SELECT remote_select.client_key, remote_select.source_system_key, remote_select.date,...
0
votes
0answers
18 views

General pattern to dedup records

I'm storing a large amount of time series data/value (varchar) with timestamp for a set of parameters (varchar) in PostgreSQL. I'm looking for a way to avoid storing duplicate if the value is the ...
0
votes
0answers
17 views

Possibility of using self-referential foreign key to create hierarchical comments

I am an accidental DBA and have a question regarding the table structure of a website I maintain (postgresql 9.6 backend). Imagine a typical content website (let's call it 'Links'), where users post ...
0
votes
1answer
215 views

Postgresql JSONB Column, how to query for multiple specific elements in a JSON array

Giving the following JSONB data structure of a stopInfo column in a myTable table: { "stopInfo": [ { "stopType": "Origin", "partnerId": "KR01", "locationId"...
1
vote
2answers
20 views

Only one non-nullable value across 3 columns

I have 3 "Nullable" columns. I am looking for a way to restrain it so that: One of the three has value, the other two columns must be null All three cannot be null Is it possible to do this? I ...
1
vote
2answers
19 views

How do I know if my PostgreSQL is up do date with DST rules?

Recently (2019-04-25) brazilian government ended the use of daylight savings time all around the country. In my current project I rely on PostgreSQL to do timezone conversions and analytics based on ...
1
vote
1answer
316 views

Improve Performance on GROUP BY - large table PostgreSQL

I have a Postgres table containing the following three columns: origin (INT, b tree index) destination (INT, b tree index) minutes (SMALLINT, b tree index) The table has ~ 1.4 billion rows. Each row ...
1
vote
2answers
1k views

Postgresql: what are the disadvantages of unlogged table

To make update faster i am using below command. ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ; I want to know what are the drawbacks of this command.
2
votes
1answer
132 views

Postgres 9.6 AWS RDS loadbalancing

I have an legacy application that has a number of very complex queries that take significant time and resource to execute. Rather than rewrite the application I am looking at the possibility of doing ...
3
votes
1answer
2k views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
6
votes
1answer
3k views

Creating UUID extension as the non super privileged user

I need to load the uuid extension on my database, which is being created in runtime. The user that created the db is not superuser, she only has the "CREATEDB" permissions. Is there a way to do this? ...
1
vote
1answer
4k views

Do I need to execute “create extension pgcrypto” everytime?

I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart....
3
votes
1answer
4k views

Postgres, levenshtein distance and nested queries

I have a DB Table with 3,000,000 rows or more like that: |ID|T4|T16|T64|TL where ID =INTEGER (PK) and T4,T16,T64,TL are text fields. Each one is larger in length than the previous, i.e., LENGTH(T4) ...
0
votes
2answers
25 views

Why does PostgreSQL FDW talk to multiple backend servers for this point query?

I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers: CREATE TABLE my_big_table ( user_id bigint NOT NULL, serial bigint NOT NULL, -- ...
2
votes
1answer
150 views

How to influence the planner on Postgresql

I have the following: a complex query, 'match', that returns about 200 rows (ids) a huge table, 'usernames', indexed on id that maps them to usernames When I try to left join the query (1) with the ...
0
votes
1answer
355 views

Update postgres array jsonb

I'm using postgres 9.6, and my question is about updating a jsonb array column. My table looks more or less like that: ID Name MyArray 5 John [{"key":1,"key2":a}] 6 Mike [{"key":4,"key2":b}] ID ...
1
vote
1answer
26 views

Inspecting the status of a long-running COPY statement

I need to migrate a table from one PostgreSQL database to another. There was a chance I would need to fix some data, so I exported to a CSV. Then I imported the CSV in the second database with a COPY ...
-1
votes
1answer
27 views

Advantage of 'with' queries over plain 'select' and joins?

Are there any advantages using WITH queries instead of simple SELECT queries using JOINs performance wise? I am asking solely about reading data (from catalogues specifically).
0
votes
1answer
23 views

Multiple THEN outputs on a PostgresQL query

Helllo there, I've been playing around with a materialised view, and my goal is to pre-process as much information as I can, aggregating the info into this view that would speed up the data analysis. ...
0
votes
1answer
107 views

PostgreSQL query suddenly very slow after new column addition

I have a query that's performing terribly after adding a new column. Here's the slow version: SELECT COUNT(*) FROM activities INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."...
0
votes
1answer
35 views

How to set order for constraints to be checked in Postgres

I have a table with 2 constrains like below. ALTER TABLE entity_instance ADD CONSTRAINT instance_name_uniq UNIQUE (tenant_id, name) ALTER TABLE entity_instance ADD CONSTRAINT instance_source_uniq ...