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

Handle no more connections or out of shared memory

I have a golang program that does computation on data in multiple threads at once, all pulling their data from Postgres. The number of threads depends on a previous result. There can therefore be ...
0
votes
1answer
43 views

SELECT query on unique number field returns no rows

This is a cross-post of an issue I field on the GORM Github repo. I am unsure if the issue is with Postgres 11 or with GORM. See: https://github.com/jinzhu/gorm/issues/2872 I have a table containing ...
0
votes
0answers
7 views

Does Postgres ever require a naked field without single quotes?

I have this query: UPDATE user_map_table SET user_id = '1', value = '{"bob":"diane"}', key = 'couple', value_type = 'string', added = 'false' WHERE user_id = 1 it worked fine. The thing is the ...
0
votes
1answer
16 views

Why is PostgreSQL not allowing me to drop the extension “postgis” and immediately recreate it in the same transaction?

I have a PostgreSQL database called "db". A long time ago, I installed PostGIS into it by doing: CREATE EXTENSION postgis; In hindsight, this was a horrible mistake, and I partly blame the ...
0
votes
2answers
25 views

What happens at low level when I add a column to a table in PostgreSQL?

I have a table with number of rows in the order of billions. Let's say I want to add a column to that table. I would like to know what happens behind the scene to know the cost, expected time of this ...
0
votes
1answer
7 views

Why does PostGIS create an ugly table in my database called “spatial_ref_sys”?

I need to use PostGIS, and do use it, but it bothers me that it created this ugly "spatial_ref_sys" table in my database, in the main (public) schema, where I keep all my own tables. It looks ugly. ...
0
votes
0answers
9 views

Postgres build array from boolean columns

I have columns like role1, role2, role3, etc. They are all booleans. I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the ...
1
vote
1answer
144 views

How do I insert a record and put the ID into an existing record in another table?

I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy. Simplistic example: create table aaa (id serial primary key, foo int);...
0
votes
0answers
11 views

How to have PostgreSQL running forever with constant insert and delete per day

Question: Lots of rows are getting inserted every second. Batch deletion is performed when the disk is close to full. This causes two problems: It is impossible to examine actually how much space in ...
0
votes
1answer
38 views

using SUM on foreign view's keys grouped on multiple key/values?

I had a question on executing SUM on rows from one table and inserting them in a column on another table where the calculated rows shared id with a row in the new table. The solution worked very well ...
2
votes
2answers
618 views

Can I cluster PostgreSQL by sharing a data directory over NFS?

I created a PostgreSQL cluster in one host and shared it over NFS share to another host. Even though i can access the cluster using second host but can not use or log in to databases. Is it possible ...
-1
votes
1answer
22 views

Help removing special characters from a value

I have a table that looks like this. The data in the "TestDate" column is a NUMERIC data type even though it represents a date. So, a value of "20200216" is February 16, 2020. I am trying to mimic ...
0
votes
2answers
350 views

Running a task without waiting for the results in Postgres 11 on RDS

I've got a question about spawning stand-alone threads on Postgres. Here's what I'd ideally like: Have a postgres-side solution to automatically launch tasks on a schedule in their own threads. ...
1
vote
3answers
45 views

How to avoid multiple SELECTs from a bag of properties table?

I have a table with key-value pairs for managing user settings. This approach is known as the “bag of properties” and one of its benefits is you don’t need schema changes when a new property is ...
0
votes
1answer
221 views

Have multiple foreign keys or one

I have 3 table: brand: brand_id(pk), name product: product_id(pk), name brand_product: brand_product_id(pk), brand_id, product_id And I have a report table, at the moment it looks like this: ...
0
votes
1answer
2k views

Join JSONB column to normal PK column in Postgres

I am trying to join a normal table to a table with a jsonb field via a property on that jsonb field in Postgres. Example: Normal table: create table Res ( id uuid default uuid_generate_v4() ...
3
votes
3answers
35 views

Is there any performance issue with including the “COLLATE” as part of the query as opposed to setting it for the column/table?

PostgreSQL has this "collate" concept. You can either tell a table's column to always have a "collate", or you can do it in the query. If I do it in the query, does PG punish me somehow performance-...
4
votes
1answer
43 views

Recursivly get a Tree Via self joined table

Using other questions here & Postgresql documentation I've managed to build a many-to-many self joined table. However adding a WHERE clause is giving me trouble. Problem: A Category can have ...
2
votes
2answers
39 views

Eliminating OIDs while upgrading PostgreSQL from 9.4 to 12

I'm currently running PostgreSQL 9.4 in my production environment, but we're in the process of doing an OS upgrade on the server so I thought it might be time to upgrade our PostgreSQL version to the ...
24
votes
6answers
46k views

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; ...
0
votes
1answer
43 views

How is it possible that some people are able to juggle several different SQL variations in their heads at once? [closed]

I have spent the last 20 years dealing with first MySQL (for about 6 years), then 14 years old PostgreSQL. In 2006, I naively thought that it was going to be painless, "because both use SQL"... man, I ...
3
votes
1answer
24 views

What is the meaning of “-x-icu” in PostgreSQL's “collates”?

I had this query: SELECT * FROM table ORDER BY label ASC; Since the labels are not in English, they didn't get sorted in the right order (ones beginning with "ö" were not in the bottom/end). I ...
0
votes
1answer
19 views

PostgreSQL: Save roundtrips / Network performance

For my work I often have to to large selects loading a lot of data. The connection happens with JDBC driver and high fetch size. This is not a problem when working directly on the server of the ...
0
votes
2answers
24 views

PostgreSQL: Allow connection by host name and not IP?

As background when connecting from remote (eg home mostly using JDBC) due to how the VPN works I always get a different IP address and hence I can't connect because the IP isn't listed in pg_hba.conf. ...
0
votes
1answer
14 views

Strange behavior of query with pg_sleep

I have plpgsql anonymous block. PostgreSQL 10. I store current time to variable, raise notice with value of the variable, sleep for 20 seconds, store current time to another variable, store both ...
0
votes
0answers
12 views

Postgres randomly fails silently on huge CSV import

With Postgres 10.12 on Windows 10, I am importing at 67 GB CSV file into a database using a simple COPY query: COPY table_name FROM 'c:/temp/data.csv' WITH (FORMAT csv, HEADER); The import fails ...
-1
votes
1answer
38 views

Why is the order of `OFFSET` and `LIMIT` treated differently between PostgreSQL and MySQL?

I have a schema like this (works for both MySQL8 and PostgreSQL12) # 1. Build schema DROP TABLE IF EXISTS Employee; CREATE TABLE Employee ( Id INT, Salary DECIMAL(6, 2), PRIMARY KEY(Id) );...
0
votes
1answer
86 views

Aurora postgres grant permission to pg_attribute

In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. ...
43
votes
5answers
38k views

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in the SO post Ignoring time zones altogether in Rails and ...
0
votes
1answer
12 views

Migration DB postgresql in windows to Red Hat(linux)

I would like to know how I could migrate a postgresql database which is currently in a Windows operating system to Red Hat? I'm a beginner and I don't know where to start. Thanks .
0
votes
1answer
45 views
+50

Setup VIP for PostgreSQL Replication

I have been assigned a task to setup Streaming replication in Postgresql. I am not a DBA by profession, doing this thing for the first time. I have set up replication, now as we don't want to change ...
0
votes
0answers
9 views

Postgres point type column to Spring Entity data type

I have a postgres table: CREATE TABLE public.geometry ( id serial not null primary key, coordinates point not null ); and I am trying to retrieve a row as JSON response API from a ...
-1
votes
0answers
14 views

is it possible to trigger lambda AWS Postgres RDS

we are using AWS Aurora Postgres, is it possible to invoke lambda if any event was triggered in RDS, if any post already is there means please let me know Thanks in advance
0
votes
1answer
36 views

Why aren't read-only queries executing concurrently?

I have a Django app using either sqlite or Postgres. (I've tried both and encountered the same problem.) Requesting a particular endpoint results in a read-only query like this that takes 1.5-3 ...
0
votes
0answers
13 views

How to improve postgreSQL availability [closed]

What are the methods to improve postgreSQL's availability?
0
votes
0answers
30 views

How to reconcile databases after split-brain?

We run three on-premise datacentres with several databases. The network between the three datacentres is not constant and goes down several times a year for lengthy periods of time, but the ...
0
votes
0answers
18 views

Fast long-to-wide reshaping in PostgreSQL

I am trying to change a really long table (1955451890 rows -- 278 Gb on the DB) to a wide format using crosstab from the tablefunc extension. Following several examples from SO (i.e. Crosstab with ...
2
votes
1answer
284 views

commitlog broken after standby setup with pg_basebackup

I'm trying to replicate a database with pg_basebackup in order to use it as standby server. But when I'm trying to start up PostgreSQL I get the following errors and the server shuts down again: 2016-...
1
vote
1answer
29 views

Backup and restore for PostgreSQL + why number of backups increased while we limit the recovery window of 7 days

We are using the barman for PostgreSQL backup and restore on rhel 7.2 machines In the barman configuration file - /etc/barman.conf we set the following retention_policy_mode = auto retention_policy ...
0
votes
1answer
16 views

Long running VACUUM VERBSE in Postgres 9.5 - how much time remaining?

I am vacuuming a very large (718GB) table containing a lot of dead tuples and 4 indexes. VACUUM ANALYSE VERBOSE bigtable; This vacuum has been running for over 11 hours (!) now and I am trying to ...
1
vote
1answer
47 views

Postgres table index broken after updating existing rows on very large table

The largest table in our DB (Postgres 9.5) has almost 2 billion rows and is about to reach the 32-bit integer limit. I have begun a process of converting the PK (id) from int to bigint as follows: ...
0
votes
2answers
18 views

Postgres tablespace size management

I have a Postgres 10 database which is the model for an MVC app. On it many (200,000) LOBS get saved, eventually processed, and stored or deleted depending on some conditions. I have two problems. ...
6
votes
2answers
12k views

See entire string from a lengthy text field in pgAdmin?

Using pgAdmin, how can I see the entire string value for a row when the value is very long? For example, the value may be comma-separated values (CSV) content with hundreds or thousands of ...
0
votes
1answer
76 views

Queries on large database kill connection to the server, works with LIMIT

I'm trying to run queries on a large-ish database without killing the connection to the server. I'm using Postgres 12.1 on a mac with 16gb of memory, and about 40gb of free disk. The database is ...
0
votes
1answer
25 views

use SUM on foreign keys where it matches rows id?

I have this table: CREATE TABLE buildings ( id SERIAL PRIMARY KEY UNIQUE, name character varying(20) NOT NULL UNIQUE, document jsonb ); then this view: CREATE VIEW v_rooms AS SELECT ...
0
votes
1answer
14 views

Postgres Minor Version Upgrade from 11.4 to 11.7

I am new to PostgreSQL database. I want to upgrade the PostgreSQL version from 11.4 to 11.7. what are the steps involved can anybody tell. It will be helpful if anyone can share SOP. Thanks
0
votes
1answer
16 views

Calculate products count based on category tree table

It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent. How can i do that SQL/Postgresql? ...
0
votes
2answers
17 views

COPY Command spawning multiple processes

I have a lengthy data pipeline that imports a CSV, creates a sequence of dependent views and then exports the results using COPY as a CSV. This process had been working fine, completing in around 30-...
1
vote
2answers
376 views

Postgres \copy command into single column without delimiter

I am trying to import big fixed length files into Postgres and I am using the copy command and a staging table following this guide. The problem is that this files has a lot of different characters ...
2
votes
2answers
582 views

Delete records if not present in subquery

I want to delete records from a table that are not found in a subquery. But it's not having the desired result. The table has multiple rows per SKU. Each price row has a valid_from and valid_to date ...

1 2 3 4 5 224