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
6 views

PG - Select a record not reserved yet

I have a PG (12) database with two tables: RESERVABLES id uuid RESERVATIONS id uuid reservable_id uuid reserved_between tsrange Where reservations....
0
votes
0answers
2 views

PostgreSQL rans out of memory in the middle of query

I am running a PostgreSQL 11 database in AWS RDS in a db.t2.xlarge instance (4 CPU 16 Gb RAM) with 4 Tb of storage. My query is based on a fairly large table (48 Gb -- 243.711.955 rows), but nothing ...
0
votes
0answers
3 views

Backing up Time Series Database (PostgreSQL/TimescaleDB) where old data will never be updated

If a PostgreSQL (using TimescaleDB extension) database that holds time series data is continuously growing in size, what is the recommended backup strategy if we do not want to continuously create ...
0
votes
0answers
6 views

Fuzzystrmatch Postgresql similarity func with array

How to pass a array to the SIMILARITY ()function Something like that SELECT denomination, is_active FROM MyTable WHERE SIMILARITY(denomination, ['Coke', 'Coque']) > 0.4;
0
votes
0answers
18 views

Readonly table permissions not applying to new tables

I have a postgreSQL-11 database that get's a new table created every Monday as part of a DW load. However, when that new table is created, the [readonly] role that's been created doesn't get SELECT ...
0
votes
2answers
27 views

Postgresql low performance with max and min (wrong index?)

I'd appreciate some help here, I'm new with Postgresql and don't fully understand what's happening here. I have the following table: history=> select * from last_sales.store01 limit 5; ...
2
votes
1answer
2k views

pgAgent not working on linux (Ubuntu 14.04)

I am running Postgres 9.3 on Ubuntu 14.04 and I am unable to get pgAgent daemon/service to run. I have confirmed that the daemon is not running using service --status-all. When trying to run ...
0
votes
0answers
14 views

Json_agg slow down the performance

I have a database, simulate a structure of a document, like this table document: -------------------------------------------------- | Id | ------------------------------------------------- | 1 |...
0
votes
0answers
31 views

Foreign Key fails PostgreSQL

I have these table definitions in PostgreSQL 12: CREATE TABLE paleositio ( oid integer DEFAULT NEXTVAL('paleositio_oid_seq') PRIMARY KEY, acronimo varchar(100) ...
0
votes
1answer
775 views

Postgresql - archive_command Windows, not working

I'm attempting to set up archiving on a windows server machine but when I check the logs nothing is happening. The postgresql.conf file this is my input: #wal_level = replica #archive_mode = on #...
-1
votes
1answer
17 views

Perform TABLESAMPLE with WHERE clause in PostgreSQL

I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL. This runs fine: select * from customers tablesample system (1); But I cannot figure out how to ...
1
vote
1answer
14 views

Multi language full text search using postgresql

I am trying to implement full text search using postgresql for some images. I am storing some information about my images into a json field in my table. This json has a tags key where I have multiple ...
1
vote
1answer
34 views

Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field

I am fairly new to SQL so apologies if my terminology is incorrect. I am looking to summarise a number of measurable's from "Table 2" but in the example below I have only included 1 for ease. I would ...
0
votes
0answers
14 views

Incremental replication of an Oracle database to a PostgreSQL replica

I have a third party system using Oracle 12C database to which I need to do data analytics. For safety I don't want to do it to the production database server. For economical reasons I can't run ...
0
votes
0answers
11 views

How to share one postgresql database between multiple opennms installations which don't run concurrently

I have multiple opennms installation on different file systems which access the same postgresql 9.6 database for opennms. The accesses are never concurrent. Unfortunately, each installation displays ...
1
vote
1answer
682 views

Access denied error when archiving WAL during postgresql 9.5 replication setup

I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video. For archive_command I use this: archive_command = 'copy "%p" "\\\\ip-address of ...
0
votes
1answer
27 views

How to split one table into two in a single statement?

I've got an old table I want to split into two, but I'd rather not run two near-identical queries across the whole thing. The following does not work because you can only return column from the table ...
5
votes
2answers
2k views

How to exclude tables when using pg_dumpall with postgresql?

If use pg_dump, it has a --exclude-table-data option to ignore special table. But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?
0
votes
1answer
16 views

How to transfer postgres databases from one server to other

VPS server has old Debian 6 Squeeze with Postgres 9.1 It has 24 databases. Every night backup copies are created using pg_dump to /root/backups directory for every database. This directory has 24 ....
0
votes
1answer
328 views

MS Access - Creating a report based on a pass-through query that requires a parameter

I am attempting to make a report in Access that uses parameters as well as a pass-through query. I'm open to using a temporary table but the guides I can find using temporary tables all use an ...
2
votes
1answer
546 views

Why does postgres choose to sort instead of scanning the index?

I have the following table: # \d service Table "public.service" Column | Type | Modifiers -------------+----------+----------- customer_id | integer | not null date | date ...
5
votes
3answers
5k views

How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?

I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege. db=> create role replicator login password 'something'; CREATE ROLE db=> alter ...
1
vote
1answer
26 views

How to create a column with rows in postgres?

I want to create a table that withholds an array of values inside one column in table. Example in JSON form: "trainNumber": 1, "departureDate": "2020-01-26", "operatorUICCode": 10, "...
0
votes
1answer
14 views

How to select fields using alias name?

I have an select: SELECT tb_dim_equipe.no_equipe "EQUIPE", tb_dim_profissional.no_profissional "PROFISSIONAL", no_cidadao "CIDADÃO", a.nu_cns "CNS", sum(case when co_dim_tempo >= 20190100 and ...
0
votes
1answer
17 views

pg_dump - table dependencies when using --table

The documentation says this when using -t table: Note When -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon. Therefore, ...
0
votes
1answer
31 views

How to perform schema migration from array to many-to-many to lookup table?

I currently am storing values in a column of type array like follows (this is just an example): CREATE TABLE blog_posts( id INTEGER PRIMARY KEY, blog_title TEXT, tags TEXT[] ); With the ...
1
vote
2answers
48 views

Is it better to have a CASE expression or a JOIN with a dimensional table?

I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this: SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday' WHEN date_part(...
-1
votes
1answer
26 views

How to return value from function in PostgreSQL [closed]

I have a function in postgreSQL, I want that if the query in function executes properly then it should return true else false example: CREATE OR REPLACE FUNCTION enquiry."fn_queryFunction"( IN ...
155
votes
5answers
390k views

Granting access to all tables for a user

I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all ...
1
vote
1answer
24 views

Unable to deny remote access to Postgresql even if there is no entry in pg_hba.conf [closed]

I have 5 Postgres Servers, with IP addresses let's say as A,B,C,D and E. A is the master server and remaining are the child servers which need to access A. In the postgresql.conf file of A, the listen ...
0
votes
2answers
17 views

PostgreSQL 11 trigger can't select a value in a table to use it in a second table

Here's a sample of log : gid alti numlog 1 79.55 1 2 79.65 2 Here's a sample of us : gid numlog numus sommet base alti_sommet alti_base 1 1 1000 0 0.25 (79.55-0) (79.55-0.25) 2 ...
2
votes
2answers
5k views

unable to start psql : no pg_hba.conf entry for host “::1” , user “postgres”, database “postgres”, SSL off [closed]

I am unable to connect using psql to PostgreSQL 9.6 on Windows 10. I saw almost all the StackExchange questions regarding this error. While installing Postgres it asked for superuser password and I ...
1
vote
0answers
24 views

HIVE + understanding the hive-metastore logs

we have HDP cluster version - 2.6.4 , and we are runs spark streaming app and we are uses presto cluster in order to run Hive queries when we look on the hivemetastore logs ( under /var/log/hive ) ,...
0
votes
0answers
15 views

multiple pg_dump

My Postgres 9.3 cluster has 9 databases. I want to take backup of these databases using pg_dump utility of PostgreSQL 10. Since individual dbs are too large in size (~350GB), I want to run pg_dump ...
0
votes
0answers
18 views

Recover corrupt archive log file in postgres [closed]

I am using postgreSQL 11. I want to know how can I recover the corrupt archive log file available in postgresql.
0
votes
1answer
29 views

Listing function input and output details for stored functions in Postgres 11.5+

I'm trying to build a query that extracts the input and output definitions for the functions in a schema. We've got multiple client types written in multiple languages, and plan to move many queries ...
0
votes
1answer
21 views

Limiting user access by subnet to Amazon Aurora PostgreSQL

How do I limit user access by IP? For example, assuming I have two non-superusers (user1 and user2), how can allow general access (only limited by security groups) to the instance/cluster by user1 but ...
0
votes
1answer
452 views

barman + postgresql not start after barman retore

I use the barman for backup and restore after some restore , we start the postgresql as the following systemctl start postgresql.service but postgresql not startup from the log: /var/lib/pgsql/...
0
votes
0answers
16 views

How to create user(read-write & readonly) and admin roles for an existing postgres database?

We have an existing postgres database in production with a superuser adm that is being used to do everything. Our web application connects to the database using the same user and also the ...
4
votes
1answer
1k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic. I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
5
votes
2answers
461 views

What is the `TABLE example` syntax called?

In PostgreSQL, I've been using syntax like TABLE example1 UNION ALL TABLE example2 which I understand to be equivalent to SELECT * FROM example1 UNION ALL SELECT * FROM example2 But a colleague ...
0
votes
1answer
16 views

Prevent overlapping TSRANGE

I have the following PG (12) reservations table id uuid reservable_id uuid reserved_between tsrange How can I ensure there are no 2 reservations for the same reservable_id ...
1
vote
1answer
100 views

PostgreSQL unique continue on violation

I am creating ETL where I rely on the database to refuse duplicate values for a business key. In SQL Server this works like a charm. The database gives an error, the ETL ignores the error, continues ...
-2
votes
2answers
110 views

Wrong Output(Null values) for a column

I am unable to get data from bnrcno column while using the below query. select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||...
5
votes
1answer
2k views

ERROR: improper qualified name (too many dotted names) when trying ALTER FUNCTION

Working with Amazon Aurora Postgres Version 2.0, which is basically PostgreSQL 10.4 with a modified storage layer. Trying to restore a dump that contains multiple lines like: ALTER FUNCTION ...
0
votes
2answers
31 views

How to completely restrict a postgres role to a single schema

I'd like to create a readonly role for a third party to access a handful of tables from the public schema without being able to view the rest of the tables in public. My initial idea was to create a ...
0
votes
1answer
91 views

pg replication connection not returns wal events to java code client , it hangs some times

Describe the issue We are facing issue of pg replication slot not listen database wal events. Once pg database restarted and we attach Driver Version: 42.2.6 Java Version: JDK1.8 OS Version: Window ...
1
vote
1answer
46 views

How to denote separate records as “one and the same”?

Let's say there are two separate Person records V. Baker Vincent Baker each with their own various associations. These could be things like photos, contact information, etc. And let's say that it ...
0
votes
1answer
86 views

postgres performance tuning - what to try next?

I have a simple (but large) postgres database. I have a table defined like this…. CREATE TABLE TABLE_A ( MessID SERIAL UNIQUE, rawData CHAR(250), loadedTime TIMESTAMP)...
0
votes
2answers
39 views

Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?