New answers tagged

0 votes

What are disadvantages of using locktable?

The idea to synchronize application threads via regular row locks in a special table is a bad one. The reason is that you need to keep a database transaction open as long as you want to hold the lock, ...
Laurenz Albe's user avatar
  • 50.5k
1 vote

Identify if the sequence of visits to an area id was interrupted at any point

Assuming all involved columns NOT NULL, and no duplicate timestamps per area_id. Undefined aspect: what if the same area_id is visited again (after a gap) and it starts over with trip_nr = 1. Still &...
Erwin Brandstetter's user avatar
0 votes

Reverse Byte-Order of a postgres bytea field

Was curious if there's a simple way to reverse bytea strings without dealing with hex or regex. In case someone finds it useful, here's what I came up with: create or replace function bytea_reverse(...
Mr. Curious's user avatar
1 vote

Postgres 14 and pg_trgm index on primary key column make join operations super slow

I can confirm this is still going on in postgres 16.1. We have the same structure where we want to do FTS search on customer_id column and have added a GiST trgm index on it, after upgrading to ...
Kim's user avatar
  • 11
0 votes

how to refresh table in pg-admin 4?

Just click the "play" (Run query) button over the table tabs in the editor. This will make the query run again and therefore, the data will be refreshed.
sdomoszlai13's user avatar
3 votes

ERROR: duplicate key value violates unique constraint "sometable_pkey"

I want to reduce the size the db occupies, by changing the data types of varchar (yes/no domain), to int. That would actually increase the storage size on disk if anything. integer occupies 4 bytes ...
Erwin Brandstetter's user avatar
3 votes
Accepted

trivial query very slow on primary key on a PostgreSQL DB (instantaneous on MySQL)

I assume that you have an index on id. If not, you need one. Your problem is the 1e9. If you write a numeric constant with the scientific notation, it is considered to be of type numeric: SELECT ...
Laurenz Albe's user avatar
  • 50.5k
2 votes

Missing collation cs_CZ.utf8

Since this is CentOS: install the glibc-all-langpacks package restart PostgreSQL import the new collations with SELECT pg_import_system_collations('pg_catalog');
Laurenz Albe's user avatar
  • 50.5k
1 vote

Alter all tables in schema to set an existing field as an IDENTITY PRIMARY KEY

I think this can do the job: SELECT format( 'ALTER TABLE %I.%I ADD PRIMARY KEY (objectid); ALTER TABLE %I.%I ALTER objectid ADD GENERATED ALWAYS AS IDENTITY;', table_schema, table_name, ...
s.k's user avatar
  • 352
1 vote

Postgres Sharding

Would it then be possible to allow an application such as a web framework, knowing what tenant it is serving, to then connect to and query (including reading and writing) the respective server with ...
mustaccio's user avatar
  • 25.7k
0 votes

How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?

First, I exported a plain file in WIN1252 encoding: $ sudo -u postgres pg_dump "OLD-DB" -F p -E WIN1252 -f /tmp/db.dumplain I dropped wrong characters to UTF-8: $ sudo -u postgres iconv -c -...
alboforlizo's user avatar
1 vote
Accepted

Behavior of PostgreSQL checkpoint_timeout when the previous checkpoint is still in progress

Only one checkpoint can run at a given time, so if the previous checkpoint is still in progress, the next checkpoint has to wait until the first one is done. The second checkpoint should begin soon ...
Laurenz Albe's user avatar
  • 50.5k
1 vote
Accepted

Can a BRIN index be used instead of Table Partitioning in Postgres?

In this particular scenario, the key distinction between employing BRIN (Block Range INdexes) and table partitioning lies in their impact on table size. Table partitioning effectively reduces the ...
cevian's user avatar
  • 46
0 votes

Postgresql with just index storage

The way PostgreSQL stores data, it keeps transaction information in the heap table so it is impossible to store data just in a BTREE. The heap is necessary. To have a high transaction rate, Postgres ...
cevian's user avatar
  • 46
3 votes

How to calculate the division of two SELECTs in Postgresql?

Of course, while both counts are based on the same table(s), you would do that in a single SELECT, which is typically much cheaper. Use the aggregate FILTER clause. See: Aggregate columns with ...
Erwin Brandstetter's user avatar
-1 votes

Compress PostgreSQL Database

The anwer by Evan lists most of the methods. But since your data has GIS, it's probably sensor-like data that comes in over time, i.e. time-series data. In that case, you may want to consider ...
cevian's user avatar
  • 46
1 vote

Compression of table - what are my options? (postgres15)

In plain Postgres, your only option is TOAST for large attributes. You can, however, use Timescale to compress a table like your example above (more below). TOAST only allows compressing large ...
cevian's user avatar
  • 46
3 votes
Accepted

Catching exceptions and rolling back transactions

The whole procedure runs inside a single transaction automatically. Except, if you execute COMMIT, which commits everything so far and starts a new transaction. So, no, you do not have to catch ...
Erwin Brandstetter's user avatar
1 vote
Accepted

How to calculate the division of two SELECTs in Postgresql?

SELECT AVG((col_name > crit)::INT) FROM table this creates a syntax error when I tried it Your expression does not contain outer SELECT. To make it correct you'd write SELECT (SELECT COUNT(*) ...
Akina's user avatar
  • 19.7k
-1 votes

Why is it so fast to create an index on a table?

Usually create a index on one column is much faster than you thought it should be. For example when you create an index on User.name, usually the column will not exceed 31bytes and row will not exceed ...
ramwin's user avatar
  • 99
1 vote
Accepted

How can I minimize data loss & data transfer when I failback with the requirement that I promote the old primary back to its primary status?

Before I answer your question, let me remark that you forgot a very important step in your failover procedure: between steps 1 and 2, you have to make dead sure that Constanza is down. This may seem ...
Laurenz Albe's user avatar
  • 50.5k
0 votes

Generate a series of monotonically increasing and decreasing integer values without duplicating them at the boundaries of the intervals

This is how custom SQL functions are born. Create the first function: DROP FUNCTION IF EXISTS generate_numbers_interval_as_descending_order( k integer, i integer) CREATE OR REPLACE FUNCTION ...
Cyril Mikhalchenko's user avatar
0 votes

Postgres trigger to update records of several parent tables based on values of a many-to-many join table referencing those parent table records

Here is what I currently achieved, it's working, but probably not perfect so feel free to improve this idea if you think there are drawbacks: CREATE OR REPLACE FUNCTION schema.set_referenced_state() ...
s.k's user avatar
  • 352
0 votes

How to make this postgresql query to use composite index only ? Any Suggestion

The subqueries in the SELECT list are what is expensive; they are executed for each result row. Use the power of filtered aggregates: SELECT fi.runid, count(*) FILTER (WHERE action = 'Add' and ...
Laurenz Albe's user avatar
  • 50.5k
2 votes

Generate a series of monotonically increasing and decreasing integer values

Several approaches to answering this (interesting) question were discussed here. You might be interested in exploring these answers in order to gain a better understanding of the topic (and PostgreSQL ...
Vérace's user avatar
  • 29.7k
0 votes

What's the most efficient UUID column type

Very late, I know, Vlad. UUIDv7 is excellent for database inserts as it supports decentralized generation as well as efficient clustered index insertion.
Andrew G's user avatar
  • 111
2 votes
Accepted

Generate a series of monotonically increasing and decreasing integer values without duplicating them at the boundaries of the intervals

You should be able to adapt the answer given to you previously by excluding repeated values: WITH s AS ( SELECT n, x, lag(x) OVER (ORDER BY n, ((n%2 * 2)-1) * x) l_x FROM generate_series(1, 3) ...
mustaccio's user avatar
  • 25.7k
0 votes

Should you include the primary key in an index, if the column is used in a condition?

If you have several processes inserting concurrently, and you use sequence caching to speed it up, it is possible in some cases to have a lower id inserted after a higher id. Here's an explanation. If ...
bobflux's user avatar
  • 1,436
1 vote
Accepted

How to improve performance for this query ? Any Suggestions

Creating a test setup: CREATE UNLOGGED TABLE bigtable ( runid INTEGER NOT NULL, type TEXT NOT NULL, action TEXT NULL, status TEXT NULL ); INSERT INTO bigtable ...
bobflux's user avatar
  • 1,436
1 vote

PostgreSQL enforcing order dependent constraints within a query

"If, But, Maybe and Unless ..." style thinking is not good for databases to deal with. If a field is supposed to contain JSON, then it should always contain JSON, even if it's an empty ...
Phill  W.'s user avatar
  • 8,578
0 votes
Accepted

effective_cache_size vs shared_buffers

These two parameters are unrelated. shared_buffers is the size of shared buffers, the shared memory segment that is allocated when PostgreSQL starts and caches all data that are read from or written ...
Laurenz Albe's user avatar
  • 50.5k
0 votes
Accepted

Are default values for a column still required if using audit triggers?

That won't work (well) with an AFTER trigger. You need a BEFORE trigger that modifies the rows before it is added. Then you don't need to worry about DEFAULT values, because constraints and ...
Laurenz Albe's user avatar
  • 50.5k
0 votes

(PostgreSQL) Insert a tuple containing specific value in a column linked to a sequence

You get the error because the column is created with "GENERATED ALWAYS". If you use "GENERATED BY DEFAULT" instead then you can insert values into the column, and in this case it ...
bobflux's user avatar
  • 1,436
1 vote

Optimizing Reads on versioned table with multiple ANDed "IN" clauses

Test data: CREATE UNLOGGED TABLE resource_operation ( id bigint not null primary key, isdeleted boolean not null, rid INTEGER NOT NULL, --rids are just ...
bobflux's user avatar
  • 1,436
1 vote

Optimizing Reads on versioned table with multiple ANDed "IN" clauses

The query itself should be simple: SELECT * FROM (SELECT DISTINCT ON (rid) * FROM resource_operation WHERE actorrid IN ('ID-1', 'ID-2') AND resourcerid IN ('ID-3', 'ID-4', 'ID-5') ...
Laurenz Albe's user avatar
  • 50.5k
2 votes
Accepted

(PostgreSQL) Insert a tuple containing specific value in a column linked to a sequence

Fortunately, PostgreSQL error messages are quite helpful. Following the hint shown by the PostgreSQl engine, I added the instruction "OVERRIDING SYSTEM VALUE" and it worked flawlessly: ...
ValerioOliveira's user avatar
0 votes

Calculating memory size required for an index

Let's start with the page size assumption, as you don't need to guess it: 8kB is the usual size but because this places a limit on the tuple size it can be adjusted. Your... rather simple ... record ...
Nick F's user avatar
  • 1
1 vote
Accepted

Generate a series of monotonically increasing and decreasing integer values

SELECT n FROM generate_series(1, 10) n -- number range , generate_series(1, 10) x -- how man times? ORDER BY x, ((x%2 * 2) - 1) * n; fiddle Produces your desired result.
Erwin Brandstetter's user avatar
1 vote
Accepted

Update selected columns containing 0 with NULL

Fist ask yourself if a table structure with a single value column like suggested by Akina in the comments wouldn't be more practical. While stuck with your current design, you need dynamic SQL to ...
Erwin Brandstetter's user avatar
0 votes

Point in time Postgres restore

The log looks like recovery went well. If you are missing some data, those were probably stored in the current WAL segment, which was not yet archived. You can either perform a clean shutdown, which ...
Laurenz Albe's user avatar
  • 50.5k
1 vote
Accepted

Merging information from multiple rows into one row

If I were you, I would lose the JSON and use the PostgreSQL ARRAY type - PostgreSQL has very powerful array processing capabilities which might be better suited to your needs. JSON is usually for &...
Vérace's user avatar
  • 29.7k
1 vote

Merging information from multiple rows into one row

You can use a correlated subquery where you aggregate JSON from the other tables. SELECT e.event_id, e.event_name, e.event_date, ( SELECT jsonb_agg(to_jsonb(p.*)) FROM ...
Charlieface's user avatar
  • 12.6k
0 votes

`RETURN NEXT` vs `RETURN RECORD` in PostgreSQL

A RETURN NEXT statement: can append zero or more rows to the function's result set one by one and the rows can be modified one by one. cannot exit a function while a RETURN statement can. can work ...
Super Kai - Kazuya Ito's user avatar
0 votes
Accepted

Finalize GroupAggregate parent using Partial HashAggregate child nodes in Postgres query plan

As the other answer explains, the individual child workers have duplicates between them. The parent needs to remove these duplicates. Both hashing and sorting are valid methods to remove duplicates. ...
ahron's user avatar
  • 793
0 votes
Accepted

Need a Role that can CREATE USER but not allowed to GRANT Predefined Roles in PostgreSQL <16

I was able to figure out a workaround with supautils library which exists just to solve the problem I have here. As I'm doing this to resolve a security issue, creating functions with SECURITY DEFINER ...
imans77's user avatar
  • 111
0 votes

How to know the locale that has been used to initialize a Postgres server?

When upgrading to postgresql 16 or later, this is no longer relevant. The locale and encoding match requirement has been removed as redundant, pg_upgrade now copies the encoding settings itself: https:...
Melkij's user avatar
  • 2,999
0 votes

PostgreSQL plan cache and variable number of arguments

Q: what is the key for plan cache in Postgres? It's the prepared statement associated to the query if there is one, and it does not happen automatically. Outside of prepared statements, there is no ...
Daniel Vérité's user avatar
0 votes

How to store historical correlation matrices?

If you can't use the advanced features, you can create a table with rows like this. AutoIncrement Key Instrument A Id Instrument B Id Correlation between A and B
Rohit Gupta's user avatar
  • 1,598
0 votes

Point in time Postgres restore

Clearly, your server went beyond the point to which you're trying to restore, so you can't revert it back to what it was at a prior point in time without restoring a base backup from an even earlier ...
mustaccio's user avatar
  • 25.7k
1 vote

Finalize GroupAggregate parent using Partial HashAggregate child nodes in Postgres query plan

The parallel subplans can potentially return duplicates (in relation to the group key): since the blocks coming out of the Parallel Seq Scan are not distributed between the parallel workers by the ...
mustaccio's user avatar
  • 25.7k

Top 50 recent answers are included