Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,074
questions
0
votes
0
answers
3
views
PostgreSQL — considerations when adding new values to rows with a long-running migration
I have a table of ten columns containing 370 million rows and I need to add six new columns to it. Some new columns will take null values, others will be defined with NOT NULL DEFAULT. After altering ...
- 101
0
votes
0
answers
8
views
How to skip over a constraint record and continue?
I have a script that copies records from one psql DB (origin db) to another (destination db).
At the moment, the script gives me an error because in the destination DB, one of the columns is set to ...
- 1
0
votes
0
answers
16
views
-- lateral injecting a strange outer join condition
dbfiddle
code source: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/join.sql;h=7f866c603b8d65524584f16ae96205e1dad32e0b;hb=9eacee2e62d89cab7b004f97c206c4fba4f1d745#...
- 163
0
votes
0
answers
8
views
PostgreSQL text search with negation context
We have a managed PostgreSQL database on AWS (RDS) and we use it for text search using to_tsvector and phraseto_tsquery with the english configuration.
All functions properly except from when looking ...
- 103
0
votes
1
answer
32
views
Add row from table B based on minimum distance between table A and B
I am a beginner with PostgreSQL and have two tables A and B, both with multiple columns, including a point column (geom):
TableA includes a given 'ID', geom, etc , TableB is a "DB" of ...
- 3
0
votes
0
answers
10
views
QsqlQuery:prepare doesn't seem to recognize pgcrypto's encryption function
I found this error in the log file:
Fatal Error: column "9*Auz108" does not exist
LINE 1: ... VALUES ($1, $2, $3, $4, pgp_sym_encrypt($5, "9*Auz108"...
QPSQL: Unable to prepare ...
0
votes
0
answers
20
views
PostgreSQL as an external data source
I'm trying to get an external data source to work with PostgreSQL.
This is what I've done so far:
Downloaded and installed the latest 64bit PostgreSQL ODBC drivers
Created a System DSN using the ANSI ...
- 189
-1
votes
0
answers
23
views
How can I add an insert procedure into this stored procedure
I have a stored procedure to provision tables in a database...I also want to insert system-generated data into the date_dim table...I have some values in single quotes 'yy-mm-dd' in the insert ...
0
votes
0
answers
9
views
pg_dump permission denied for relation error for owner user
I am attempting to dump out a PostgreSQL database with pg_dump:
/usr/bin/pg_dump -f /home/crmpicco/rfc_754622.sql -F c -x -E UTF8 -b --disable-triggers -U root -h tillman.ff1lvb.us-west-2.rds....
- 145
-1
votes
1
answer
11
views
pg_restore relation error
I am trying to run pg_restore on a file so I am able to use it in postgres. The file is a psql file. I try the command: pg_restore --no-privileges --no-owner -d test backupfile.psql -c -U postgres ...
- 1
-4
votes
1
answer
18
views
How do I create a procedure in PostgreSQL
I tried following the example posted here: Returning values for Stored Procedures in PostgreSQL
Here is my proc
Create OR REPLACE PROCEDURE analysismodule.MyProc()
LANGUAGE 'plpgsql' as
$proc$
begin
...
- 115
0
votes
1
answer
38
views
Simple join on large table is very expensive
I have two tables, a (id, timestamp) and b (id, value).
They have equivalent number of rows (in fact b is materialized view of a and several other tables). There are around 1.3m rows on each table. I ...
- 101
0
votes
0
answers
16
views
PostgreSQL replication checking pg_stat_subscription more than 1 row
I'm new to DB replication.
I performed a replication task on 3 production DB.
On the first 2 DBs (low size) on the subscriber side, they only have 1 row result when i query pg_stat_subscription. On ...
0
votes
0
answers
19
views
Subtracting preceding cumulative values from current value using SQL
I have the following table of distances:
loc_id
distance
2
1844
3
4781
4
12678
and would like to subtract the cumulative sum of the preceding distance rows.
loc_id
distance
between_dist
2
1844
...
- 111
0
votes
0
answers
21
views
postgres 11: remaining connection slots are reserved for non-replication superuser connections
I've run into the following problem. From time to time, my app receives remaining connection slots are reserved for non-replication superuser connections from postgres. It lasts for quite noticeable ...
- 565
0
votes
1
answer
16
views
Postgres FDW: Is it possible to execute a foreign function in a foreign server after importing the foreign schema?
I imported a foreign schema using IMPORT FOREIGN SCHEMA.
Now I'm able to access all the table content in that schema, but still unable to access any of the functions.
Is it possible to execute a ...
1
vote
0
answers
17
views
Why this aggregate function cannot be converted with pg_upgrade from PostgreSQL 9.5 to PG 14.3?
I am trying to port old PostgreSQL v9.5 database (or family of databases) to PostgreSQL v14.3, with pg_upgrade utility.
After many hassles with old v9.5 binaries, source files (had to be recompiled to ...
- 23
0
votes
1
answer
18
views
logical replication initial phase
I am about doing replication for 16 millions record. Just want to ask about the initial phase of logical replication :
Do I need to copy the initial table from publisher to subscriber first ? Or is ...
- 7
0
votes
1
answer
20
views
PostgreSQL size of used shared buffer vs actual result size
I am debugging a production issue where a regular index scan used in a join reports very high buffers usage (sometimes gigabytes) when inspecting using explain (analyse, buffers). Because it's reading ...
- 3
-1
votes
0
answers
15
views
how to run ANALYZE VERBOSE with -j or in parallel
I have become the defacto DBA for a major version update 11 -> 14. Hosted on AWS.
I saw one comment saying ANALYZE VERBOSE can be run with -j, but that seems odd as the command would have to be a ...
- 99
0
votes
0
answers
12
views
How to consider special characters in ts_query and ts_vector as plain english
How to include special characters &, *, / in to_tsquery and to_tsvector . It converts boyce & doyl as {boyce:1 , doyl:1}. How to treat them as char so they can be searched in a query ?
- 1
0
votes
1
answer
26
views
How are HashAggregate row estimates calculated?
I have the following query:
SELECT foo.*
FROM foo
WHERE foo."id" IN ((SELECT bar."id"
FROM bar
WHERE bar.user_id IN ( 100, 102 ))
...
- 207
0
votes
0
answers
21
views
Can't cancel own query in AWS Aurora PostgreSQL 10.18 using pg_cancel_backend
I'm trying to cancel queries in AWS Aurora PostgreSQL 10.18 when logged in as not the master user, using pg_cancel_backend.
However, when I run SELECT pg_cancel_backend(1234) say, then I get the error:...
- 109
0
votes
1
answer
22
views
UNION types text and jsonb cannot be matched PostgreSQL
I have 3 tables on which I am trying to union all, one of the tables contains many jsonb columns.
I am doing this query,
CREATE
OR replace view datasets_dashboard AS WITH core AS
(
SELECT
...
- 281
2
votes
1
answer
33
views
Aggregate query for pairs of arrays of events
db<>fiddle for all of the data and queries below
I have a table events with the following structure:
create table events (
correlation_id char(26) not null,
user_id bigint,
...
- 123
1
vote
1
answer
20
views
How could this UPSERT query be made shorter?
I have a table storing the result of some simulations.
Besides the few columns that identity the row, every single column in the row is getting replaced at each update.
The query feels quite long and ...
- 217
-2
votes
1
answer
21
views
Converting timestamps in total minutes
Experts,
I have this column I used the following to calculate the time difference between two time stamps:
x.created@::timestamp at time zone 'America/Los_Angeles' - usr.reviewed@t::timestamp at time ...
- 21
0
votes
0
answers
22
views
Improving performance with a common low-cardinality field
I have a database with multiple tables. Every table has a field named sourcename, which indicates basically which customer the data came from. There are only about ten possible values for this field ...
- 101
1
vote
0
answers
26
views
Clever upserting and deleting
Let's say I have a table that has roughly the following schema (for brevity, I only kept the relevant fields):
CREATE TABLE IF NOT EXISTS some_table
(
id SERIAL NOT ...
- 111
0
votes
0
answers
23
views
How do I filter a SELECT query with VARIADIC conditions as arguments to function in PSQL?
For an API I am building I am supposed to write a function with a single HSTORE argument containing colname => value pairs so that queries may be filtered with a WHERE clause including an ...
- 101
0
votes
2
answers
49
views
How to automatically rollback a transaction?
How to automatically roll back every transaction after, for example, 10 seconds?
Does this parameter do the job?
idle_in_transaction_session_timeout
It looks good, but it kills only idle sessions. Is ...
- 199
2
votes
1
answer
79
views
Is a serialization anomaly only possible with SUM/COUNT?
I have read Deeply understand Isolation levels and Read phenomena in MySQL & PostgreSQL and especially the part "Serialization anomaly in Postgres". I think I've understood the problem ...
- 125
0
votes
1
answer
68
views
About Transaction knowledge
Am teaching myself SQL. And I never worked in any enterprise company but I have watched and learned some tutorials about web development and I see crud applications used to inject data into a database ...
0
votes
0
answers
29
views
Extract all elements from json array in postgresql
I have a table like:
id Name f_data
1 Raj {"review":"perfect", "tech":{"scalability":"complete", "backbonetech": ["...
0
votes
0
answers
27
views
VACUUM FULL alternatives on Windows PGSQL (pg_repack, pg_squeeze, ... )
I have a big table which needs a Vacuum full, but I don't have enough disk space.
I was looking online and came across some: pg_repack, pg_squeeze, ...
Am I mistaken or are all these extensions Linux ...
- 11
0
votes
0
answers
7
views
how to use the type FLOAT8 [] with NPGSQL / Postgres [migrated]
I have this in my schema:
f_gain_factor_notional FLOAT8 NOT NULL,
f_profits FLOAT8 [] NOT NULL,
f_fees FLOAT8 [] NOT NULL,
How can I ...
- 217
0
votes
1
answer
40
views
DELETE plus INSERT, or UPSERT row?
I'm trying to figure out the fastest way to:
INSERT a row if it doesn't exist
REPLACE (all columns at once) the row if it exists
I thought about DELETE + INSERT, but that looks like 2 scans
and if I ...
- 217
1
vote
0
answers
34
views
Full vacuum on big table (2.7 TB)
I have big database which contains a table of 2.7TB of data. I deleted some records, and I want to reclaim the disk space.
However, i only have about 200 gb free disk space left. How can I do this?
...
- 11
1
vote
1
answer
32
views
A query, safe against concurrent writes with READ COMMITTED, also safe with SERIALIZABLE isolation level?
I have asked this question with a wonderfully informative answer:
How to have uniqueness constraints for structures across multiple tables?
The answer from Erwin Brandstetter suggests this:
WITH ...
- 155
1
vote
1
answer
18
views
Transpose 1 row into columns with count/sum
I have a a single row with age buckets as column names. I want them all in one column with their count or sum whatever I end up doing. I have quite a few buckets but for the sake of this example I've ...
- 21
0
votes
1
answer
25
views
How do you find specific column name in function/sequences
I am trying to see if a specific column exists in any functions/sequences. I have found a script which can search tables, is there anything out there that would allow me to search in functions/...
- 185
0
votes
1
answer
9
views
what should I do q jsonb query in postgreSQL 13
I have stored jsonb data in PostgreSQL 13 like this:
[{"code": "OFFICIAL"}, {"code": "FULLTEXT"}]
how to query the jsonb data type? I have tried like this:
...
- 527
0
votes
2
answers
19
views
PostgreSQL JOIN SELECT null if record on one of both table not exist
I am having 2 tables with data as below:
tableA
========
1
2
3
4
5
tableB
=======
1
3
5
6
8
9
With this query:
select
coalesce(tA.id, null) as id_A,
coalesce(tB.id, null) as id_B
from
...
0
votes
1
answer
23
views
Accessing specific portions/values in a Column
Experts,
I have this table in PG - I need to convert my output to the green part.
So in short extract certain parts and make them a different column. I can easily do this in Python but kind of stuck ...
- 21
0
votes
1
answer
19
views
Monitring Aurora
We are currently migrating from SQL server to RDS Aurora. I am using Redgate to monitor the SQL Server database. My manager recommending New Relic for RDS Aurora, however, I don't feel Newrelic deep ...
- 1,373
0
votes
1
answer
53
views
PostgreSQL Foreign Data Wrappers - Simultaneous queries won't finish
We're using foreign data wrappers in a database which points to another server (which is a read-only replica). We run scheduled jobs using python ( more on this here: https://github.com/sqlalchemy/...
- 3
0
votes
0
answers
19
views
The best way to initialize table before logical replication
I am in the middle of setting up logical replication between 2 physical servers.
It involves 2 tables from the master with millions of rows. One table got 15 millions, and another one is 7 millions. ...
- 7
0
votes
0
answers
23
views
How can I import foreign functions (using FDW) in Postgres?
I tried using IMPORT FOREIGN SCHEMA and it just imports the tables (let me know if it imports anything more). It doesn't imports the functions created in that schema (I think they're stored in pg_proc ...
0
votes
0
answers
9
views
Is the Postgresql App on MacOS of Any Benefit Over a Brew Install?
I've been running postgresql in Docker on my MacOS image. That makes it difficult to get to get to it from psql and impossible from DataGrip. I decided to do a native install and found that there's a ...
- 101
1
vote
2
answers
45
views
Getting the total size of rows with same ids across multiple tables restricted to a particular user id
I'm trying to calculate all the projects size of one user across multiple tables.
a unique project is separated into 4 tables with multiple rows assigned to it.
for the moment I'm only able to ...
- 133