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
-2
votes
0answers
17 views

Postgres Restore

I need to restore a single schema from production postgres to test. Test database and schema already exists. The users/roles & sequences are different on prod to test and after schema is ...
0
votes
1answer
26 views

Disallow superusers and single-user mode in POSTGRESQL

I have a doubt about superusers and single-user mode. I want to restrict the access to a certain database, in a way that only those users which I gave privileges can see the structure of my database....
0
votes
2answers
31 views

How can I obtain the last inserted row with INSERT … SELECT in PostgreSQL?

I'm trying to batch-copy data in a specific order from one table to another in PostgreSQL 12-beta2. The table is not using sequences, but contains an composite unique Primary Key (user_id, object_id). ...
0
votes
0answers
10 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
0
votes
0answers
11 views

Postgresql HA using patroni

We are using master-slave configuration for our PostgreSQL DB with streaming replication. But now we want to setup high availability using Patroni. I have configured etcd, HAProxy and two Postgres ...
0
votes
2answers
10 views

Create integer id columns from existing string columns (integer coding?)

I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this: | ID_STRING_A | ID_STRING_B | | 'AAAA' | 'BBBB' | | ...
0
votes
1answer
16 views

How to count number of occurrences for all different values in database column and obtain 0 if are there empty result?

I'am newby with postgresSQL. I have a table with this structured: city | type | quantity | sellout | sellout_update "ACIREALE" | "Man" | "3" |"1100.0" |"366....
5
votes
1answer
196 views

Where are database specific settings stored?

Postgres documentation says ALTER SYSTEM writes the given parameter values to the postgresql.auto.conf file, which is read in addition to postgresql.conf. I am curious about where the alter database ...
0
votes
1answer
29 views

Problem with How to join with subquery

I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
1
vote
1answer
204 views

Have several 128 elements lists, need to obtain distance between them

Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way? I can not save them as a list of floats, since i get: "can't adapt type numpyndarray" Can ...
0
votes
2answers
32 views

How to convert UPDATE operation into INSERT using triggers

Given I have a table User with column status (active/inactive) these users may be attached with some other entities, so I do not want them to get modified unless explicitly specified. and for ...
1
vote
1answer
34 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
1
vote
2answers
321 views

Empty table after pg_restore

I have a backup of an unlogged table called potential_users, the relation has 58677237 records in it. Steps: pg_dump --format custom --verbose --file "potential_users.backup" --table "public....
0
votes
2answers
101 views

Elements inside a container: How to perform atomic operations on them?

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null). Each element ...
0
votes
1answer
155 views

Can I delete a postgresql-9.x-main.log in a running cluster?

In a debian 8 box I got a PostgreSQL 9.4 up and running in production environment. Recently I found that the postgres-9.4-main.log is larger than 38 GB. I have adjusted the postgresql.conf ...
274
votes
4answers
475k views

How do I list all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. It's a 3rd party product, they are slow on answering some questions, and I know the data is lying inside ...
3
votes
1answer
153 views

Postgresql numeric and decimal is automatically rounding off

CREATE TABLE IF NOT EXISTS ttable ( tcol decimal(9,7) ); insert into ttable(tcol) values(17.4604786); the value is getting stored as 17.46 Happens the same if I use decimal/numeric type I am ...
0
votes
1answer
114 views

From PostgreSQL to SQL Server (No User/Pass)

Some of you MAY be able to crack a really tough nut... I have to move all the data from a PostgreSQL (9.1 - Windows) database to SQL Server because the company I am contracting with is upgrading the ...
0
votes
1answer
94 views

If the standby server is promoted to master, will it work without any replica?

If I have 1 master and 1 standby server, and my master server is down and the standby server is promoted to master. Does my new master work fine without a replica? Does it act like a standalone server?...
2
votes
1answer
107 views

Understanding pg-pool in details

I have following problem regarding the pg-pool (3.3.3) When master fails, automatic failover happens and standby is promoted to master. But this new master don't have any replicators. Other slave ...
0
votes
0answers
29 views

Increase shared_buffers without restarting PostgreSQL

According to the official PostgreSQL documentation: shared_buffers (integer) Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 ...
8
votes
4answers
34k views

Delete all data in Postgres database

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a ...
3
votes
1answer
3k views

Postgresql Streaming Replication Error: WAL segment removed

I want to set up PostgreSQL streaming replication, but get the following error: FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000000000006A has already ...
4
votes
2answers
641 views

How to completely hide a function from all other users and roles?

Database : Postgres 9.6 Use case : I have a function that needs to be hidden, only a particular role/user can see it. I don't even want to expose even the name or body of function to other users/...
0
votes
1answer
112 views

Migrate MySQL 3.23 Data

I have .FRM, .MYD and .MYI files from a MySQL (version 3.23 I think) database from back in the early 2000's. I managed to get the files into a Windows installation of 3.23 and then dump it, however I ...
0
votes
0answers
42 views

the AND argument, SQL state: 42804 [on hold]

this query, taken individually, works, but in the function in which it is inserted, it gives me an error (select count(*) from a where a.d= (select max(a.d) from a,o ...
2
votes
1answer
169 views

Removing Outliers Grouping by Minute

I have a very simple table storing the following fields per http request: id serial url varchar date varchar (yyyy-mm-dd) time varchar (hh:mm) latency integer And I am trying to calculate average ...
2
votes
1answer
132 views

PostgreSQL: simple equality query returning no results for indexed columns

I have a very large table (around 140M rows) with five columns: CREATE TABLE IF NOT EXISTS citations ( citing_id text, citing_issn text, cited_id text, cited_issn text, year int ); ...
3
votes
2answers
446 views

self-joins in recursive CTE: `recursive reference must not appear more than once`?

This is tested with PostgreSQL 9.6, but it's a general SQL question. Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)? I tried the following rCTE containing a self-...
0
votes
1answer
167 views

Postgresql Replica is in sync but repmgr says node unattached

I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't ...
0
votes
2answers
197 views

PostgreSQL 8.1 Corruption Issue

I have an old Postgres server that some major corruption problems on one of the databases. We first noticed the issue when the drives started to fill up on a database that should have plenty of space. ...
0
votes
1answer
24 views

How to add days to a date in PostgreSQL?

I have two tables and I want to execute an update similar to this: UPDATE table1 set table1.date = table2.date + table2.numberOfDays FROM tabel2 WHERE table1.id = table2.table1_id Edit: table1.date ...
0
votes
1answer
21 views

Postgres DB is extremely fast with a query with a certain parameter, but extremely slow for another

It's me again. Sorry, as you can guess, I'm very new at this, but I'm going to try and give you all the info at once this time. I have a table called stats, where I store every action on a certain ...
1
vote
1answer
20 views

Does pg_export_snapshot work across replicas?

This is probably of little importance, but it's possible in PostgreSQL to retrieve the transaction snapshot with pg_export_snapshot and restore it with set transaction to start a second transaction ...
1
vote
1answer
26 views

Predict how much hard drive space a query might cost?

Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My ...
1
vote
1answer
18 views

archive command repeatly failing for one particular file

I am seeing many pg_log files full of following errors 2019-07-08 00:04:06 CEST DETAIL: The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/...
-1
votes
2answers
33 views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
0
votes
1answer
25 views

Get the 1st record and last record of a day in PostgreSQL

Below is the table structure: | Createdtime | Value ---------------------------|-------------- |2019-06-02 00:25:35.86+08 |7805165.31 Mb |2019-06-02 00:03:52.645+08|7796650 Mb |2019-06-...
1
vote
1answer
112 views

Postgresql BDR problem compiling pluging

I'm trying to build deb packages for postgresql BDR plugin, but I'm getting the following error: gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-...
1
vote
1answer
107 views

PostgreSQL full-text search retrival from id gap

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part). There is a postgres function where I SELECT concat_ws(' ', a."...
3
votes
1answer
24 views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
1
vote
1answer
18 views

RDS: permission denied for relation spatial_ref_sys when copying

I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get, ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS
1
vote
1answer
116 views

Is it possible today to replicate 30 TB from multiple databases to PostgreSQL close to real-time? [on hold]

If you had 20 databases spread across 20 servers, could you replicate them all into a single PostgreSQL instance? And can this be done on a server costing less than $100,000? The databases total 30 ...
4
votes
2answers
2k views

Adding hstore entries to an uninitialized (NULL) column

I got bitten by this "feature" recently. If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error. Is this expected? create table ...
0
votes
1answer
125 views

API on top of postgresql [on hold]

I'm looking for one or several API's that can be put on top of postgres to make data input more dynamic. What I'm looking for is something that can handle a wider range of protocols and differently ...
0
votes
1answer
15 views

PostgreSQL: Find row with JSON object attribute in array

I have a postgres database which is set up like this: CREATE TABLE arr_test ( id serial primary key, data jsonb ); INSERT INTO arr_test (data) VALUES ('{"test_row": 1, "my_arr": [{"serial":"...
0
votes
0answers
36 views
+50

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
39 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
30 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 ...