New answers tagged

1 vote

"column new.total does not exist" error while creating a trigger

The Hint in the error message suggests that you have a column of the name "new.total" - containing the reserved word "new", and a dot (!). The otherwise illegal identifier forced ...
user avatar
1 vote
Accepted

Is it possible to reference an inserted column within the same INSERT?

You probably also saw the hint: HINT: There is a column named "a" in table "qwe", but it cannot be referenced from this part of the query. You cannot reference a column value ...
user avatar
0 votes
Accepted

Table with smaller data types seems to take more space on disk?

After running VACUUM FULL public.region; and VACUUM FULL public.region;, test again with: SELECT pg_relation_size('public.region'); Three possible issues: The obvious reason: table bloat from ...
user avatar
0 votes

Unexpected PostgreSQL restart

When this happened to me it was automatic updates to PostgreSQL. I validated this by looking at the /var/log/dpkg.log file and seeing that PostgreSQL was updated at the time of the shutdown.
user avatar
  • 103
0 votes
Accepted

pg_class returns 0 as relpages

Your table is so small that autoanalyze never got triggered. Since autovacuum_analyze_threshold is 50 by default, tables with less than 50 data modifications are not automatically analyzed.
user avatar
  • 32.1k
1 vote
Accepted

Marking/retrieving a long array of numbers as "true/false" in postgres

Both, bit and bit varying type can store a bit mask of 10 million bits (the upper limit seems to be 83886080). But in order to be able to set/get bits in there, you need to pre-initialize the value. ...
user avatar
0 votes

How to restore a RDS Postgres instance from a snapshot exported to S3?

I'm still waiting for AWS to provide a full PG RDS restore from snapshot stored on S3. at the moment this is the closest thing you can do, per table basis only. https://aws.amazon.com/about-aws/whats-...
user avatar
  • 11
-1 votes

Postgresql update timestamp without timezone to have timezone

One option I use to update a numeric column to be datetime ALTER TABLE table ALTER COLUMN field TYPE TIMESTAMP WITHOUT TIME ZONE USING NULL::timestamp without time zone;
user avatar
2 votes
Accepted

How to fix unicode REPLACEMENT CHARACTER � in postgres database

That character (0xEFBFBD in UTF-8) really is Unicode 0xFFFD, the “replacement character” �. So if that's not what you want, the problem must have occurred when you inserted the data into the database. ...
user avatar
  • 32.1k
1 vote

How to enforce only one NULL value per unique combination of some other columns?

I want to have only one null value for some_value when enabled is false. A minimalist partial unique index can do that: CREATE UNIQUE INDEX a_some_value_not_enabled_uni_idx ON a (1) -- constant ...
user avatar
3 votes
Accepted

Is it safe to use default value with not null when adding a new column?

That statement is safe. An ALTER TABLE will never lock "the database", it will only lock the table. Postgres will not actually rewrite the table because you provided a constant value (false) ...
user avatar
3 votes
Accepted

Why is the optimizer not using the clustering index on my table?

Your filter l_shipdate<='31/08/1998' Is not very selective, we can see from the plan that it was responsible for removing only 293,696 rows and it ended up needing to use 19,701,655. If it was to ...
user avatar
  • 2,289
0 votes
Accepted

How to enforce only one NULL value per unique combination of some other columns?

From this constraint: CHECK ( (enabled IS FALSE AND some_value IS NULL) OR (enabled IS TRUE AND some_value IS NOT NULL)); ...the "enabled" column is redundant and simply means &...
user avatar
  • 437
0 votes

Postgresql - how to use bool_and with group by

As @a_horse_with_no_name indicated in their comment, the syntax is correct. The query failed for me due to some issues with the client I used.
user avatar
  • 147
10 votes
Accepted

Do datatypes of columns affect query performance?

Yes. And probably a lot. You never gain performance using character(n)(alias char(n)). You don't gain anything at all because that type is outdated, mostly useless, and discouraged. Related: Would ...
user avatar
1 vote
Accepted

Express a predicate DATE >= X and DATE < Y in form of DATE in [X,Y)

Use a date range daterange(x, y, '[)') @> the_date The @> operator tests if the value on the right hand side is contained in the range on the left hand side
user avatar
0 votes

How to speed up subquery

You need an index on orders (o_orderdate), which should avoid the expensive sequential scan. If it doesn't, and the sequential scan is actually slower than the index scan, PostgreSQL is probably not ...
user avatar
  • 32.1k
1 vote
Accepted

Forcing a sequential scan over the index scan

set enable_indexscan TO off; set enable_bitmapscan TO off; solves
user avatar
  • 95
0 votes

PostgreSQL alternative to SQL Server’s `try_cast` function

I have accepted an answer, but I thought I might add this for posterity. Here is the version I use in my work: DROP FUNCTION IF EXISTS cast_int; CREATE FUNCTION cast_int(string varchar, planB int ...
user avatar
  • 2,337
1 vote

Passing dynamic filter to the Postgresql stored procedure

Your issue is trying to use single quotes within a string: 'name='jhon'' Here the second quote is not taken as part of the string, but the end of it. The parser then sees jhon and doesn't know what ...
user avatar
0 votes

Setup VIP for PostgreSQL Replication

What your link describes is not a true1 virtual IP address (VIP) implementation; it simply shows how to assign an additional IP address to an interface. Since it's not a true VIP, it can only be ...
user avatar
  • 21.1k
6 votes
Accepted

PostgreSQL crash due to lack of device space

You cannot know which directory belongs to which database without a look at pg_database or the file for that table (probably /var/lib/pgsql/11/data/global/1262). But you cannot start deleting files or ...
user avatar
  • 32.1k
1 vote
Accepted

Autovacuum does not clear the database

It will take a very long time to vacuum a table of that size with the those settings (assuming the ones you don't show are at their defaults). If the live tuples are really 1% of the dead tuples, the ...
user avatar
  • 31.5k
0 votes

PostgreSQL function call error: structure of query does not match function result type

count(*) returns BIGINT in with plpgsql, NOT JUST INT
user avatar
0 votes

Finding rows where one field is equal and another differs

You can use NOT EXISTS: SELECT b.site,b.date FROM table_b b WHERE NOT EXISTS (SELECT 1 FROM table_a a WHERE a.site=b.site AND b.date = a....
user avatar
1 vote

Postgres equivalent of Oracle's RMAN.VALIDATE_DATABASE

The procedure tries to ensure that the backup pieces available can be used to restore the database. I don't know if there's any equivalent for PostgreSQL. However, the only way to be absolutely ...
user avatar
  • 6,469
0 votes

Running balance of a ledger with aggregated balances

Maybe you are looking for an assignment operator query: I used table name item_data SELECT *, @running_qty:=@running_qty+quantity AS runningQty FROM (SELECT * FROM item_data i) a JOIN (SELECT @...
user avatar
  • 1
1 vote
Accepted

Why parallel aggregation is not used for subquery when other subquery using non parallel aggregation?

No, that is working as expected. If there is a parallel unsafe function used anywhere in a query, that query cannot use parallelism, period. In the words of the documentation: When a query contains ...
user avatar
  • 32.1k
0 votes

Use variable as column name

I can't let you do this. :) Don't repeatedly UPDATE the triggering row. That's hugely inefficient and error-prone. Use a BEFORE trigger instead, where you can simply assign new values to columns of ...
user avatar
1 vote
Accepted

postgresql trigger --> trigger function --> sub trigger function chain

Inside a trigger on the table thing, NEW is of the composite type thing that has the same attribute names and data types as the table columns. So, create or replace function dbo.tf2(thing_row thing) ...
user avatar
  • 21.1k
0 votes

Impose limits on number of records stored in a table

I know I can enforce maximum policy by That example seems wrong. Each row in that table is a relationship between a user and a category, it is a junction table (sometimes called a relationship table)....
user avatar
0 votes
Accepted

Greenplum add a column to table and insert value to it in incremental order

One way is altering the table using: alter table student add column NEW_STUDENT_ID serial; Result: student_id new_student_id 100001 1 100002 2 100003 3 100004 4 ...
user avatar
1 vote

PL/pgSQL package manager for Postgres libraries

The equivalent would be a PostgreSQL extension. Extensions have support for versioning and dependencies to other extensions. Sure, you need shell access to the database machine to install the ...
user avatar
  • 32.1k
0 votes

PostgreSQL query for parent - child relations

PGSQL Tree relations Hello, I just got a handle on this for a project I'm working on and figured I'd share my write-up Hope this helps. Let's get started with some prereqs This is essentially the ...
user avatar
0 votes
Accepted

Check how many Bytes does my index need to be stored?

pg_indexes_size returns the size of all indexes on the table. To get the size of the primary key index alone, you first have to find its name (probably customer_pkey). Then you can run SELECT ...
user avatar
  • 32.1k
-1 votes

Postgresql - PID in cancel request did not match any process

You get PID 24861 in cancel request did not match any process if/when at the time you do a pg_ctl restart -m fast, you had other application(s) checking out connections to the postgres server.
user avatar
  • 1
1 vote
Accepted

Compare occurence of chars in PostgreSQL string

they're similar ... if they share at least 2 chars. Unfortunately, there is no built-in "intersect" operator or function for strings or arrays. You can roll your own function to count ...
user avatar
1 vote
Accepted

Optimizing a PostgreSQL query with many small lookups

This lists all pairs of books that were actually bought by at least one person. (All other pairs were never bought together, no point in diluting the result): SELECT p1.book_id AS book1_id , p2....
user avatar
0 votes

Optimizing a PostgreSQL query with many small lookups

You can achieve the same with something like select book_1_id, book_2_id, sum(cnt) person_count from ( select b1.book_id book_1_id, b2.book_id book_2_id, 0 cnt from book b1 join book b2 on ...
user avatar
  • 2,289
1 vote

Optimizing a PostgreSQL query with many small lookups

Your query falls under this condition: The following operations are always parallel restricted: Plan nodes that reference a correlated SubPlan. The top nested loop could be parallelized, but it ...
user avatar
  • 31.5k
0 votes

How to check if the current connection is in a transaction?

From PostgreSQL version 10 and onward (i.e., all supported versions as of May, 2022), there is, besides the txid_current() function already mentioned by @Gajus, also a txid_current_if_assigned() ...
user avatar
  • 267
1 vote

How to check whether TimescaleDB view is defined `WITH DATA` or `WITH NO DATA`?

Options WITH DATA and WITHOUT DATA mean if the materialized view will be refreshed/populated during the creation (see the doc). After the creation the option doesn't have much sense, thus this ...
user avatar
  • 314
1 vote

help with improve query

It's helpful to see the explain in depesz , https://explain.depesz.com/s/nbl0, and one can see the only orange in the left hand column, the per line cost, is the part that is expensive. The second ...
user avatar
1 vote

How to check whether TimescaleDB view is defined `WITH DATA` or `WITH NO DATA`?

Not sure if this is the best way to do it but you could count the number of partitions for each table and deduce that if one has more data then it was likely enabled with WITH DATA. select count(*) ...
user avatar
4 votes

What's the most efficient way to store many small user messages in Postgres?

Postgres employs a storage technique (TOAST) that allows you to compress individual column values, but it is only suitable for large values, as it compresses each value on its own, without taking into ...
user avatar
  • 21.1k
0 votes

Serializable isolation fails even for unrelated rows

Ok, turns out I needed to create more data for my test table, otherwise postgres will opt to use sequential scan instead of index scan and thus causes conflict.
user avatar
2 votes

help with improve query

Going through the plan most of the time is spent doing a big sort of 27 million rows (5 mins 33 secs). That is never going to be fast. A good starting point is to reduce the amount of rows that really ...
user avatar
  • 2,289
2 votes

Moving from Amazon RDS to own server made queries slow (maybe because heap fetches increased?)

Looks like the visibility map gets out of date quickly, requiring the engine to visit data pages even during the index-only scan to confirm visibility of each row. May be you need to make autovacuum ...
user avatar
  • 21.1k
1 vote
Accepted

PostgreSQL did not released space "UPDATE set column = null", vacuum did not worked too

Short answer - Yes. When you update rows, you create a new version of each row that is held inside the table. Vacuum does not remove these row versions. It does make the space occupied by these "...
user avatar
  • 6,469
0 votes

How can I get a unique array in PostgreSQL?

A simple function based on @Evan Carroll's answer create or replace function array_unique (a text[]) returns text[] as $$ select array ( select distinct v from unnest(a) as b(v) ) $$ language ...
user avatar
  • 101

Top 50 recent answers are included