Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,898
questions
0
votes
2
answers
126
views
Debug why Postgres wants to use an uninstalled extension for a function
I recently moved a Postgres function app_user_id from an old, custom extension called intpair into a new, custom extension called heap_exts. This worked well except for machines I restored from a ...
1
vote
1
answer
476
views
rename a column without disrupting users
You can rename a column using the following statement:
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
However, this brings the old column out of commission immediately. Any use ...
0
votes
1
answer
789
views
How to execute a trigger procedure with an expression as an argument?
How can I pass an expression as an argument when calling a trigger function?
create trigger my_trigger
after insert on my_table
for each row
execute function trigger_job(
'job_name',
(...
4
votes
1
answer
10k
views
What is Postgres lock behaviour when UPDATE WHERE clause does a table scan?
Say you have a large table with tens of millions of rows.
You want to UPDATE large_table SET col=value WHERE col=other_value... but col is not indexed and an EXPLAIN shows that this query will perform ...
1
vote
1
answer
460
views
Postgres: Why is my delete query using up all available disk space?
I have a postgres table set up like so:
Table "public.facts"
Column | Type | Collation | Nullable | ...
0
votes
1
answer
371
views
Encounter installation problems on postgres 12
i install postgres-12,but report this error,i have execute sudo systemctl restart postgresql-12 ,and follow this install postgres12 on centos 7, and when i step into Step 5: Enable remote access,and ...
0
votes
1
answer
309
views
Unable to delete a referenced row due to a trigger on a referencing table?
I ran into some problems in using triggers to ensure only deletions from cascading deletes:
CREATE TYPE status_type AS enum ('A');
CREATE TABLE parent(
name VARCHAR(255) PRIMARY KEY
);
CREATE ...
1
vote
1
answer
109
views
Join tables and summarize rows based on condition
I have two tables.
One containing countries with an ID and the geometry
The other containing customers with a name, geometry and a boolean value showing if they are active or not.
Countries
iD ...
1
vote
0
answers
36
views
Using one sequence across all tables in PostgreSQL [duplicate]
I propose using a single sequence for the bigint identity columns of most or all tables. (Essentially, OID, except 64-bit.)
This helps prevent security problems for a multi-tenant application that ...
-2
votes
2
answers
470
views
How to find out WHERE a PG error/warning/log happened?
I have PostgreSQL CSV-log its "errors" according to this structure: https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
It doesn't include the file ...
0
votes
2
answers
43
views
filter table rows within range of related column
I have a postgres database with the following table:
CREATE TABLE data (
id integer PRIMARY KEY,
group_id integer NOT NULL,
value double
);
the data contained in it is ...
5
votes
1
answer
290
views
Determine Nodes in Network with PostgreSQL
I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes ...
1
vote
1
answer
674
views
How to define a parameter that will be used as an IN argument inside a function?
I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and ...
0
votes
1
answer
157
views
Fetch partitions where values are present
I have table partitioned by range of dates (monthly).
Having a list of dates, I want to select all the partitions, where values from the list of dates are present. That is to avoid scanning the ...
1
vote
1
answer
3k
views
postgresql multicolumn partitioning
Postgres 11
I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys.
The reason is that:
I want to prune months that are not longer ...
15
votes
1
answer
7k
views
What is "special" about PostgreSQL update vs delete+insert
My understanding is that an update locks a tuple, marks it as deleted, and then adds a new tuple.
In other words, update = delete + insert.
Or so I had believe. But it appears there something ...
0
votes
1
answer
4k
views
Get the last ID after INSERT the PostgreSQL database with PgBouncer
Previously we used PostgreSQL 9 with PgBouncer in session mode, and with that was able to easily create a new record and get the last ID with pg_get_serial_sequence and CURRVAL.
INSERT INTO "...
1
vote
0
answers
535
views
Bottleneck when inserting many rows into different tables
TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain....
4
votes
1
answer
397
views
Is there a way to allow only DELETES from CASCADES only?
I want to prevent anyone from deleting certain records from a table, except for when the parent record in another table gets deleted, then should it be deleted. Can I use triggers for this purpose?
0
votes
2
answers
702
views
How do I migrate Postgres database using an intermediate file
I have a PostgreSQL v9.5.23 database on an Ubuntu 16.04 server that I want to migrate to a new Postgres 12.4 installation on an Ubuntu 20.4 server.
The network pipe recommended by the Postgres docs ...
5
votes
2
answers
2k
views
Auto vacuuming high-write, high-update, and mostly read table types
What are good auto vacuum settings (recommendations) for tables like:
High Write Table Insert load
Range between 30-10,000 inserts in a day. The table can idle for weeks without load, but can get ...
0
votes
1
answer
2k
views
logical worker replication says the publication does not exist, however this seems to actually exist?
I'm trying to setup logical replication between two database instances.
I've created a publication on the main db, and a subscription on the replica. Specifically:
on main db:
create publication chris ...
0
votes
1
answer
81
views
How to monitor general performance and wellbeing of a database?
I've read @Mike answer to How to tell if database tuning is fruitful (PostgreSQL), and that's the kind of approach I'm thinking about for monitoring the performance of my database (of a web ...
1
vote
1
answer
7k
views
How to set statistics level of a table in postgres?
I know how to set statistics level for a particular column i.e
alter table table_name alter column column_name set statistics 100;
However, if I wish to set statistics level for a particular table or ...
0
votes
1
answer
39
views
Postgresql Create ForeignKey with repeated table name in value of ref contrain key
I am working on a database which does not contain any foreign key. When i open table I see strange format key because table of parent reference is stored in the value.
exemple
CREATE TABLE foo
(
...
0
votes
1
answer
61
views
Downsampling to (eg) "last result on Sunday night"
We have a largish PostgreSQL table that records user's disk usage.
id | bigint | | not null | (unique constraint)
time | timestamp without time zone | |...
0
votes
2
answers
1k
views
psycopg2 connect error: cannot pass more than 100 arguments to a function?
I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error :
Error :
psycopg2 connect error: cannot pass ...
0
votes
1
answer
286
views
Can sharding speed up insertion performance in postgresql
Referenced from this question, the most popular answer does not mention sharding. As far as I know, postgresql will acquire auto-inc lock every time it inserts, so I think sharding can improve ...
3
votes
1
answer
1k
views
Searching INET[] for CIDR match, efficiently using indexes (without UNNEST)
This question stems from having recently discovered that the following are very, very different in terms of performance
DROP TABLE IF EXISTS dns_lookup;
CREATE TABLE IF NOT EXISTS dns_lookup (
...
2
votes
1
answer
837
views
Query resource quota for PostgreSQL accounts
Within the latest (12, 13) versions of PostgreSQL, is there any throttling support on the user-account level, to limit how much data any query can produce and/or how much time + memory it may consume ...
0
votes
1
answer
368
views
Best way to solve a lot of queries stuck because of no index
Environment
PostgreSQL 9.4
16 GB RAM 8 Cores
max_iops: 8000
What problem I encountered
Last night our production environment database encountered a large amount of traffic, whcih caused the cpu ...
2
votes
1
answer
844
views
Monitor FOR loop progress in postgres
I have a Postgres loop as follows, which I am running from the command line via psql:
DO
$do$
BEGIN
FOR i IN 1..20000
-- update some rows
LOOP
END LOOP;
END;
$do$;
Is there any way to ...
0
votes
2
answers
337
views
pg_stat_monitor: see placeholders instead of actual query
I have started using pg_stat_monitor extension from : https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html with my postgresql database.I have a problem here. ...
-1
votes
1
answer
94
views
How do I log the actual script's name in csvlog? [duplicate]
I have PostgreSQL CSV-log its errors according to this structure: https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
Sadly, from what I can tell, it neither ...
0
votes
0
answers
80
views
How I can find the most resource-intensive queries that has ever run in my database and use a specific table?
In my database I want to check whether my idea on an improvement upon table schema works. Therefore I want so look which are the heaviest select queries ever run into our database and use a specific ...
1
vote
2
answers
696
views
can any tweaks improve full table scan time in Postgres/Cloud SQL?
I have a table with 150 million rows in a Postgres database hosted in Cloud SQL (in Google Cloud Platform).
Typically I obviously try to avoid scanning the table, but occasionally I will manually want ...
0
votes
1
answer
1k
views
When I set Datestyle to 'SQL, YMD' all queries still return as 'SQL, MDY'
I am working on a database migration from Oracle 12c to Postgresql 12.
We must keep the same date format as the program interacting with this database is written in COBOL and should not be changed.
...
1
vote
0
answers
37
views
Expected function to return very few rows, but returns all rows
I am creating a Postgres/postgis function that is suppose to cut an input-geometry according to a 'hard-coded' table's geometry. The hard-coded table is called j.borders and its geometry attribute is ...
0
votes
1
answer
180
views
Does Postgres read blocks sequentially, even if a disk is high-latency?
I'm using Postgres on AWS EBS, when running a query that uses a bitmap heap scan and a bitmap index scan, the execution time seems proportional to the number of "heap blocks" read, as shown ...
1
vote
1
answer
28
views
Would it be correct to assume that inner query ordering will follow into my outer query?
Is it spelt out someplace in the documentation that if I do this:
SELECT *
FROM (
SELECT *
FROM foo
ORDER BY bar ASC
LIMIT 1000
)
LIMIT 10
OFFSET 10
then the inner query ordering will carry ...
1
vote
2
answers
2k
views
Why is PostgreSQL Not Using My Index?
I have the following query:
SELECT "account_transactions"."subsidiary_id"
FROM "account_transactions"
WHERE (transaction_type = 'loan_payment'
AND amount > 0
...
0
votes
1
answer
4k
views
How do I select row which has a max value in other column - PostgreSQL?
I'm just starting with PostgreSQL and I bet there is a simple answer for my qs.
I have a view like:
county - candidate - votes
Abbeville - John Kasich - 157
Abbeville - Ben Carson - 305
Abbeville - ...
0
votes
1
answer
90
views
How I can place connection metadata in order to trace my queries on RDS performance insights fot my postgresql db?
In my infrastructure I need to trace my queries to the execution location from AMAZON performance insights in case of a huge database load so I can refactor them. Each query is dynamically generated ...
0
votes
1
answer
144
views
Postgresql not able to start
I am facing below error during postgresql db start.
FATAL: the database system is starting up
FATAL: failed to re-find parent key in index "16619" for split pages 915114/915115
What is the ...
0
votes
1
answer
2k
views
check if each value of array are equal
I try to get d.id only if all rows with same id have same pday:
with dif_zero as (d.id,
date_part('day', p.docdate)::text pday
from sh_billing.dogovor d
join sh_billing.pays p on p.dogovor_id = d.id ...
2
votes
0
answers
133
views
Check constraint on a pair of columns
I have a table with this structure:
create table licence_report
(
id bigint default nextval('licence_report_id_seq'::regclass) not null,
created ...
0
votes
1
answer
238
views
How to call a function in regex_replace with matches?
I'd like to use some processing function instead of replacement text in regex_replace function. Is there possible to use a function with matches as arguments, like:
SELECT REGEXP_REPLACE( description, ...
0
votes
2
answers
437
views
Finding tables & columns at risk of hitting max value of int
I just took over a medium-sized postgres database and the team has been surprised with many tables hitting their auto-incrementing id int max limits which triggered unscheduled downtime to fix.
Does ...
0
votes
1
answer
793
views
Understanding Index w/ Cast
Given:
postgres=# \d b
Table "public.b"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
a | uuid | | not ...
0
votes
1
answer
2k
views
How to get row(s) with max or null value in Postgres
I would like get all rows with h_id max value or NULL if present there. For example result for table below will be:
fid |h_id |
--------|--------|
13374976| |
13377411|66666666|
I tried ...