Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
1
vote
2answers
34 views

Wich solutions is better for translate some data?

I've a little mobile app who need some translation. Currently, I just want to translate my app in one language, I don't think I'll need more. So I'm facing a problem, should I translate stuff in ...
0
votes
1answer
56 views

Why does pre-sorted index not help me in window function?

I am trying to speed up this query. Currently it takes ~6s. It queries a TEMP TABLE providers_tam_with_offices_tmp. select *, %(max_providers_to_target_per_territory)s as max_providers, case ...
0
votes
1answer
35 views

pg_dump. Some COPY processes stuck on 'idle in transaction' after pg_dump completion

We do a dump of huge database (~ 2 TB) via pg_dump command: pg_dump -h 192.168.0.132 -d vodka_factory -U vodka_user -F d -j 10 -v -f /backups/vodka_factory_2021-03-26-23-56.dump/ pg_dump process ...
1
vote
0answers
35 views

Multiple tables vs rows for timescaledb / postgresql

I am designing a postgresql/timescale database where I will be recording trades on multiple stocks. For each stock I will be saving the following columns: Trade time, Attribute 1, Attribute 2, ...
1
vote
1answer
23 views

Proper form in relational meal database

I am trying to design a database solution for the following: A table containing all nutritional information regarding a given ingredient; ingredient_ID is the PK here but ingredient name has a 4 ...
0
votes
0answers
31 views

Postgresql server appears not to be running after install

I installed ubuntu 12.6 on Ubuntu 20.10. I had a postgresql version 10 once running on my system. I purged it before installation with sudo apt --purge remove postgresql postgresql-contrib. The ...
0
votes
2answers
31 views

Any way to verify user password from PL/PGSQL?

I need to verify user password, but this user may be "local" (with md5 method in pg_hba.conf) or LDAP user. This works fine and "local" and LDAP users can login, but I need to ...
0
votes
0answers
30 views

Sum of column per distinct ID and group by different column

Let's say I have the following table: create table orders (id int, order_id int, vendor varchar, price int, quantity int); INSERT INTO orders values (1, 1, 'nike', 10, 2), (2, 2, 'nike', 10, 3), (3, ...
1
vote
3answers
55 views

How to write “and” queries for a lookup table when you can't use the AND keyword?

I'm building a practice website that allows users to search hotels based off of the amenities that the hotel provides. From the FE, the user will select a checkbox for however many amenities they want,...
1
vote
1answer
19 views

How to create an aggregate to find min and max value of an integer column and return output in text format?

I have a function where I am specifying the table and column but I require an aggregate that can work for any table and column for similar output create function min_max_val() returns text LANGUAGE ...
0
votes
1answer
44 views

why use PostgreSQL clusterdb instruction

I have come across recommendation for 1C:Enterprise on PostgreSQL. It says that we should periodically use clusterdb command but with no details. PostgreSQL documentation says: "clusterdb is a ...
0
votes
0answers
16 views

Not able to create postgres_fdw

I am using postgresql-9.5. Creating an extension postgres_fdw seems to be taking too long without throwing up any error. create extension postgres_fdw; It keeps on running till I terminate the query ...
1
vote
0answers
53 views

pg_basebackup from docker postgres container and restoring to physical postgres [closed]

I am using pg_basebackup to backup data that is running in docker based postgres (alpine) to a physical postgresql cluster. Both postgres versions are 12. docker exec -t postgresql pg_basebackup -U ...
0
votes
1answer
15 views

How do I handle a single `reference` to multiple different types of data?

I host a file sharing site. The idea is to take a short string (e.g. abcd) and do one of the following: serve an image display some text redirect to another url Historically, I've hosted this site ...
0
votes
0answers
15 views

Postgresql update rows in batches

I need to update millions of rows in postgres and I am trying to write a routine to process these updates in batches. Below is a sample data setup script: -- drop function if exists dbo.dostuff(); -- ...
0
votes
0answers
7 views

Merging two databases and sequence on Primary Key column

I'm using Database Migration Services on AWS to migrate data from 2 DBs and merge into 1. The problem is with existing sequence on id column(in both databases) which is Primary Key too. I want to find ...
0
votes
1answer
21 views

In postgresql.conf, does the order of the listed parameters matter?

I'm relatively new to the DBA field and I have read the official documentation/guides on this topic, but I apologize if my question seems weird. I have deployed PostgreSQL 12 on my AWS instance and ...
0
votes
1answer
24 views

pg_dump - how to split it into directories and files?

I am looking for a way to dump PostgreSQL12 database model into a directory/file structure, instead of a single file - for versioning purposes. I found this old thread, which mentions this exact case ...
0
votes
1answer
35 views

Error while Running query in postgreSQL from sql server 2016

The below query runs well in sql server 2016: select ResellerId, vCompanyName ,x.* from wlt_tblReseller AS main outer apply ( select (count (ipkReportTypeId)) AS "count", vReportTypeName ...
0
votes
1answer
14 views

POSTGRESQL: Using Values List in Delete

Based on the PostgreSQL Documentation it seems possible to use VALUES list as argument in Insert/Select/Update. Is it possible to use it as argument in DELETE something like: delete from some_table ...
1
vote
2answers
48 views

How to optimize a query with timestamp that using OR clause?

I have the follow table: create table myTable ( id serial not null rowtype integer ...
0
votes
0answers
18 views

Merging two two nested JSONs in Postgresql

I have two jsons as follows JSON 1 - { "2021-03-11": [ { "14:30:00": 3000 } ], "2021-03-14": [ { "14:30:00": 130 } ] } JSON 2 - ...
0
votes
1answer
26 views

Determining primary key columns in PGAdmin, if the constraint name is generated by system

I am trying to learn a new schema of a PostgreSQL database, but it's unclear from looking at pgAdmin which columns per the tables constitute the primary keys. It appears that when the tables were ...
1
vote
2answers
29 views

Postgres 11 JSONB - How to get all distinct values of a field across all elements

I have a table called Shipments: CREATE TABLE shipments ( shipmentId numeric, metadata jsonb ); Inside the metaData column there can exist an array of JSON objects named stops which ...
0
votes
1answer
21 views

Equivalent of PGAdmin III Server Status Activity Window in PGAdmin 4 v5

I'm in the process of upgrading to Postgres 13.2 from a much older version. This entails an upgrade from PGAdmin III to PGAdmin 4. PGAdmin III has a brilliantly designed feature called Server Status ...
0
votes
0answers
12 views

Group records by similarity in Postgresql

I have a large table with Tags records. It has a varchar field name. I need to review all those tags and remove near duplicated rows: singular/plural forms, misspellings, a/the, separated or joined ...
0
votes
1answer
47 views

user mapping hide password

I would like to perform queries over different postgresql11-databases. Therefore I created a data wrapper and a user mapping as following: CREATE server 'test' foreign data wrapper postgres_fdw ...
0
votes
0answers
27 views

docker postgres 9.6 cannot append to pg_hba.conf

I have an instance of postgresql running in a docker container that I use for development, so it's regularly torn down and built up again. End of last week, I suddenly started getting an error when ...
0
votes
2answers
41 views

Really poor performance when using subquery to get one value

Hi I have a table defined like below (Its actually lots of partition tables) CREATE TABLE sensor_values ( ts TIMESTAMPTZ(35, 6) NOT NULL, value FLOAT8(17, ...
0
votes
0answers
74 views

Connect to WSL2 Postgres database from local network

I run Postgres 12 service in Windows Subsystem for Linux 2 (WSL2). In my /etc/postgresql/12/main/postgresql.conf, I have: listen_addresses = '*' while my /etc/postgresql/12/main/pg_hba.conf has this ...
1
vote
2answers
58 views

GROUP BY, but use only one row per user

I've spent several hours to make a "simple" SELECT with GROUP BY in Postgres without success. The GROUP BY clause is giving me problems. I have the table cities with columns user_id and city....
1
vote
1answer
47 views

Function to compute difference between 2 times

I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I ...
1
vote
1answer
55 views

Postgresql 11 - service doesn't start

I use PG Admin 4 with PostgreSQL 11 for my data. After some kind of crash yesterday, I am not able to start postgresql anymore. I've done some research, but it doesn't look like a localhost, IP ...
2
votes
0answers
38 views

PostgreSQL: issue with query plan

I am trying to speed up a search query. The context is the one of Sentinel 2 products (satellite images). Trying to fasten the spatial intersection, I have a table containing all MGRS tile geometries (...
1
vote
1answer
69 views

How do I return all column values for the recently inserted row/rows?

I have these two tables in a PostgreSQL 12 database: CREATE TABLE public.test ( entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4() ); CREATE TABLE public.test_registration ( entity_id uuid ...
-4
votes
1answer
54 views

Given a large table (say “users”) that receives many writes, which of the following SQL statements will not lock the table against writes for others [closed]

Statements: Vacuum analyze; Vacuum full; Create unique index concurrently on users .... Create index on users ..... Insert into users.... Alter table users add constraint 'new_check_name' CHECK (...
2
votes
1answer
72 views

Very bad query plan in PostgreSQL 9.6

I have a performance problem with PostgreSQL 9.6.17 (x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit). Sometimes very inefficient query plan is chosen for ...
0
votes
1answer
66 views

Postgres Table toast size is too large [closed]

I want to store some information as JSON in the postgres table. Our application is using a platform. When they started business we store all the data in our db. But now as the application grows we ...
1
vote
2answers
42 views

Slow index scan

We have the following table which contains 25Mi rows and growing in production: create table "Common"."Event" ( "Id" bigserial not null constraint "...
0
votes
1answer
37 views

Run DELETE on each INSERT to enforce row limit

Using PostgreSQL 11. Consider a table like CREATE TABLE "logs" ( "id" INTEGER NOT NULL, "userId" INTEGER NOT NULL, "timestamp" TIMESTAMP ...
0
votes
1answer
21 views

PostgreSQL Update and Delete are not (logically) replicated while having REPLICA IDENTITY

I was trying to sync a table with Logical Replication. This needs to cover Insert, Update and Delete. However the replication only worked for Insert and not the rest. As documented here A published ...
0
votes
1answer
27 views

Query sometimes hangs (but can be executed in less than 4s when run in parallel)

I have a Flask API that has 2 endpoints. When the user clicks a button, the front-end first calls a /calculate route, which computes an expensive query and appends the results to a table called ...
0
votes
0answers
43 views

ERROR: invalid input syntax for type json in postgres import

I have taken a dump of postgres database from server, let's say abc.sql whose total size is 56 GB. I created a local database in postgres, let's say import_here where I have to import the abc.sql. I ...
-1
votes
1answer
35 views

What is the best way to create descriptive labels for tables and columns that have unique alphanumeric names?

I have a database of 1,050 tables with unique alphanumeric names like B02001, B07009, B20017F, each with columns named B02001_001, B02001_002, B02001_003, and so on. I'm trying to create a way to ...
1
vote
1answer
45 views

Postgres: Efficient schema for querying with one exact string match and two range matches

The table I need to query: CREATE TABLE regions ( state text NOT NULL, zip_begin text NOT NULL, -- 9-digit zip code zip_end text NOT NULL, date_begin date NOT NULL, date_end date, ...
0
votes
1answer
16 views

Check if a database table has been renamed

My app has a list of an external db's tables and it needs to be regularly updated. How do I check if a table has been renamed? i.e. how to differentiate between a new table and an old table that's ...
2
votes
1answer
18 views

How can one make a partial index to optimize a query for a single value?

Let's say I have table foos with column thing_id. What's the smallest index I could make for these queries? select count(*) from foos where thing_id is null; select id from foos where thing_id is null;...
2
votes
1answer
39 views

Why does PostgreSQL accept junk when parsing an INTERVAL

psql (11.8) Type "help" for help. public=# select '!@#$%^&*()00:00:00.01<>?<>:'::interval; interval ------------- 00:00:00.01 (1 row) Seems to take just about anything......
-1
votes
2answers
66 views

Get distinct values for all columns for all tables from specific schema

I would like to get all distinct values for all columns in each table of a specific schema. First, I summarised all my tables/columns in a new_table (myschema.dico) CREATE TABLE myschema.dico AS ( ...
0
votes
1answer
32 views

Capture row with highest value from each percentile

So far I've tried doing this: SELECT MAX(buckets.position_id), NTILE FROM ( SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id) FROM position WHERE activity_id = 8 ...

1
3 4
5
6 7
266