Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
2 votes
1 answer
324 views

How do you reset a serial type back to 0 after deleting all rows in a table?

I previously asked this question , and somebody posted a very helpful answer on db fiddle here. This has worked really well for me. However, now I would like to ask a new (related) question : Suppose ...
user avatar
0 votes
1 answer
42 views

how to optimize multiple unions for the same table with different condition into a single query

Environment : PostgreSQL v10.6 (AWS RDS instance) For the given table: CREATE temp TABLE lve ("fin" timestamptz, "vehiculo" int, "equipo" int) ; INSERT INTO lve ...
user avatar
0 votes
0 answers
83 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....
user avatar
0 votes
0 answers
13 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 ...
user avatar
  • 31
1 vote
0 answers
86 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 ...
user avatar
0 votes
0 answers
61 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:/...
user avatar
1 vote
0 answers
36 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 ...
user avatar
0 votes
1 answer
80 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 ...
user avatar
2 votes
2 answers
515 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 ...
user avatar
  • 153
0 votes
0 answers
14 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=...
user avatar
  • 469
0 votes
1 answer
285 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 ...
user avatar
0 votes
1 answer
186 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....
user avatar
  • 3
0 votes
1 answer
29 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 ...
user avatar
-1 votes
1 answer
51 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 ...
user avatar
  • 1
0 votes
1 answer
77 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 (-...
user avatar
  • 207
1 vote
1 answer
90 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 ...
user avatar
  • 11
0 votes
0 answers
84 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, ...
user avatar
0 votes
0 answers
369 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 ...
user avatar
  • 111
0 votes
2 answers
65 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, ...
user avatar
3 votes
2 answers
962 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 ...
user avatar
  • 133
0 votes
0 answers
96 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 ...
user avatar
0 votes
1 answer
73 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.
user avatar
  • 1,163
0 votes
1 answer
48 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 ...
user avatar
  • 1,163
0 votes
0 answers
34 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 = '...
user avatar
0 votes
0 answers
59 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 ...
user avatar
7 votes
2 answers
6k 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)...
user avatar
  • 181
1 vote
0 answers
33 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 ...
user avatar
  • 11
0 votes
0 answers
49 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 ...
user avatar
  • 13
0 votes
2 answers
307 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 ...
user avatar
  • 3
0 votes
1 answer
37 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 &...
user avatar
  • 191
1 vote
1 answer
181 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 ...
user avatar
0 votes
0 answers
360 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 ...
user avatar
0 votes
1 answer
38 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?
user avatar
  • 237
1 vote
0 answers
3k 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/...
user avatar
1 vote
0 answers
93 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(...
user avatar
-1 votes
2 answers
26 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 ...
user avatar
0 votes
1 answer
1k 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 * ...
user avatar
  • 125
1 vote
1 answer
27 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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
  • 59
0 votes
0 answers
276 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 ...
user avatar
  • 111
0 votes
1 answer
62 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:...
user avatar
  • 237
4 votes
3 answers
3k 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 ...
user avatar
  • 237
0 votes
1 answer
37 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 ...
user avatar
  • 5
1 vote
1 answer
49 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 ...
user avatar
0 votes
0 answers
809 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 ...
user avatar
2 votes
1 answer
72 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, ...
user avatar
  • 23
0 votes
1 answer
37 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 ...
user avatar
  • 3
0 votes
2 answers
88 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, ...
user avatar
2 votes
2 answers
47 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 (...
user avatar
3 votes
0 answers
93 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 ...
user avatar
  • 131

1
2 3 4 5
8