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

psycopg2 connect error: cannot pass more than 100 arguments to a function?

I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error : Error : psycopg2 connect error: cannot pass ...
0
votes
1answer
863 views

HA PostgreSQL installation using patroni and pgbouncer

I'm not having a problem yet, just searching for advise I want to build installation of PostgreSQL on 3 nodes using patroni: master and 2 standbys (for failover only). There are PostgreSQL and ...
-2
votes
2answers
29 views

How to find out WHERE a PG error/warning/log happened?

I have PostgreSQL CSV-log its "errors" according to this structure: https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG It doesn't include the file ...
0
votes
1answer
176 views

Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars

Trying to use REGEXP_REPLACE to cleanup a text field in a table. Here's my query: select title original ,REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', ' ') clean from tmp_table2 WHERE txn_title like '%?%' ...
1
vote
1answer
17 views

Join tables and summarize rows based on condition

I have two tables. One containing countries with an ID and the geometry The other containing customers with a name, geometry and a boolean value showing if they are active or not. Countries iD ...
0
votes
1answer
3k views

Insert a 'NULL' string in a table in PostgreSQL

I would like to know if it is possible to insert a 'null' string in a column which has a NOT NULL constraint. I'm using PostgreSQL, although this probably applies to other database management systems ...
3
votes
1answer
54 views

Determine Nodes in Network with PostgreSQL

I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes ...
0
votes
2answers
1k views

Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

I am not a developer or PostgreSQL DB admin, so this may be basic questions. Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13 I'm using a python script to ingest external data, ...
-2
votes
0answers
8 views

In the CSVlog mode, is there some way to make PostgreSQL log a custom string bases on some kind of SQL session variable or similar?

I have instructed PostgreSQL to csvlog its errors/notices. Then my script reads these files and put them back into a database table, then deletes the log file. This part is all working and stable. My ...
9
votes
1answer
657 views

What is “special” about PostgreSQL update vs delete+insert

My understanding is that an update locks a tuple, marks it as deleted, and then adds a new tuple. In other words, update = delete + insert. Or so I had believe. But it appears there something ...
1
vote
0answers
16 views

Using one sequence across all tables in PostgreSQL

I propose using a single sequence for the bigint identity columns of most or all tables. (Essentially, OID, except 64-bit.) This helps prevent security problems for a multi-tenant application that ...
-2
votes
0answers
10 views

In OmniDB, how do I make it “remember the state”?

A few days ago, I finally said "good bye" to pgAdmin 4 forever, after enduring its sadistic developers' mind-games and torture through software for a very long time. I cannot describe in ...
0
votes
2answers
12 views

filter table rows within range of related column

I have a postgres database with the following table: CREATE TABLE data ( id integer PRIMARY KEY, group_id integer NOT NULL, value double ); the data contained in it is ...
-1
votes
0answers
35 views

How to use NOT IN in PostgreSQL query? [closed]

I am working on one of my projects. Following is my query: SELECT ab.calendardatekey::text::date AS dte, idm2.emplid, 1 AS entityid, count(ab.dts) AS s_abs FROM basedt.t_basedr ab ...
1
vote
1answer
14 views

How to define a parameter that will be used as an IN argument inside a function?

I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and ...
0
votes
1answer
23 views

Fetch partitions where values are present

I have table partitioned by range of dates (monthly). Having a list of dates, I want to select all the partitions, where values from the list of dates are present. That is to avoid scanning the ...
-1
votes
0answers
23 views

Get distinct count of a table joined via two different bridge tables

I have a situation where I need the count of unique/distinct communities any tool is part of. Tool can be part of community via two different bridge tables. Below is my query: SELECT ...
3
votes
2answers
31 views

Auto vacuuming high-write, high-update, and mostly read table types

What are good auto vacuum settings (recommendations) for tables like: High Write Table Insert load Range between 30-10,000 inserts in a day. The table can idle for weeks without load, but can get ...
0
votes
1answer
22 views

Downsampling to (eg) “last result on Sunday night”

We have a largish PostgreSQL table that records user's disk usage. id | bigint | | not null | (unique constraint) time | timestamp without time zone | |...
0
votes
0answers
31 views

How do I safely update a unique column in PostgreSQL?

CREATE TABLE person ( id bigserial PRIMARY KEY, name text NOT NULL, ssn text UNIQUE, birthdate date, created timestamptz DEFAULT now() ); I want to update the SSN of person #5. If someone ...
0
votes
0answers
10 views

Updating multiple fields in a related row within a trigger [closed]

I have a search_results table that contains all of the fields a user could search for. It includes fields populated from multiple tables along with a computed search_document column for when the user ...
6
votes
2answers
479 views

What is the `TABLE example` syntax called?

In PostgreSQL, I've been using syntax like TABLE example1 UNION ALL TABLE example2 which I understand to be equivalent to SELECT * FROM example1 UNION ALL SELECT * FROM example2 But a colleague ...
0
votes
1answer
150 views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
0
votes
1answer
27 views

logical worker replication says the publication does not exist, however this seems to actually exist?

I'm trying to setup logical replication between two database instances. I've created a publication on the main db, and a subscription on the replica. Specifically: on main db: create publication chris ...
2
votes
1answer
309 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-...
0
votes
0answers
26 views

Bottleneck when inserting many rows into different tables

TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain....
0
votes
1answer
19 views

postgresql multicolumn partitioning

Postgres 11 I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys. The reason is that: I want to prune months that are not longer ...
0
votes
1answer
84 views

How to remove NULL values in multidimensional array?

SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
0
votes
2answers
22 views

How do I migrate Postgres database using an intermediate file

I have a PostgreSQL v9.5.23 database on an Ubuntu 16.04 server that I want to migrate to a new Postgres 12.4 installation on an Ubuntu 20.4 server. The network pipe recommended by the Postgres docs ...
0
votes
0answers
17 views

Get the last ID after INSERT the PostgreSQL database with PgBouncer

Previously we used PostgreSQL 9 with PgBouncer in session mode, and with that was able to easily create a new record and get the last ID with pg_get_serial_sequence and CURRVAL. INSERT INTO "...
1
vote
4answers
100 views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
3
votes
1answer
43 views

Is there a way to allow only DELETES from CASCADES only?

I want to prevent anyone from deleting certain records from a table, except for when the parent record in another table gets deleted, then should it be deleted. Can I use triggers for this purpose?
0
votes
1answer
22 views

Why is a table showing up twice in pg_class?

I am attempting to run a PostgreSQL statement to query approximate table size count estimate using the following statement: SELECT reltuples as approximate_row_count FROM pg_class WHERE relname = '...
0
votes
0answers
12 views

How can I enable trace for a particular sql query or a process id i postgres? [closed]

Is there any method in postgres to enable tracing for a particular process id or a sql query?
0
votes
0answers
15 views

Postgres Logical Replication to AWS Aurora Postgres with or without Database Migration Services [closed]

I have an end goal of migrating a large Postgres (v10.7) database (3TB) from one cloud provider to AWS Aurora Postgres. Here are my constraints and approach: Current / Non-AWS Cloud Platform will not ...
221
votes
14answers
562k views

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

Hello I am trying to run a website sent to me but after doing so this error appeared connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "...
0
votes
0answers
19 views

High CPU when executing a Postgres query spanning multiple tables [closed]

We have an application which executes jobs. The job usually consists of a single transaction in which around 4k rows are inserted/updated across 13 relations. The transaction takes around 2 minutes to ...
13
votes
4answers
38k views

How to add a column with a foreign key constraint to a table that already exists?

I have the following tables, CREATE TABLE users (id int PRIMARY KEY); -- already exists with data CREATE TABLE message (); How do I alter messages table such that, a new column called sender is ...
0
votes
1answer
15 views

How to set statistics level of a table in postgres?

I know how to set statistics level for a particular column i.e alter table table_name alter column column_name set statistics 100; However, if I wish to set statistics level for a particular table or ...
0
votes
1answer
18 views

How to monitor general performance and wellbeing of a database?

I've read @Mike answer to How to tell if database tuning is fruitful (PostgreSQL), and that's the kind of approach I'm thinking about for monitoring the performance of my database (of a web ...
2
votes
1answer
25 views

How to fetch related rows on multiple condition with ranking

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | ----------------------------------------------------------...
0
votes
2answers
24 views

pg_stat_monitor: see placeholders instead of actual query

I have started using pg_stat_monitor extension from : https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html with my postgresql database.I have a problem here. ...
10
votes
6answers
42k views

PostgreSQL user can not connect to server after changing password

I've met this with 4 roles I created: After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more. pgAdmin III show error message: An error has occurred: ...
0
votes
0answers
11 views

How to find the statistics level of a column in postgres?

I have altered the statistics level of column using below command. edbstore=> alter table orders alter column orderdate set statistics 100; From which table, i can find the statics level of all the ...
0
votes
0answers
17 views

Index for the items inside a jsonb array in Postgres

I've got a table which looks like this: create table data ( id serial primary key, report jsonb ); with the data like this: insert into data (id, report) values (1, '[{"item_id": 1, &...
0
votes
0answers
18 views

While in database is large quantity of database writing, reading from the database sometimes takes a long time [closed]

I have PostgreSQL cloud-based database, making 1.5-2M requests per day. I checked the statistics of the individual queries themselves with different variants of the extracted data. In general, the ...
0
votes
2answers
744 views

How to delete records without a primary key in a stardard way (i.e. not using ctid)

Case of use We have a log table without a PK with two columns (date, event); You inserted a row. Then you want to undo the inserted record. Is there a way to delete that row (without deleting ...
0
votes
0answers
16 views

Remote access problem - PostgreSQL

I'm don't know much about database management, so to enable the remote access to my postgresql db I followed some tutorials. I've updated pg_hba_conf and postgresql_conf files. In pg_hba_conf I added ...
0
votes
1answer
17 views

Postgresql Create ForeignKey with repeated table name in value of ref contrain key

I am working on a database which does not contain any foreign key. When i open table I see strange format key because table of parent reference is stored in the value. exemple CREATE TABLE foo ( ...
0
votes
1answer
14 views

Can sharding speed up insertion performance in postgresql

Referenced from this question, the most popular answer does not mention sharding. As far as I know, postgresql will acquire auto-inc lock every time it inserts, so I think sharding can improve ...

1
2 3 4 5
249