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
1 answer
210 views

How to give Read_write users access to foreign tables imported through foreign data wrapper?

I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host. In Database B, I have a set of ...
1 vote
2 answers
324 views

Optimizing table for timeseries Postgres data table

I have the below table which maintains a timeseries result. The row only becomes relevant when the signal is true, When signal is false, it just marks that for that particular timestamp we got a ...
0 votes
1 answer
601 views

How to count number of occurrences for all different values in database column and obtain 0 if are there empty result?

I'am newby with postgresSQL. I have a table with this structured: city | type | quantity | sellout | sellout_update "ACIREALE" | "Man" | "3" |"1100.0" |"366....
0 votes
1 answer
255 views

Same postgres query in two different instances with the same data but with different times

I have the same postgres query running in two different instances restored with the same dump file: one instance in aws rds => https://explain.depesz.com/s/USMO ('PostgreSQL 11.10 on x86_64-pc-...
2 votes
0 answers
21 views

Postgres check constraint column name

I am trying to list the check constraints for a column in a postgres table. I am using postgres 14.1 Given this table: CREATE TABLE IF NOT EXISTS public.test_change_column ( address CHARACTER ...
1 vote
1 answer
5k views

Why are my postgres logical replication workers crashing after syncing a certain amount of data?

I am trying to setup a logical replication between two postgresql 12 DBs run in Docker containers on AWS ECS. I gave the task 2 vCPU and 6 GB of ram so quite luxurious for a DB with ~1e7 rows in two ...
12 votes
4 answers
10k views

How to change definition of a Postgres GENERATED (stored) column

How to change definition of a Postgres 12 GENERATED column? CREATE TABLE test ( id serial PRIMARY KEY, val_a int4 NOT NULL DEFAULT 0, val_b int4 NOT NULL DEFAULT 0, val_sum int4 ...
1 vote
1 answer
46 views

Postgresql: Drop detached partitions

I am trying to implement an automatic archiving system for some partitioned tables in pg14. The process I am trying to implement is as follow : I start with several tables partitioned by date with ...
1 vote
0 answers
12 views

Unable to create and install pgAgent service

I'm trying to install pgAgent on my device. During the installation using the stackbuilder, the last step requires me to add the operating system username and password as below: However, after ...
0 votes
1 answer
21 views

How to get exact match when the column used for filtering is comma-separated list in postgresql

I have a column which will have values like: FC FCW FC,FCW FX,FC My input used for filtering should give correct result (match the exact value in the column) ex: FC like 'FC,FCW' --> true FC like '...
1 vote
1 answer
17 views

Backing-up Postgresql data when only root login is available (Ubuntu)

I have a Ubuntu-based workstation with several boot issues that prevent any user login - only logging in as "root" works. Before formatting the Ubuntu partition to perform a clean ...
-1 votes
1 answer
36 views

How is data represented in postgresql internally

I've been using MongoDB and I know that it uses the Bson specification to serialize data into bytes, uses (and stores) it. This data is then transmitted to the client where the client's driver de-...
2 votes
2 answers
26 views

How to extract the subquery of an update script into a CTE?

I store groups and their members in two tables where the member table references the group_id as a foreign key: CREATE TABLE group_table ( group_id INT, group_name VARCHAR(8), ...
0 votes
1 answer
578 views

Docker postgres work_mem not using ram

I have set shared_buffers to 256MB. Using pg_buffercache extension I see that all of the buffers are in use: SELECT pg_size_pretty(COUNT(*)*8192) as used FROM pg_buffercache; used ---------------- ...
2 votes
0 answers
23 views

Postgres First Replica has expected safe_wal_size, 2nd replica does not

Using Postgres 14 on Ubuntu: I have logical replication setup from master => slave => slave2; It is replicating, but there is a problem with safe_wal_size. Using du -sh /var/lib/postgresql/14 ...
0 votes
1 answer
371 views

pg replication connection not returns wal events to java code client , it hangs some times

Describe the issue We are facing issue of pg replication slot not listen database wal events. Once pg database restarted and we attach Driver Version: 42.2.6 Java Version: JDK1.8 OS Version: Window ...
0 votes
2 answers
1k views

How to quote arrays in postgres copy statement to insert into JSON field

I have some data that looks like this: id,bo,c,url 1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]" 2, "more text", 83, "[{'id': 'W3nAl', '...
0 votes
0 answers
41 views

problem on replication EFM (WAL LSN not match)

I have a postgres cluster (edb) , when i check the status i have this output : Cluster Status: efm Agent Type Address Agent DB VIP ------------------------------------------------...
0 votes
1 answer
28 views

How to index a Postgres Full-Text Search across relations

I'm in the process of adding indices to support full-text searching in our PostgreSQL (12) database. I noticed that everything works well if all searched columns are in the same table, but that is not ...
0 votes
0 answers
23 views

Why is the query getting slow after few executions?

As part of my code I'm using postgres and doing the following: (starting from a clean table) Adding a lot of records to a table (around 1M) Performing analyze on the table Executing the query ...
1 vote
1 answer
1k views

Reset all sequences so that they continue after max(id) + 1?

It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like: django.db.utils.IntegrityError: duplicate key value violates ...
7 votes
1 answer
1k views

Pass additional data in connection for use by PGSQL Trigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started. What I would ...
0 votes
1 answer
298 views

How to use a recursive CTE to get ancestors in a hierarchy

create table division ( id serial primary key, name varchar not null ); -- nested sets table create table location ( id serial primary key, name varchar not null, division_id integer not ...
0 votes
1 answer
15 views

How to update the "Description" column of existing user-defined data types?

Say I have an existing user-defined data type like this: my_db=# create type foo as (id int); CREATE TYPE my_db=# \dT+ foo List of data types Schema | Name | ...
0 votes
0 answers
17 views

How to use multiple arguments in ANY query when is bind message supplies parameters trhowing error?

I have the follow query that is using one parameter that cointains a lot of valid values to validate with ANY, rigth now I need to cast the id from bigint to int and validate with integers (This is ...
0 votes
0 answers
19 views

Source a synonym dictionary from a table in PostgreSQL

I am keeping a table with numerous aliases for a given canonical product name. I would like to use that table as a synonym dictionary for full-text search. Unfortunately, PostgreSQL wants to load the ...
0 votes
0 answers
25 views

How to accomplish this kind of basic "local replication" in PostgreSQL 14 on Windows 10?

I start (and restart) PostgreSQL like this: pg_ctl restart -D "C:\Users\John Doe\Documents\PGDB" I am trying to also start three "replication PostgreSQL servers" on the same ...
51 votes
1 answer
45k views

Indexes: integer vs string performance if the number of nodes is the same

I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
0 votes
1 answer
12 views

How to put a condition in the conflict_action of an Upsert?

I have a table like this: CREATE TABLE mytable ( pk1 varchar(4), pk2 varchar(4), col1 varchar(7), col2 varchar(6), ...
11 votes
2 answers
15k views

PostgreSQL full text search on many columns

I need an advice with searching of record based on specified string. Search strings can contain values from these columns. Values in this string don´t have to be strictly identical given in the ...
29 votes
4 answers
47k views

postgres - pg_dump and pg_restore without roles

I'm trying to restore a dump without having the appropriate roles on the receiving database. As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or ...
2 votes
1 answer
1k views

How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information?

Postgresql full text search to_tsvector() function returns a sorted array of lexemes. I need exactly the same function, but returning unsorted array of lexemes. Is there any such postgresql function ...
1 vote
1 answer
259 views

Loop Block with execution doesn't persist

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist. Like this... do $$ declare dados Record; begin for ...
2 votes
3 answers
466 views

"ERROR: column "a" does not exist" when referencing column alias

I am attempting to retrieve either a null or boolean value from a query. Here is my query: SELECT EXISTS (SELECT 1 FROM employee where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6') ...
0 votes
2 answers
22 views

PostgreSQL 9.6 version server unintentionally upgraded to version 14.5, now database cannot start, what to do?

This is not a production system but it happened on my development laptop. I have PostgreSQL running on cygwin basis on a Windows 10 platform. The other day I was going to just add some other cygwin ...
1 vote
2 answers
1k views

Update all the elements of JSON array nested in a JSON column (Postgres)

I have json column with data that looks like this: CREATE TABLE foo(mycol) AS SELECT $${ "a": [ { "q": 5 }, { "q": 8 }, { "q": 10 } ...
0 votes
1 answer
290 views

Optimizing big sorted join (nested loop) based on value frequency

I have a query involving two tables, let's call them timeline and events, defined as follows: create table timeline ( event_id int, ts timestamptz ); create table events ( id int primary key,...
2 votes
2 answers
4k views

Postgres Windows post-install "The database cluster initialization failed"

I've seen many solutions and have been working on this issue for hours now. After installation the post-installation script fails with the message above. This is a development installation of ...
1 vote
1 answer
42 views

Postgresql - long column select/projection list leads to poor performance

I have a rather structurally "simple" query that consists basically in these clauses: SELECT [650 columns here] FROM MAIN_TABLE (Several Joins) WHERE (Some conditions in ...
0 votes
1 answer
224 views

Query All Tables In PostgreSQL Database For Text

I do not have write privilges on the database so I can not create a proc to run. I was originally going to use this function CREATE OR REPLACE FUNCTION search_columns( needle text, ...
1 vote
0 answers
16 views

Improve cardinality estimation when two joined tables have a condition on an identical column

I have a table foo, with an association table foo_associations with columns foo_parent and foo_child. foo has an column called department_id. Any association made through foo_associations is ...
35 votes
2 answers
53k views

postgresql: how to define a JSONB column with default value

I am unable to find in the documentation how to create a JSONB column in PostgreSQL that has a DEFAULT value of an empty json document. How the above can be stated in the CREATE TABLE definition ?
3 votes
2 answers
22k views

Insert- and Read-only table in Postgres

I want to create an "immutable" Postgres database, where users can insert/select (write/read) data only, but can not update/delete (change/delete). I came to know there is FOR UPDATE lock, but did ...
0 votes
0 answers
14 views

how do you condition on column of type jsonb in hibernate

I have a table definition given below filled with random text. I wanted to fetch the records based on the collection of tags. For example, I wanted to pick the records whose tags are 'social' and '...
4 votes
2 answers
5k views

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not ...
10 votes
1 answer
14k views

Have Postgresql query planner use nested loop w/ indices over hash join

I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
0 votes
2 answers
38 views

How to implement "SELECT INTO" in Oracle with PostgreSQL commands?

I have seen many explanations about SELECT INTO in the oracle clause. I found a difference in applying SELECT INTO in Oracle and other SQL databases. In Oracle, the SELECT INTO statement retrieves ...
0 votes
1 answer
32 views

PostgreSQL backup help

In PostgreSQL backup, the file large ( more than 500 GB ) And I need to do daily backup. Have any option, how I not need to backup all of the 50 GB plus the changes daily? Only download the big one ...
-1 votes
0 answers
31 views

Can a delete operation fail due to a concurrent update?

For a given table, I am seeing scenarios where one thread is trying to update (not upsert) a row by unique ID, while another thread is trying to delete that same row. Assuming both transactions start ...
0 votes
1 answer
499 views

best performance for storing notifications in postgresql

I have a question concerning the performance of 2 different options, which one is better? Explanation: I have 5 tables in a postgresql db: users, followers, posts, likes and comments. They look like ...