Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
0
votes
0answers
34 views

I have an issue with postgresql, something is preventing the postgres to startup. I cant figure out the solution for this issue

I have an issue with postgresql, something is preventing the postgres to startup in the cloud server. I cant figure out the solution for this issue. here i attached the log file. 2021-12-18 10:19:08....
0
votes
0answers
8 views

How to drop a table with invalid block in pg_attribute?

QGIS is unable to access one my DB. Tracking the issue, I found that the broken relation was pg_attribute. From there, I managed to identify the table causing the issue. select * from pg_attribute ...
1
vote
0answers
40 views

How to write the query in postgres in which i have to join two tables with specific condition?

I have to build a query the example fiddle is given here. In this ctx.params.user_id Is a variable The conditions which i have to satisfy is as follows. If the value in effective_employee_type = ALL ...
0
votes
0answers
27 views

pg_ctl error when using pg_upgrade

We are attempting to upgrade Postgres 10.10 to 12.4 using pg_upgrade on a Windows 2016 Server machine using the command "C:/Program Files/PostgreSQL/12/bin/pg_upgrade.exe” --old-datadir "C:/...
1
vote
0answers
18 views

FK constraint ON DELETE RESTRICT not working on some database instances

My database instances appear to treat FK constraint ON DELETE RESTRICT differently and I don't know why. On production database instance (and a clone, where I do my testing), I believe the RESTRICT is ...
0
votes
1answer
14 views

Create table with foreign keys blocked by other Table-level Lock

I'm not pretty sure it is database question or development-process question, but I will kindly give it a try. When creating a new table B with foreign keys to table A, the operation tries to acquires ...
2
votes
2answers
242 views

pg_dump hangs in Git Bash but works in Windows command line

I tried to make a backup of a database from Git Bash environment, but the executable does not output anything. If I specify the filename using the -f flag, a blank file gets created, so the executable ...
0
votes
0answers
11 views

Postgres connection logged temp file, but no query?

I'm going over some logs for a Postgres database and came across the following logs and I'm at a loss as to what would've caused it. connection authorized: user=app1 database=pg SSL enabled (protocol=...
0
votes
1answer
81 views

Optimizing a recursive CTE or replacing it with a temporary table

I have a recursive query like this: with recursive PCte(id) as ( select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in ('123', '124','125', 'n') union all ...
0
votes
1answer
32 views

PostgreSQL slow performance using "or" operator

Development team on my company built the following query: select distinct c.customer_id from table1 c join table2 l on c.customer_id = l.customer_id join table3 cal on c....
0
votes
1answer
24 views

Unable to locate and login to by PostgreSQL

I am using Ubuntu 20.04 LTS, and I have configured PostgreSQL 9.5 to work on the server. I am trying to log in but I have forgotten the password for the user postgres and therefore I tried to reset it ...
-1
votes
1answer
31 views

db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function

When running the following query I receive an error: SELECT $__timeGroupAlias(receivedat,$__interval), SUBSTRING(message, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}') AS the_address, COUNT (message) AS ip ...
0
votes
1answer
41 views

Is renaming schemas more efficient / faster than drop/create in a single transaction

Given the constraint (from customer side) to having only one database - how can I most effectively deploy new versions of data into a production-facing application. The new data is packaged into a (-...
1
vote
1answer
53 views

Postgres schema based replication

I have setup streaming replication in postgresql V10 in the production environment. Now for a typical scenario, I would like to know if I can configure replication in such a way that only specified ...
0
votes
0answers
61 views

postgreSQL slow query with high shared read

i have a problem with my postgreSQL database, actually i'm developer not DBA so i don't have enough experience to deal with problems like this. I ran this query at my psql: explain (analyze true, ...
0
votes
0answers
259 views

Upgrade Postgres database from 10 to 13 running in Docker

I have a Postgres database running in a docker container (https://hub.docker.com/_/postgres). The docker image is postgres:10.0 and I would like to upgrade to postgres:13.3. If I just change the image ...
0
votes
2answers
61 views

Seemingly simple query with unexpected execution time

I have the following schema : And want to get the last price (based on transaction_datetime) per test_acts.code using the following query : SELECT code, price FROM test_transaction_acts, ...
3
votes
2answers
646 views

AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?

I'm using AWS Aurora PostgreSQL Serverless with autoscaling. It appears as though scaling clears the shared buffer, so right when we want to crank out the performance, we are forced to face-plant with ...
0
votes
0answers
85 views

Postgres fails to propagate constraint into subquery

I have a complex query "csq", which I'm using as a view. For this question I'm writing it as a subquery, also to show the more performant version. Please remember that I cannot use the ...
0
votes
1answer
37 views

What is difference between START WAL LOCATION, STOP WAL LOCATION, and CHECKPOINT indicated in the backup history file?

I am a bit confused about these locations. Could someone give a short definition for these locations.
0
votes
1answer
34 views

Actual used and free space in WAL segments

I am new to the PostgreSQL world. I have an experience in administrating Microsoft SQL Server and Oracle databases. In SQL Server, we are able to find how much space is actually used in pre-allocated ...
0
votes
0answers
28 views

An update operation that would target 0 rows, aborted by timeout

I have an update operation that is being executed on a quite large table (postgresql@10): UPDATE contacts_trashbin SET op_id='\x60771801dbed2d6e021ec067'::bytea WHERE contacts_trashbin.company_id = '...
0
votes
0answers
49 views

PostgreSQL: Query runs fine in Prod but really slow in QA

Postgres 10.4 dbs hosted in AWS environment. I have a query which joins two tables with many-to-many relationship. I learned while troubleshooting that Analyzer won't run until number of rows inserted ...
1
vote
2answers
4k views

What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer) [duplicate]

I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it)...
1
vote
0answers
29 views

PostgreSQL cannot work when opens SSL on Windows 10

I am not sure this topic will fit here but I don't know ask it where. I am trying to open the SSL of PostgreSQL 10.16 on Windows10. I read a lot of documents about creating an SSL certificate to ...
0
votes
0answers
25 views

Check when a privilege was granted - PostgreSQL

Is there any information_schema view that can tell when an object privilege was granted? The pg_namespace view doesn't have that. select * from pg_namespace join lateral ( SELECT * from ...
0
votes
2answers
173 views

Postgres index scan on large table taking a long time

I have a large table (~500M records, 85GB in size) on a PostgreSQL 10.10 database (AWS RDS instance). The records are created by a bunch of game simulations which insert "events" whenever ...
0
votes
1answer
32 views

Why is an unindexed range operator (<@) faster than using BETWEEN with an index?

NB This is the same setup as this question, where here I'm asking specifically about something I was specifically not asking about over there. I've got a table with a column utc timestamptz, with a &...
1
vote
1answer
124 views

Diff in idx_tup_read and idx_tup_fetch in postgres pg_stat_all_indexes

I have gone through documentation and previous stackoverflow questions but not findings those helpful. Postgres version 10.6 Even for index only scans values of idx_tup_fetch is increasing. So I am ...
0
votes
0answers
301 views

postgres is choosing wrong index even with STATISTICS 1000

I have a table test_monika with 300k records. Simple query is not using index test_monika_rank_idx instead it is using primary key index test_monika_pkey. Actual rows returned are 1. The planner with ...
0
votes
1answer
30 views

PostgreSQL skips WITH query

Consider this CTE: WITH division_by_zero AS ( SELECT 1/0 ) SELECT 42 It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?
1
vote
0answers
2k views

postgresql: Invalid data directory | Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory

This error is showing after running this command: sudo systemctl status postgresql@10-main.service postgresql@10-main.service - PostgreSQL Cluster 10-main Loaded: loaded (/lib/systemd/system/...
1
vote
0answers
78 views

Postgresql Database leaking t99_8989898 files in db directory or db size is showing incorrect in postgres

postgresql version is 10 \l+ showing db size to be 7228 GB i checked all tables sizes those comes out to be 60 GB only using SELECT relname AS "relation", pg_size_pretty(...
-1
votes
2answers
20 views

casting custom string to date in SQL

I have a column with strings such as 2015-W02, meaning the second week of 2015. I would like to convert it to a date (e.g., corresponding to the Saturday of that week). It does not seem that CONVERT ...
0
votes
1answer
614 views

How to get the last non-null value that came before the current row

Say I have the following table: create table tab (ts integer, a integer); insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3); When i query the data there may be some nulls: select * ...
1
vote
1answer
19 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 ...
1
vote
1answer
927 views

Passing parameters for dynamic SQL in a function

I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In ...
0
votes
0answers
195 views

PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv

I am in the situation where I need to create a table which also has a time column. This table is filled with data taken from a CSV, but the csv has no time column. Only after importing the data inside ...
0
votes
1answer
38 views

Give alias / column definition list to ROWS FROM()

This obviously doesn't work: SELECT regexp_matches[1], regexp_matches[1] FROM ROWS FROM ( regexp_matches('fooBarBaz', '[[:upper:]]', 'g'), regexp_matches('fooBarBaz', '[[:lower:]]', 'g') ); Error:...
4
votes
3answers
2k views

Why is there no max(uuid)/min(uuid) function?

Why can I use a UUID to sort rows: SELECT uuid_nil() ORDER BY 1; But I cannot compute the maximum value: SELECT max(uuid_nil()); [42883] ERROR: function max(uuid) does not exist Hint: No function ...
0
votes
1answer
25 views

VACUUM FULL restored very small amount of disk space

I have a large table with ~130m entries. I recently removed unnecessary entries and it should be left with ~60m. I ran VACUUM(FULL) [table_name] and after it finally finished it restored only 2GB of ...
1
vote
1answer
34 views

Postgres partition avoiding redundant column

Say I have a table, say project_data with lots of rows where the data may be grouped by a project_id integer column, and partition it onto one table for each project_id value. Do I then need the ...
0
votes
0answers
718 views

Deleting child record doesn't delete foreign key reference

I am using PostgreSQL 10.7. I have this strange occurrence where I fail to see the problem. Whenever I delete all child-records referring to a parent, somehow the reference between these two tables ...
2
votes
1answer
63 views

Should I duplicate columns between tables to speed-up aggregations like SUM? [closed]

I have two tables in PostgreSQL 10.12 database: waste_card (id (PK), user_id, manufacture_date, transfer_date, address_id, card_type, .. (and other card_specific_firelds) wastes (user_id, ...
0
votes
1answer
21 views

want to use logical replication with a postgresql10 cluster (that is not using logical replication)

We presently have an active/standby postgresql 10 cluster (based on PAF/clusterlabs). Anyways, the servers are kept in sync using: wal_level = replica This is fine and working. However, we would like ...
0
votes
2answers
54 views

how to change priority in ILIKE

I have this simple table and user records. I just want to get users with ilike or something else to get better order in PostgreSQL 10.12. CREATE TABLE users( ID INT PRIMARY KEY NOT NULL, ...
2
votes
2answers
36 views

How to order some rows one way, and the rest another way?

I am trying to sort rows from a table in PostgreSQL 10.12: CREATE TABLE example ( id serial PRIMARY KEY, val text NOT NULL, x integer NOT NULL, y integer NOT NULL ); INSERT INTO example (...
3
votes
0answers
70 views

Logical Replication/Foreign Data Wrapper Connectivity between Two Aurora Serverless Databases (PostgreSQL 10.7)

I have two Aurora PostgreSQL Serverless databases running in the same region and have PostgreSQL version 10.7. I'm wondering if logical replication is available? The official docs say that it's ...
2
votes
2answers
112 views

Group by time interval and output the source and destination station_id and count

I am stuck with a query: CREATE TABLE public.bulk_sample ( serial_number character varying(255), validation_date timestamp, -- timestamp of entry and exit station_id integer, ...
0
votes
0answers
18 views

Postgresql : UNNESTING & NESTING

I have below jsonb in table. '{ "Owner":[ { "Edgeid":4, "Weight":40, "EdgeColor":"Black" }, { ...

1
2 3 4 5
7