Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,899
questions
1
vote
1
answer
46
views
Storing and Querying User Defined Data
I'm working on a system where each user defines a strongly typed definition of data they can store, consisting of classes with specified fields. Embedded classes are also supported. My first instinct ...
- 1
1
vote
0
answers
60
views
Splitting time period into multiple with irregular length, grouped by name
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
1
vote
1
answer
530
views
Splitting time period into multiple smaller ones with irregular length
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 19.9k
1
vote
1
answer
203
views
design Postgres database schema for arbitrary JSON attribute queries
In my Postgres database, I have got lots of data (a million rows) and the schema looks like this:
create table test_tb (
id int,
tags jsonb
);
A few queries to be performed on this dataset are:
1....
- 51.7k
2
votes
2
answers
210
views
PostgreSQL (TimescaleDB) asynchronous replication (WAL) doesn't work
I'm trying to setup asynchronous replication of a PostgreSQL (TimescaleDB) database. I've been following numerous of guides, and even after trying things from documentation it doesn't seem to work. I ...
- 39.9k
-1
votes
1
answer
418
views
Filesystem-layout for postgres?
Please advise with filesystem-layout to choose for PostgreSQL-Databases.
Assuming that wal is enabled. What filesystems should be built, which ones can be placed into the same FS?
I do not think it ...
- 51.7k
1
vote
2
answers
4k
views
SQL (Postgres): query array Column which must be a subset of an array
I have a table "article" and for each entry there are some "tags" stored (array column)
name | tags
test1 | {t1,t3}
test2 | {t2,t3}
test3 | {t3}
test4 | {t1,t5}
test5 | {...
CommunityBot
- 1
-1
votes
1
answer
35
views
Schema required to find a record in a table with null values
I have the following table.
ID
Country
City
Street
No
1
UK
2
2
Lon
3
Oxf
19
4
UK
Glas
5
US
NY
Wall
6
US
NY
14
7
5
In this table, I have defined some address rules. For example.
If ...
- 3,367
2
votes
2
answers
3k
views
Postgres : Indexing switches to Parallel Seq Scan from index scan when queried for date range exceeding 150 days
I have two tables - exam_data and series_data, with 300M and 900M records each. I have a query which joins the two tables. If I pull the records for the last 90 days, then the query returns within a ...
- 121
3
votes
1
answer
5k
views
UPDATE statement on UPSERT not incrementing value
I have the following QUERY:
INSERT INTO ReservationCounter (student_id, reservation_count) VALUES (1, 1)
ON CONFLICT (student_id) DO
UPDATE SET reservation_count=excluded.reservation_count+1;
When ...
- 3,367
-2
votes
1
answer
3k
views
pg_dump - aborting because of server version mismatch
El error se genero tratando de generar el DUMP:
pg_dump -h 192.168.0.56 --no-owner -U user name_db > /var/tmp/dump.sql
Output:
pg_dump: server version: 12.3; pg_dump version: 9.2.24
pg_dump: ...
Miguel Guzmán
2
votes
1
answer
766
views
postgresql Recursive Update resulting in Sequential Scan
I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update.
When using an UPDATE with a ...
- 207
0
votes
0
answers
62
views
postgres14 on ubuntu streaming replication to a freebsd
I am trying to setup my postgres streaming replication to a freebsd machine. The problem is that both use different postgres.conf files with different system locations.
I can run pg_basebackup with no ...
- 161
1
vote
0
answers
573
views
How to return query result to a tset and later process the data?
Table and values are as follows:
-- create a table
CREATE TABLE table (
id INTEGER PRIMARY KEY,
a integer[],
b integer[]
);
-- insert some values
INSERT INTO table VALUES (1, '{1,2,3,4,5,6,7,8,9,...
3
votes
1
answer
1k
views
Running parallel functions with union all
I'm trying to run 3 functions at the same time using parallelism and union all. I've read that union queries are candidates for parallelism but i can't get it to work.
The query looks like this (the ...
- 51.7k
1
vote
1
answer
748
views
Calculate running total of "consecutive days without views" for each group, that can restart multiple times within each group based on conditions
Summary
I'm working to create a counter metric that shows, for each client's slug, the number of consecutive days of zero views.
The counter should increase for each consecutive day that the client's ...
- 21
0
votes
1
answer
155
views
MS Access write conflict XMIN at (2^32)/2 -1
I have several clients running access 32 bit with a postgres database backends. A while ago, almost all the access apps started to return "write conflicts" when the end users would make changes to the ...
CommunityBot
- 1
0
votes
1
answer
1k
views
psql delete using join and return values
Hello I have this query:
DELETE FROM c_table as c
USING
a_table as a,
b_table as b
WHERE
a.id = c.id
AND b.a_id = c.id
RETURNING a.*, b.*;
but I have ...
- 176k
1
vote
1
answer
3k
views
Postgresql error syntax error at or near "END;"
Can someone explain why it throws error ERROR: syntax error at or near "END;" at this block of code:
CREATE OR REPLACE FUNCTION find_equal(object objects []) RETURNS text [] AS $$
DECLARE ...
- 41
0
votes
1
answer
4k
views
There is a lot of Client Locks and IPC Locks in PostgreSQL
I'm monitoring out PostgreSQL server using PostgreSQL Workload Analyzer. before implementing streaming replication there was a lot of IPC locks and now, after replication, there is a lot of Client ...
CommunityBot
- 1
2
votes
1
answer
3k
views
How to revert Postgres to some time ealier?
Consider this scenario, I made some update to a running Postgres database, and some bad things happen.
Now I want to rollback the database to 1 hour earlier, what's the process to achieve this?
I was ...
- 51.7k
2
votes
0
answers
230
views
Why is Postgres 9.6 using a sequential scan when an index would be more efficient
I have a function which is being used on our system. When you run the function direct on the server itself it takes milliseconds to run. However when it is run via the application it takes seconds (...
- 459
1
vote
3
answers
2k
views
Permission Denied when trying to import an sql file on my linux system
I'm trying to import a table to a database called 'test' that I've already created as the postgres superuser. The file i.e. the table is in a drive/filesystem that's already mounted.
*I'm logged into ...
0
votes
1
answer
1k
views
PostgreSQL not using varchar index when ordering by primary key
My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it ...
CommunityBot
- 1
3
votes
1
answer
3k
views
Copy Table from One Database to another database in the same cluster?
Is there a way to copy table of one database to table of another database within the same cluster?
I have a cluster with multiple database.
I have a master Database and multiple slave databases .
I ...
- 827
2
votes
2
answers
221
views
Compare signed integers as if they were unsigned
I am using PostgreSQL to store 64 bits IDs. The IDs are unsigned and make full use of all their bits (including the first one). To store said IDs, I am currently converting them to signed integers ...
- 125
0
votes
1
answer
45
views
What constraint can't be written as a table constraint?
I am reading the Postgres 7.3 documentation to add a constraint to a PostgreSQL table.
It says:
To add a not-null constraint, which cannot be written as a table constraint, use this syntax:
ALTER ...
- 84.2k
1
vote
0
answers
38
views
Return movies with their ranks based on country
I have a list of movies, each movie has a ranking for different countries. Also I have rankings from different days. For example:
Movie Name: Ready Player One
Rankings: USA #4 04/02/2018 - Canada #32 ...
- 29
2
votes
1
answer
765
views
Backfilling data using generate time series
I have a table like this:
Date
Group name
value
2022-01-01
A
1.0
2022-01-15
A
0.5
2022-01-31
A
0.2
but this contains only 3 days. I need a table with full-calendar dates, a row for every day of ...
- 176k
0
votes
1
answer
281
views
attribute 51 of type users has wrong type
I'm trying to import data to a pg table from a csv file. The error I get is:
$ psql -U postgres -c "COPY users (first_name) FROM '/users.csv' (FORMAT csv)"
ERROR: attribute 51 of type users ...
- 367
1
vote
0
answers
85
views
Do the PostgreSQL pg_* catalog tables respect transactions?
Do the pg_* catalog tables and the has_table_privilege function respect transaction isolation levels? Say in a default READ COMMITTED transaction, does the below have a race condition?
SELECT
...
- 201
0
votes
1
answer
882
views
PostgreSQL jsonb as hash tables/dictionaries?
PostgreSQL does not seem to have a native data type for dictionaries. The new jsonb (and json) type seems to have the right interface: given a key, a jsonb object returns a value.
I am just wondering ...
- 51.7k
1
vote
1
answer
1k
views
Installing pglogical: Find postgres include folder, add all include folders to C_INCLUDE_PATH
I am trying to install the pglogical extension in my postgres database, which I installed on my macOS with homebrew. I am following instructions provided on the GCP blog as I am preparing to migrate ...
- 111
1
vote
1
answer
1k
views
Proper roles for a backup-only user on PostgreSQL
I am working on new processes and policies for my PostgreSQL databases when they are first created, and I am trying to define what role attributes are necessary for a role specifically used for ...
- 51.7k
0
votes
1
answer
2k
views
How to handle possible NULL in a Trigger Updating a table without a Primary Key
I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary ...
- 3,367
2
votes
3
answers
4k
views
Postgres- update a value in a array on particular index
I have a column named ids that is of type bigint[]. ids can be null or of any length
It has values based on certain hierarchy.
My use case is I want to update value that is at a particular index.
For ...
- 3,540
0
votes
1
answer
2k
views
Dynamic CREATE TABLE AS with indexes
I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process).
One of the things I need to move are a bunch of stored procedures, that generate tables ...
- 176k
0
votes
0
answers
24
views
PostgreSQL query performing differently on different versions/VMs
I'm currently migrating a PostgreSQL database and performance on new infrastructure (v 11.12) is considerably worse than on our current infrastructure (v9.1), with the same data structure.
Query that ...
- 101
1
vote
1
answer
65
views
Constantly querying system views [Oracle, MSSQL, PostgreSQL]
I'm a kind-of-experienced database developer that just got into the DBAdministration world.
I've been told that in both Oracle and MSSQL is definitely a terrible idea to run queries such as the below ...
- 37.5k
1
vote
2
answers
3k
views
How to check that user is "db_owner" (like in SQLServer) but in PostgreSQL?
I'm new to Postgres and would like to understand how to check if a user is db_owner. I'd like to know the same for SQL Server, as well.
How can I check that user is owner of the database(db_owner)?
...
- 3,367
0
votes
1
answer
618
views
Why does PG claim that the INSERT query was successful when it was not actually?
I have this in a loop:
INSERT INTO table (...) VALUES (...) ON CONFLICT DO NOTHING;
Even if there is a conflict (duplicate record) and the ON CONFLICT DO NOTHING rule activates, it seems to return a &...
- 37.5k
0
votes
1
answer
146
views
How I can update one database schema to another database?
I have 2 databases in the same postgresql server:
mybd: used for development
test_mydb: used for testing is is preseeded with some test data that are significantly smaller than mydb
The mydb is ...
- 286
0
votes
1
answer
726
views
No speedup using parallel restore with pg_restore
I need a large database like 10TB-15TB in Postgresql for benchmarking.
I've created a smaller ~100GB sample databases with pgbench like this:
pgbench -i -s 7000 --no-vacuum exampledb
However, I ...
- 39.9k
-1
votes
2
answers
696
views
Production PostgreSQL Space Issue, Huge pg_largeobject, Newbie Thrown Off the Deep End! [closed]
Server:
PG 9.4, Linux, poorly/defaultly setup, no care and feeding
Databases:
6 user databases, all on pg_default
CT database: ~435GB
pg_largeobject in that DB
table size: 427GB
total size: 433GB
...
- 69
1
vote
1
answer
1k
views
UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"
Hey I'm trying to insert or update(if the constraints are duplicated) based on a query result, these are the create table statement:
CREATE TABLE IF NOT EXISTS public.inventory (
inventory_id serial ...
- 176k
0
votes
1
answer
1k
views
Upsert on a table that has multiple fields and jsonb column using WITH statement
So I was trying to do an upsert based on a query result:
/*
Querying from this table:
id | arrive_date | arrive_location | thing_type | thing_count
*/
CREATE TABLE IF NOT EXISTS public....
- 149
0
votes
0
answers
373
views
Foreign Key update using JdbcTemplate is very slow in Postgres
I am using Spring JdbcTemplate to update the Foreign key relationship, Primary Key of Department table has to be referred to Employee Table. One department can have same but multiple employees working ...
- 3,255
0
votes
0
answers
676
views
Syntax error in different version of Postgres
Following statement
insert into abc.ad_campaign as ac (client_id, acquisio_id, name)
values (356619, 5356522, 'Massage - Hand & Stone')
ON CONFLICT(client_id, acquisio_id)
DO UPDATE SET name = ...
- 99
1
vote
1
answer
4k
views
What is the easiest way to migrate posgresql database to another server?
I have set up my PostgreSQL database on the server up and running, but soon the server is going to be destroyed, so I need to migrate the database to another server? What is the most comprehensive ...
- 51.7k
1
vote
0
answers
2k
views
Postgresql trigger insert on another table after update and update other tables
I have a post table and when I soft delete a post I want to also soft delete rows on the comment and post_image tables using a trigger.
I have an intermediate table deleted_post where I insert the ...
- 17.8k