Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 ...
user avatar
  • 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#...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
-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 ...
user avatar
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....
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ?
user avatar
  • 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 )) ...
user avatar
  • 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:...
user avatar
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 ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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": ["...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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? ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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/...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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/...
user avatar
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. ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 133

1
2 3 4 5
302