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

find error log for streaming replication

Recently the streaming replication fails after I restart the primary. I can not find the record using select * from pg_stat_replication Now, I want to see it from the system log file at /var/log/...
0 votes
0 answers
8 views

I'm facing one issue about date format

hi i'm facing the issue about the date format.showing the error is out of the range.how can i fix the issue
13 votes
2 answers
25k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
0 votes
1 answer
579 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
0 votes
1 answer
9 views

How to search for partial matches from a GIN index in a multi-word PostgreSQL query?

I have a GIN index in a Postgres database that I'm currently using to do full text search. So if a tsvector field contains the vectorized form of "mary had 4 little lambs" then I could ...
0 votes
1 answer
793 views

Multiple rows vs Single row with JSON

What is the most optimal way to do this? In TV Show page I need to get all progress from all episodes in that TV Show In Homepage I need to get the continue_watching In Episode I need to get progress ...
0 votes
0 answers
26 views

Managing a mixed database engine environment

The majority of our database servers are SQL Server but we also have an Oracle database in production, MySQL installs that are being archived, and possibly a postgresql install out there which might ...
1 vote
1 answer
7 views

How to (pg_)restore selected tables with all indexes?

I would like to restore selected (currently after -t) tables including all indexes without making a definition after -I - because indexes are changing, new ones are created, old ones may expire. What ...
1 vote
1 answer
46 views

How to generate rows based on a constrain of values from a column?

So far, I am using this Query: SELECT "FailCode", count (*) FILTER (WHERE "FailCode" =10 or "FailCode"=14 or "FailCode"=18 or "FailCode"=23 or "...
-1 votes
2 answers
38 views

SELECT rows, but remove rows that have duplicate column

I believe this is a very simple query to make, but for some reason it doesn't work in Postgres (it works in other dbs) This is how db schema kooks like: tableA( id integer, ... ) tableB( id ...
2 votes
3 answers
92 views

For what type of data it's better to use relational, and for what type of data, non-relational databases?

I am trying to write my first big backend project. This is a mobile/web application like Instagram but for different purposes. As I searched through the internet I found that Instagram uses PostgreSQL ...
0 votes
1 answer
280 views

Query to update the JSON that does not contain a specific key

I want to query a table that has JSON data. The data of the column detail in the table details are as follows. id | 1 detail | { myCompany: [{ email: 'company@company.com', inProfit: true }], ...
1 vote
0 answers
25 views

What does PostgreSQL Status active (exited) mean?

I have PostgreSQL with PostGIS intalled on Ubuntu 22.04. I can access the PostGIS-Layers from QGIS and work with them. When i check the status of PostgreSQL with: sudo systemctl status postgresql it ...
1 vote
0 answers
410 views

How to compare a subset of two tables using dynamically generated column names in PostgreSQL?

I have two tables, one a test table and one a production table, both with +200 columns and a couple thousand lines of code to create the table. I periodically make changes and am trying to automate QA....
0 votes
1 answer
186 views

Amount of clients that have subscription by years and month

I got 2 tables as follow CREATE TABLE subscriptions( client_id INT, open_date DATE, close_date DATE ); CREATE TABLE calendar(date DATE); subscription table contains data about clients ...
1 vote
3 answers
2k views

Hash index vs. btree index performance on inserts and updates

I have table which is several time bigger than available RAM. Table size is 181 GB, 92 million rows. PostgreSQL 10. There is field with uuid in the table. And there is need to insert into the table ...
0 votes
0 answers
28 views

VACUUM is significantly slower in Postgresql 13 compared to Postgresql 12

After switching to Postgresql 13 (13.6) from Postgresql 12 (12.10), we noticed that the manual VACUUM operation takes significantly more time, often more than double compared to Postgresql 12. Which ...
1 vote
0 answers
218 views

insert or update row based on timestamp

I'm looking for a solution in postgresql to insert or update a row, based on the timestamp of that row. What I'm looking for is the following: I want to insert the use of a specific hashtag, and count ...
0 votes
0 answers
21 views

Postgres query timedout

One user request He wants to get the msn from subscriber_profile which are not present in subscriber_profile_temp He is trying below query which is getting timedout.msn column have index for both ...
5 votes
1 answer
2k views

How to constraint a foreign key to a subset

I have a table employee, and each of them can fulfill different roles, like inspector and constructor. I also have another table with a column where I can store what inspector is on the job. People ...
-1 votes
0 answers
20 views

How can I define base type user type in pgsql

I am trying to define a base type, User-defined type but I am getting error and a little confusion. So, If anybody can share example. https://www.postgresql.org/docs/current/sql-createtype.html I am ...
1 vote
1 answer
29 views

Identify unique trips for each user where walking is followed by trip start

I have a table trips with the following information Row User Timestamp Event 1 1 29/06/2022 17:18:03 Walking 2 1 29/06/2022 17:18:02 Walking 3 1 29/06/2022 17:18:01 Start 4 1 28/06/2022 19:21:03 ...
1 vote
1 answer
20 views

Restart master in physical replication

I have 2 servers involving master-slave replication (physical replication). I see the replication using select * from pg_replication_slots; there is a record saying : user : rep_user` application : ...
0 votes
2 answers
408 views

Postgresql MVIEW refresh from Oracle Materialized View Log

Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is ...
0 votes
0 answers
12 views

pgbench : too many client connections for select()

I was working on benchmark testing for pg10 and pg14. I did the testing for 100 clients with 12 threads in pg14 I was getting following error, "pgbench:too many client connections for select()&...
1 vote
2 answers
335 views

How should I configure my postgresql.conf file to backup the database once per hour?

My goal is to back up the PostgreSQL 10 database on a Ubuntu 18.04 VPS once per hour. However, I clearly do not understand how to achieve this. Here is what I've done. I modify the postgresql.conf ...
0 votes
0 answers
21 views

Postgresql: non-breaking space and other space symbols don't work in replace function

I have a table with a data field. Example of string value: http://news.mail.ru/6679541 1056 506 Firefox/5.0 I'm trying to split data, but none of the space symbols helped me do that. I tried to use:...
0 votes
0 answers
20 views

Django password hashes in database [closed]

can someone explain to me why django saves the hash in the database as argon2$argon2id$v=19$m=102400,t=2,p=8$... and not $argon2id$v=19$m=102400,t=2,p=8$?
0 votes
2 answers
44 views

Count words in table field

I have this PostgreSQL table for storing words: CREATE TABLE IF NOT EXISTS words ( id bigint NOT NULL DEFAULT nextval('processed_words_id_seq'::regclass), keyword character varying(300) ...
0 votes
2 answers
25 views

Check if running inside a PostgreSQL extension setup/upgrade script

Is there a way in PostgreSQL to check whether the current context is during CREATE EXTENSION or ALTER EXTENSION … UPDATE? I'm switching over some of my schemas that I managed with my home-brewed ...
0 votes
1 answer
32 views

PostgreSQL FATAL: no pg_hba.conf entry for host "[ipv6]", user "[user]", database "postgres", no encryption

Unable to connect to a postgreSQL database on a LAN. (No problems connecting to the db on the server locally) Error shown in pgAdmin.app: Unable to connect to server: connection to server at "xxx....
-1 votes
1 answer
27 views

Postgres server uses all of RAM and SWAP

The Postgres Server Uses all of 120GB RAM and all SWAP! Is there anything wrong? How can I fix this? This is postgres config shared_buffers = 30GB work_mem = 24MB # It's limited for the ...
5 votes
2 answers
197 views

Make two columns unique both ways

I'm creating a database which stores football matches. Each round, any given football club may only play once (home or away). For example, after the insert of INSERT INTO Match (Round, Home, Away) ...
0 votes
1 answer
2k views

postgresql deadlocks: SELECT blocked and blocking query

Based on the 2nd answer on https://stackoverflow.com/questions/22775150/how-to-simulate-deadlock-in-postgresql I performed the following snippet: Transaction 1 Transaction 2 BEGIN; ...
3 votes
2 answers
1k views

macOS Catalina - postgreSQL - sysctl.conf still relevant?

I had the following settings in /etc/sysctl.conf before Catalina: # PostgreSQL Config for the MAC kern.sysv.shmmax=2147483648 kern.sysv.shmall=524288 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv....
0 votes
2 answers
196 views

Is it possible to COPY tables into PostgreSQL without setting structure first?

I'm just getting to grips with PostgreSQL 9.6. I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin? Or...I've not ...
1 vote
1 answer
664 views

dockerized postgresql listing no clusters

I'm upgrading an existing dockerized postgres:9.6 to 10. There are plenty of good Q/As on DBA and SO that go the path of pg_upgradecluster, but it is not working for me. When I start a vanilla pg:10 ...
1 vote
1 answer
425 views

Recovery with Postgresql logical replication

I'm using Postgresql 13 on Debian 10.6 and learning about logical replication. I've set up logical replication with one publisher and one subscriber of one table. I'm wondering what my options are for ...
-2 votes
0 answers
19 views

Wal2json vs pgoutput

Im running my cdc on postgresql 13(aws aurora). Till now it’s using wal2json. But seems in many places we saw that aurora have a bottleneck with this while committing a bulk changes in a single ...
5 votes
1 answer
5k views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
2 votes
2 answers
667 views

Modelling users and groups - but with multiple types of groups

I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them). However, its seems I'm dealing ...
1 vote
0 answers
34 views

Polybase In SQL server 2019 and 2022 take so long time

I'm using polybase to load data from PostgreSQL and have some problem The PostgreSQl database and SQL server (for my case is SQL server 2022) are in same machine. I create an external table in SQL ...
0 votes
2 answers
2k 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....
4 votes
1 answer
228 views

Postgres stuck with `FATAL: the database system is starting up ` for hours. Should we wait or is this a sign for a corrupted DB?

I found answers to this problem suggesting that it might take a while to restart postgres. But now we are already waiting for over 2 hours which seems extremely long compared to the 5 minutes it ...
0 votes
2 answers
30 views

Azure Postgres "flexible server" could not write to file pg_tblspc: "No space left on device"

I just created a new instance of PostgreSQL Flexible Server, version 14, on Azure (Memory Optimized, E8ds_v4, 8 vCores, 64 GiB RAM, 512 GiB storage). I took a backup of a "single" server, ...
0 votes
0 answers
21 views

Postgres stuck in wal recovery crashloop

My postgres is deployed on k8s using the timescale helm chart. It's managed by patroni and the backup is managed by pgbackrest. It's been stuck in this state for a day now (removed timestamps etc. ...
1 vote
1 answer
374 views

What can cause low cost and high runtime in EXPLAIN ANALYZE?

I have a database that pretty consistently runs queries in a magnitude of cost/10 ms. There are a couple queries where EXPLAIN ANALYZE reports a cost of 2000 (which I'd expect to be somewhere in the ...
1 vote
1 answer
30 views

PostgreSQL attaching range partition is slow

I have a range partitioned table where I would like to be able to swap out partitions over the same range quickly (that is, re-create tables and swap out existing partitioned tables for them). However,...
3 votes
1 answer
181 views

UPSERT based on unique combination of (INTEGER, NULL), (NULL, INTEGER)

My table, for simplicity, looks like this: id | foreign_key_1 | foreign_key_2 | value As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables. The tricky ...
0 votes
0 answers
34 views

Performance improvement of query with Index scan never executed : postgresql

Can the below query be improved with new Indexes.Few Index scans are "never executed" explain analyze select ASSOC.ID,ASSOC.APPLICATIONCONTEXT,ASSOC.ASSOCIATIONTYPE,ASSOC....

1
2 3 4 5
312