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
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 ...
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 ...
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'...
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 @ ...
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, ...
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 ...
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 ...
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 (...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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

1
3 4
5
6 7
318