Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,860
questions
1
vote
1
answer
30
views
Date manipulation query conversion from mysql to postgres
I have such query in mysql
SELECT DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH)), INTERVAL 3 year)
I'm having hard time in my brain to convert this into postgres syntax. Any help ...
- 11
0
votes
1
answer
43
views
Disk space and performance issues while updating a PostgreSQL table with over 120 million rows
I have a single table with an approximate size of 85gb and roughly around 120 million of rows (in a PostgreSQL (12.8) database).
I need to update all 120 something million rows with an update query to ...
- 1
0
votes
1
answer
41
views
Logging in error states in Postgres transactions
I'm currently working on rebuilding some SQL Server stored procedures in Postgres, but I have the following hangup
CREATE OR REPLACE PROCEDURE public.math_proc ()
LANGUAGE plpgsql
AS $$
BEGIN
...
- 251
0
votes
1
answer
16
views
postgresql script to list all functions including create date and modify date
I have been deploying many functions into different databases in different servers in PostgreSQL.
I would like to have a script that would show me a list of functions in the current database including ...
- 15.3k
0
votes
1
answer
70
views
How does pgcompacttable work in Postgresql?
I would like to know how it reduces spaces. Certain forums mentions it does this by:
Moving dead tuples to the front of the table. Essentially reusing dead tuples.
Rebuilds the table and indices. ...
0
votes
0
answers
22
views
Restore only specific columns with pg_restore
I have a database dump with the table 'articles', amongst other tables. The table 'articles' contains columns 'a', 'b' and 'c'.
I have another, development database, with a table named 'articles', ...
0
votes
1
answer
17
views
Displaying a Postgres function query results in columns
I have a Postgres 12 table with a JSONB column named ‘jxl’. I can query the jxl data and get values displayed in separate columns:
SELECT jxl -> 'JOBFile' -> 'Environment' -> 'AssociatedFiles'...
- 127
2
votes
2
answers
56
views
Numeric collation sorts by digits instead of value- postgres
I have a table of users with ids in the following form user123@domain.com.
When searching and sorting the users, I need user1@domian.com to be before user14@domain.com but since 4 is “smaller” than @ ...
- 21
0
votes
0
answers
20
views
Postgres GRANT SCHEMA USAGE on all databases
I made an "admin" role in Postgres, I want to allow it to access a certain schema "foo" that will be present on all databases of my Postgres cluster.
By all databases I mean the ...
- 101
0
votes
0
answers
49
views
tuning big partitioned table to make it faster
I have a partitioned table, call it Tabel1, that is based on partition by LIST on ID_X column. This ID_X column is also indexed and it is part of a composite primary key with ID_Y column.
I do this
...
- 137
0
votes
1
answer
58
views
PostgreSQL 11.17 - backward unique index scan (DESC) is faster than ASC
I'm trying to figure out what the problem is with my Database.
I am running PostgreSQL on Azure - "Azure Database for PostgreSQL single server".
I run PostgreSQL 11.17.
I have integrated ...
2
votes
1
answer
70
views
Count how many rows have the value 1 in all given columns?
Table task1 has these columns:
workspace_id
user_id
scheduling_user
expenses_used
time_off_used
Each workspace (workspace_id) has one or more users (user_id).
scheduling_user, expenses_used, ...
- 23
0
votes
0
answers
35
views
How Update psql?
For some reason I have PostgreSQL version 13 installed, but my psql is version 10. How can I fix this?
[Tue Dec 27 22:09:13 user3@server_f ~] /usr/bin/psql --version
psql (PostgreSQL) 10.21
- 121
1
vote
0
answers
29
views
Multipile queries vs a long query with joins in my situation
I am currently working on a project where, each user can possess multipile portfolios. Each portfolio contains products, each product contains assets, which can be associated to different kinds that ...
- 11
1
vote
0
answers
35
views
Cannot access pg_largeobject table although I am the lomowner
I am looking for the best way on how to export data from the pg_largeobject table. I don't really understand what's the best way. I don't have access to the superuser, thus a pg_dump doesn't seem to ...
- 11
2
votes
1
answer
36
views
Does a multicolumn unique index support my query?
Using PostgreSQL-13, I need advice about key index. I have a table named "employee" and I manage my DB using DBeaver Community Edition (latest).
Fieldname Type
---------- ----
pid ...
- 21
5
votes
1
answer
427
views
How does offline-first database design work?
I would like to make an offline-first To-do app which works on both the mobile app (SQLite) and the web app (PostgreSQL). The app will have these business models:
User:
id
name
created_datetime
Todo:...
- 151
2
votes
0
answers
46
views
How to connect to a Postgres database cluster ( running on digital Ocean) from a linux virtual machine (running on digital Ocean)?
I have a postgres database cluster running on digital ocean, and another separate virtual machine also on the digital ocean. How can I connect to the database via CLI to the database I already have on ...
- 31
1
vote
0
answers
88
views
pg_restore error could not execute query: ERROR: relation does not exist
I am trying to restore, on the same server but different database, a specific table from a dump file using pg_restore. This table is located on public schema and I want to restore it on public schema ...
- 137
1
vote
1
answer
68
views
Find integer in array is very slow
PostgreSQL Version PostgreSQL 15.0, compiled by Visual C++ build 1914, 64-bit
This is my "victim" table.
create table if not exists "KeywordsStats" (
id bigserial primary key, &...
- 11
0
votes
2
answers
94
views
How can I optimize this SQL UNION statement?
I am using PostgreSQL 14.6 and my goal is to find out if there is simpler, and faster, way to achieve the same results but still using a similar UNION query.
This is the SQL statement I would like to ...
- 155
1
vote
1
answer
44
views
Create postgres trigger that will populate additional table column on insert
Problem
(Note: I have added potential solution to the bottom of the post)
I don't know what's wrong with the approach that I currently have, but when I try to run it dbeaver just instantly quits.
I ...
- 227
0
votes
1
answer
44
views
Best way of SELECT on large table with filter to filter out around 1m rows with indexes
I have a table billing_billcycleorders that contains a foreign key billing_cycle_id. There are around 0.9m records for a particular billing_cycle_id. I want to select the data in the chunks for ~5000 (...
- 323
0
votes
3
answers
75
views
Set custom constant in Postgres script
In Postgres 15, how can we define constants that pertain to business logic rather than db configuration settings? I'd like to define them within SQL scripts rather than passed through the psql -v ...
- 125
0
votes
2
answers
69
views
How to define constraint having two columns with boolean column to be true only once
Let the table to be considered is this:
create table users(
email varchar(50) not null,
is_verified boolean default false,
);
Now, how can I define the exclusion constraint here so that if the ...
- 103
0
votes
0
answers
46
views
Linux PostgreSQL 9.4 to Windows PostgreSQL 13
I had to switch from PostgreSQL 9.4 on Linux to PostgreSQL 13 on Windows
Normal restore was not successful.
Can somebody share experience with such case?
EDIT 1:
On linux I use:
pg_dump -c -O -U ...
- 117
0
votes
0
answers
33
views
How to swap between different postgres databases, ensuring that there's no downtime
I have some database A, and some operations take a while to process which can cause downtime. I'd like to prevent this, and have considered the following approach:
create B - a clone of database A
...
- 227
2
votes
1
answer
99
views
Installing PostGIS SFCGAL 1.4 extension in the latest official PostGIS docker image (Debian bullseye based)
I do need to use the PostGIS SFCGAL extension in its latest version (i.e. 1.4.1 at the time of writing).
This extension is based (and needs) the libsfcgal system library.
Because the official PostGIS ...
- 290
0
votes
1
answer
18
views
How to reset a cloud Postgres DB as if it was just created before each demo deployment?
I was using Heroku for projects in development.
Heroku has a great option to reset Postgres DB: https://devcenter.heroku.com/articles/managing-heroku-postgres-using-cli#pg-reset.
Now I'm using other ...
- 101
0
votes
1
answer
37
views
How does PostgreSQL resolve pg_temp?
pg_temp is an alias for current session's temporary-table schema.
Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in ...
- 101
0
votes
2
answers
65
views
PostgreSQL Multicolumn GIN Index with Ordering
I have a large table (~20M records) in a Postgres DB with a "tags" column of ARRAY type, and a "date_inserted" column. I want to be able to query the latest inserted records for a ...
- 1
5
votes
2
answers
280
views
Simultaneous aggregate count and full count
I have a table events like this:
create table events
(
correlation_id char(26) not null,
user_id bigint,
task_id bigint not null,
location_id bigint,
type ...
- 175
0
votes
1
answer
200
views
Postgres query stuck on wait_event "MessageQueueSend"
I recently upgraded a postgres database from version 9.3 to 14.5 (yes, quite a jump). The upgrade seemed successful, but I am getting an issue with a particular query (see at the end).
The query ...
0
votes
1
answer
23
views
PostgreSQL shutdown tracking
PG shutdowns are taking time.
I am using the "smart" shutdown process by sending SIGINT signal to the PG process. So, it waits for the child processes to die gracefully before exiting. Can I ...
- 11
1
vote
1
answer
238
views
postgres pg_dump + .pgpass file : no password supplied
per PSQL docs I created a .pgpass file on macOS with 0600 mask.
a.b.com:32109:mydb:user1:password1
when I list the file:
$ ls -la .pgpass
-rw------- 1 user staff 212 Dec 21 12:56 .pgpass
export ...
- 121
3
votes
1
answer
69
views
A good way to insert non-overlaping, roughly continuous integer intervals
I have a table conforming to the following basic structure:
create table ranges (range_begin int, length int);
I want it to enforce the following invariants for any insert:
range_begin is unique (...
- 133
1
vote
2
answers
101
views
How to interpret "transactions per second"?
Short version: When I execute one transaction in IntelliJ, the "transactions per second" graph increases by 10 tx/sec - I only expect it to increase by 1.
So, it seems that my understanding ...
- 223
1
vote
1
answer
82
views
Structuring a relational database to run an automatic process
I’m learning SQL and relational database design and am trying to design an affiliate system and don’t know which option to pursue. To summarise, users should be able to refer other users, and if users ...
- 65
1
vote
1
answer
23
views
Postgres archiving status from sys tables
I need a way to know what is the archiving status of the PG - how many files have been archived? How many are left to be archived? Has the archiving been failing or succeeding?
Also, the checkpointing ...
- 11
1
vote
0
answers
32
views
Resuming PostgreSQL logical replication (missing pg_stat_replication entry)
I implemented a logical replication on PostgreSQL 14 (from AWS to on-premise) via windows. A few days we experience internet lags. When i checked the pg_stat_replication in publication source, it was ...
0
votes
1
answer
34
views
Is it possible to write a generic trigger for all tables in Postgres?
I want to listen to every transaction that is recorded in the database And I noticed that there is a concept in Postgres under the title LISTEN and NOTIFY (Of course, I am not sure that this is the ...
- 101
0
votes
1
answer
39
views
What is the most efficient way to check whether a BYTEA column contains data, in PostgreSQL?
We have an online platform where we sell eBooks. In the database, we have a "books" table, which contains the books' information. It also contains the book file, stored as BYTEA.
In various ...
- 1
4
votes
2
answers
131
views
how to match pg_repack binary and db version
I am using this command sudo apt-get install postgresql-11-repack to install pg_repack(default to 1.4.8).
postgresql-11-repack is already the newest version (1.4.8-1.pgdg20.04+1).
when i run "...
- 61
1
vote
1
answer
63
views
DIV and GROUP by issues
I have one query, made it in MySQL.
And now try to run on PostgreSQL, but get syntax error twice.
Once on the DIV command, and then on the GROUP BY.
Here is my code:
UPDATE status_table
SET Status_CG ...
2
votes
2
answers
57
views
Logical replication process
While understanding the logical replication process, one thing is still not clear to me.
When the decoding process will start?
What is my understanding is,
Lets say i have a publication and a slot. ...
- 1,810
1
vote
1
answer
55
views
data archival postgres
I have this code to delete rows with column "id". I want to limit the number of rows deleted in each loop. This code deletes the first 25k rows and the next loop deletes nothing since its ...
- 61
-5
votes
1
answer
50
views
Why does PostgreSQL's SQL require this much unnecessary code for INTERVALs with placeholders?
If I try this from PHP:
database_query('SELECT * FROM whatever WHERE timestamp < now() - INTERVAL $1', $_GET['number of years back'] . ' year');
... I just get a total question mark back from ...
- 99
0
votes
1
answer
37
views
Question about PostgreSQL Logical replication behaviour
Generally when a transaction commits in PostgreSQL, then immediately the logical decoding process will start and convert the WAL data into steam of data for consumers. My understanding is
Commit » WAL ...
- 1,810
1
vote
1
answer
59
views
Using btree index with LIKE operator but not equal (=)
In summary, Postgres prefers doing the equivalent of a [datetime index seqscan]+[filter (using the heavy function)] instead of using the index when using = to find what I like. When simply replacing = ...
- 113
0
votes
1
answer
32
views
Postgresql; Max value from column A depending on column B
I'm pretty new to SQL.
I have a table like this:
id
document_id
status
date
1
1
success
01-01-2022
2
1
fail
02-01-2022
3
1
success
03-01-2022
4
2
error
01-01-2022
5
2
success
02-01-2022
6
2
...
- 1