Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,802
questions
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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....
CommunityBot
- 1
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-...
CommunityBot
- 1
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 ...
- 121
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 ...
CommunityBot
- 1
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 ...
- 23.3k
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 ...
- 21
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 ...
- 38.4k
-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-...
- 38.4k
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
----------------
...
CommunityBot
- 1
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
...
- 335
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 ...
CommunityBot
- 1
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', '...
- 221
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
------------------------------------------------...
- 68.3k
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 ...
- 38.4k
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 ...
Nir99
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 ...
CommunityBot
- 1
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 ...
- 254
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 ...
CommunityBot
- 1
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 | ...
- 23.3k
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 ...
- 101
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 ...
- 61
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 ...
- 161k
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),
...
- 38.4k
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 ...
- 161k
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 ...
- 101
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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') ...
- 1,733
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 ...
- 38.4k
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 }
...
CommunityBot
- 1
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,...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 155
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,
...
CommunityBot
- 1
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 ...
- 249
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 ?
- 105
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 ...
- 101
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 '...
- 35.5k
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 ...
- 161k
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 ...
- 161k
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 ...
- 161k
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 ...
- 38.4k
-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 ...
- 35.5k
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 ...
CommunityBot
- 1