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
2answers
35 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
1answer
25 views

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

I have a table called stats, where I store every action on a certain website (basically when the user clicks a link, data is saved in the db). The data is modeled with PostgreSQL as ( id integer NOT ...
0
votes
1answer
28 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
2answers
34 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
20 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
39 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
26 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-...
3
votes
1answer
26 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
30 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
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":"...
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
38 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
1answer
24 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
27 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
0
votes
1answer
53 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-...
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 ...
1
vote
1answer
34 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 ...
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
0answers
13 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
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
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?
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
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 ...
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 ...
0
votes
1answer
16 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 ...
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 ...
1
vote
0answers
16 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=$...
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.
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
18 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 ...
-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,...
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 ...
0
votes
2answers
26 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
vote
2answers
20 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
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
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, -- ...
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 ...
3
votes
3answers
329 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 ...
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 ...
-1
votes
1answer
29 views

How to update a numeric type column to avoid many decimal points

lets assume I have a table with this entries: thing: position: ball 1001.25 pensil 1001.0625 car 1002.5 watch ...
0
votes
1answer
13 views

Check on whether a person is subscribed to another person

I have a table of subscribers. It has a connection to the user table. id | user_id | follower_id | ----------------------------- 1 | 46 | 71 | 2 | 46 | 53 | 3 | ...
1
vote
1answer
20 views

Postgres numeric type overflow after 1023 bisection of 1

i am testing the numeric type in postgres according the precision and scale i declared the numeric type with (1000,900) which gives me 900 digits decimal points but when i test it by inserting and ...
0
votes
1answer
24 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
26 views

Postgres server is not starting

I have installed postgres 11 earlier on my MacBook Pro. Suddenly it stopped working. Usually I start my postgres server by following command pg_ctl -D /usr/local/var/postgres start and it returning ...
0
votes
0answers
22 views

Postgresql 11 - pg_stat_statements - empty query column for some of queries

we installed pg_stat_statements couple of days ago. Unfortunately, some of the queries does not show up in the view, e.g. the row is there but the 'query' column is empty. My guest is that the queries ...
0
votes
1answer
64 views

Large bulk insert performance difference PostgreSQL vs MariaDB (InnoDB)

I've got the very same single-table definition in both PostgreSQL 11.4 and MariaDB 10.4.6 (InnoDB) and I'm seeing a significant difference in INSERT performance I cannot explain for PostgreSQL. My ...
0
votes
1answer
27 views

Based on the result from the table, get the result from another table

I have two tables users and products: 1) users: id | username | ... | -------------------------- 1 | user1name 1 | ... | 2 | user2name 2 | ... | 3 | user3name 4 | ... | 4 | ...
0
votes
1answer
23 views

Find exact and partial matches in the tables

I have two tables products and users. I need to find exact and partial matches in these tables. For example, there is a user with the username cat, and there is a product with the name cat. And ...
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... ...