New answers tagged postgresql
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 ...
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 ...
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 ...
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.
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.
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. ...
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-...
-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;
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. ...
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
...
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) ...
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 ...
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 &...
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.
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 ...
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
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 ...
1
vote
Accepted
Forcing a sequential scan over the index scan
set enable_indexscan TO off; set enable_bitmapscan TO off;
solves
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 ...
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 ...
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 ...
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 ...
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 ...
0
votes
PostgreSQL function call error: structure of query does not match function result type
count(*) returns BIGINT in with plpgsql, NOT JUST INT
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....
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 ...
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 @...
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 ...
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 ...
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) ...
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)....
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
...
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 ...
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 ...
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 ...
-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.
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 ...
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....
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 ...
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 ...
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() ...
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 ...
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 ...
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(*) ...
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 ...
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.
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 ...
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 ...
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 "...
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 ...
Top 50 recent answers are included
Related Tags
postgresql × 14734performance × 1265
postgresql-performance × 766
index × 751
database-design × 688
query-performance × 526
postgresql-9.3 × 508
replication × 465
postgresql-9.6 × 439
postgresql-9.4 × 427
json × 374
plpgsql × 360
join × 351
optimization × 340
backup × 310
functions × 305
trigger × 304
postgresql-10 × 303
postgresql-9.5 × 290
psql × 277
amazon-rds × 261
postgresql-9.1 × 252
mysql × 251
array × 247
permissions × 244