Skip to main content

Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
1 vote
1 answer
35 views

Import Postgres 8.3 database in Postgres 10.5

I have a database in Postgres 8.3 (and PostGIS 4.2), which I need to import in Postgres 10.5. I am a DB newbie and would appreciate any pointers to get started.
mitchus's user avatar
  • 113
5 votes
0 answers
348 views

PostgreSQL autovacuum ignoring "inactive" tables resulting in "transaction id wraparound"

I have a PostgreSQL database with a lot of tables (currently 93k) and I'm seeing issues with transaction id age threatening wraparound, because autovacuum is not getting triggered for clean tables ...
Gordon Seidoh Worley's user avatar
2 votes
1 answer
4k views

Converting float4 to numeric in PostgreSQL is rounding the values

I have a table that has a column storing some coefficients which require high precision. The type of the column is set to float4 and I need to change it to numeric. But after altering the table, it ...
Ramona Cristea's user avatar
1 vote
1 answer
145 views

SELECT operation with four joins results in bad performance

I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places. Resources Query:...
mist's user avatar
  • 29
0 votes
1 answer
578 views

Problem migrating PostGIS

I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10. I'm using CentOS 7.X Using pg_upgrade it should be quite straightforward, but the problem is PostGIS. Postgres 9.3 has PostGIS 2.1 ...
david.perez's user avatar
2 votes
1 answer
1k views

ORDER BY ASC is 300x faster than ORDER BY DESC

The ascending sort is 300x faster than descending sort on a column which has a B-Tree index. I have table of around 20 million rows. I have indexes on inserted_at and x columns. Ascending Sort ...
HBalyan's user avatar
  • 121
0 votes
1 answer
1k views

How to prevent informational messages from getting written when query results are output to a file

When I am running the below query as a batch file (.BAT) using the Windows Task Scheduler, I am getting some informative messages in the output file DatabaseNames.txt in addition to the database names....
Shown Sunny's user avatar
1 vote
0 answers
31 views

SET TIME ZONE '-7' vs. AT TIME ZONE '-7'

This is similar to these questions: Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601? Strange UTC offset time zone parsing in Postgres I'm running Postgres 10.3. As per the ...
ben-pdc's user avatar
  • 11
1 vote
0 answers
638 views

postgresql 10 - standby flips to read/write

I am trying to setup a primary/standby for failover; however, the standby flips to read/write and no longer functions as a replication database after the copy from the primary. Additionally, the ...
thePetester's user avatar
2 votes
2 answers
6k views

Is there a way to get the IP address of a client virtual machine from pg_stat_activity?

I have several C# applications where I have to ensure that there is only one instance of the application running against a given schema. I am migrating from Oracle to PostgreSQL 10. In Oracle, I ...
Welton v3.62's user avatar
2 votes
0 answers
21 views

Show each role permission for each database

Show each role permission for each database. I found a query in an answer, but it shows only for connect(in the same row with commas) I want to show for every privilege. If I add in the were ...
user3541631's user avatar
1 vote
0 answers
644 views

PGpool not properly detecting which postgres server is master

I am in the process of building a resilient database cluster and currently have 3 database servers running postgres 10 and repmgr. repmgr is taking care of the streaming replication including ...
Martin W's user avatar
  • 111
1 vote
1 answer
843 views

'Privileges dependent' error when try to drop a role/user even if there are no database attached and no role attributes

I had a user/role that was a database owner; so I dropped first the database; I try dropping the user and I get the following error: role 'user_name' cannot be dropped because some objects depend ...
user3541631's user avatar
0 votes
0 answers
504 views

postgres ERROR: relation "routine_privileges" does not exist

I am trying to put together a query to find unused indexes in a postgres database: SELECT indexrelname, pg_relation_size(quote_ident(indexrelname)), idx_scan FROM pg_stat_all_indexes WHERE idx_scan=...
P.Péter's user avatar
  • 901
4 votes
1 answer
2k views

Why does a SELECT (JOIN on regex) gets 30x slower with +1 row (query plan stays the same)? Lack of memory?

Here is my query, it takes 2.7 sec: SELECT t2.id, t1.id FROM sample t1 INNER JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex; The associated EXPLAIN (ANALYSE,BUFFERS) output: ...
klarezz's user avatar
  • 43
4 votes
2 answers
990 views

Postgres 10 not returning disk space after dropping indexes

I had several indexes that totaled ~1TB, according to pg_relation_size(). I dropped them. I expected to get a TB of disk space back at the OS level, but this did not happen. Maybe 15GB total (I didn'...
nrb's user avatar
  • 53
1 vote
0 answers
2k views

How to connect to Postgres on localhost with URL: "jdbc:postgresql://database:5432/db123"

I am able to connect to a postgres 10 instance on my local Win10 machine with pgAdmin 4 and "localhost" as a hostname, but when I change the hostname to "database" I cannot connect. How to fix it? I'...
ieXcept's user avatar
  • 111
0 votes
1 answer
834 views

postgresql 10.5 slow query issue only on MacOS

Consider these 3 tables Members CREATE TABLE public.members ( id integer NOT NULL DEFAULT nextval('members_id_seq'::regclass), client_id integer, login character varying(255) COLLATE ...
ceadreak's user avatar
2 votes
1 answer
1k views

Slow PostgreSQL query analysis, low memory usage, high writes

I'm having issues with the following query in PostgreSQL 10.5. Using pg_activity while the query runs show that only ~400 MB of RAM is used. I also noticed that the planner estimates seem to be ...
rodney757's user avatar
2 votes
1 answer
4k views

I can not get Postgres to listen on anything but localhost

I have been on this for MANY hours and can not understand why Postgres will not listen on anything other than localhost. I an running on Ubuntu 18.04 clean install + Django, Postgres and nginx. All up ...
Arana's user avatar
  • 41
1 vote
0 answers
173 views

PostgreSQL- user-defined types: note sure what internallength/alignment is for linked list type

I'm currently trying to implement at user-defined data type in Postgres in the form of a linked list with each node holding an int and a pointer to the next node, here are my input functions: // ...
user10284022's user avatar
3 votes
0 answers
224 views

Automating performance tests in PostgreSQL

I want to check the performance of specific queries in a Postgres database on a regular basis. Also, I want to be able to check the change in performance of different queries with any change in my ...
rajat's user avatar
  • 31
4 votes
1 answer
2k views

Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

Setting time zone as numeric offset (without abbrev, thus IMO not in POSIX format) is still interpreted by PostgreSQL as POSIX format: SET TIME ZONE '+02:00'; At least I assume, because checking the ...
dwelle's user avatar
  • 277
3 votes
1 answer
168 views

Why does postgresql differentiate between named and numeric time zone notations? [duplicate]

Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ? Assume Europe/Prague timezone, which is currently +02:00 (...
dwelle's user avatar
  • 277
3 votes
3 answers
2k views

Large update and delete based on another table

I have a very active PostgreSQL 10.4 DB, dealt with through SQLAlchemy, so SQL is not my forte. Among its tables, there are master with a primary key id and some nonindexed string column value, and ...
Vedran Šego's user avatar
3 votes
1 answer
822 views

PostgreSQL Geo Spatial Query is slow

After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only. I need to fetch records from a Table ...
nenad007's user avatar
  • 153
4 votes
1 answer
57k views

Getting Failed (exit code: 1). when running restore DB from a .tar file in pgAdmin4 postgresql 10.5 [closed]

I'm taking an intro to SQL course and I can't get my very first database loaded. I have a file dvdrental.tar, I create a new database dvdrental > right click > Restore > Select File > Pick "Data" ...
Cdhippen's user avatar
  • 161
2 votes
1 answer
9k views

Which is faster to update thousands of table rows?

With stored procedures in PostgreSQL v10.1.2, which is the fastest way or which is better: to check if rows exists and then update or try to update directly with the possibility that no rows matching ...
Geist's user avatar
  • 35
5 votes
1 answer
9k views

How to sum (aggregate) all the values in a key/value JSONB?

in Postgres 10 I have the following table: CREATE TABLE testtable ( id int PRIMARY KEY, qty jsonb ); INSERT INTO testtable (id,qty) VALUES ( 1, '{"2018-08-01": 10, "2018-08-11": 20, "...
TheStranger's user avatar
2 votes
1 answer
176 views

Aggregate additional values to min and max

This is a follow-up question to: Make custom aggregate function easier to use (accept more input types without creating variants) This SQL statement is given: SELECT c.name AS commodity_name, ...
Shinigami's user avatar
  • 123
1 vote
1 answer
272 views

String equality test fails for base64 encoded sha512 digest

I have the following table Column | Type | Collation | Nullable | -------------+----------------------------+-----------+----------+------------------- name | text ...
sitifensys's user avatar
1 vote
1 answer
706 views

Permissions for runtime user on all objects in schema (including future objects)

In PostgreSQL 10 - when using an "owner user" (login role owning a schema and all tables, not used by the application at runtime) and a "runtime" user I can GRANT Select/Update/Delete permissions on ...
eckes's user avatar
  • 1,456
0 votes
0 answers
505 views

Can I replicate a table in PostgreSQL to an unlogged table in another database?

The documentation is not clear about this specific logical replication scenario. It does mention unlogged tables on the publisher's side: Only persistent base tables can be part of a publication. ...
ivarec's user avatar
  • 229
2 votes
2 answers
20k views

Declaring variable for a whole script

I have a PostgreSQL 10 script for which I would like to declare variables once at the beginning and then use them throughout the script (with possible transactions). I know I can do: DO $$ DECLARE ...
Darth Kangooroo's user avatar
3 votes
2 answers
9k views

Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent?

I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs: pg_restore -d analytics -U postgres -j 4 -v "D:\Program Files\PostgreSQL\10\backups\...
mikelus's user avatar
  • 323
0 votes
1 answer
86 views

postgresql bgworker SIGTERM handler called twice

Im using postgresql 10.1 on Mac. I have a trouble in terminating a background worker using SIGTERM. In my project, im creating a new background worker using RegisterBackgroundWorker(). i have ...
krithikaGopalakrishnan's user avatar
3 votes
1 answer
332 views

Performance difference in accessing differrent columns in a Postgres Table

Created a table with 200 bigint column, 200 varchar column. (Postgres 10.4) create table i200c200 ( pk bigint primary key, int1 bigint, int2 bigint,....., int200 bigint, char1 varchar(255),......, ...
Dinesh Kumar's user avatar
1 vote
1 answer
284 views

Transferring (backup/restore) from development to an online server

I have a PostgreSQL database that is on my computer owned by postgres user and another database on a server, owned by a different user let's say user_server I'm using pgAdmin. I create a backup on ...
user3541631's user avatar
8 votes
1 answer
13k views

Replicate only a few tables in PostgreSQL 10

I have a need to keep updated just a few tables between databases with different purposes and schemas, except for these few tables. My current solution is a cron job that selects from one database ...
ivarec's user avatar
  • 229
2 votes
0 answers
2k views

why order by desc and limit query is taking too much time?

Currently, i was trying to make a sql query on my postgresql table, all things was going good unless i sort my query by Price in DESC, after this filter query is taking almost round about 60 sec to ...
Amad's user avatar
  • 143
2 votes
1 answer
1k views

How to make pg_basebackup use temporary replication slots

This article https://blog.hagander.net/another-couple-of-steps-on-my-backup-crusade-235/ states that temporary replication slots can be "used to prevent the wal on the server from being removed while ...
mikelus's user avatar
  • 323
2 votes
1 answer
1k views

Any workarounds for lack of primary key on partitioned tables?

I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not ...
J. Gallagher's user avatar
3 votes
2 answers
3k views

Moving Postgres 10 data dir on Ubuntu 18

I am trying to move the initial Postgres data dir from /var/lib/postgresql/10/main to another harddrive. The harddrive is accessable within the terminal via /media/chris/Datenspeicher I used this ...
Trevirius's user avatar
2 votes
0 answers
671 views

Postgres pg_dump restore performance. Is using pg_dump to back up large databases an acceptable solution

Folks, I am considering moving from SQL Server to Postgres. The per core licensing of SQL Server is too expensive for my needs. My one concern is restoring Postgres from a dump. I believe that the ...
mikelus's user avatar
  • 323
1 vote
1 answer
319 views

Partitioning in PostgreSQL 10

Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning. While I ...
Anita's user avatar
  • 21
13 votes
1 answer
3k views

Why is PostgreSQL choosing the more expensive join order?

PostgreSQL using defaults, plus default_statistics_target=1000 random_page_cost=1.5 Version PostgreSQL 10.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit I've vacuumed and ...
Paul Draper's user avatar
0 votes
0 answers
86 views

PostgreSQL UPDATE query issue

I have devices table like this: traccar=# \d devices; Table "public.devices" Column | Type | Collation | Nullable | ...
Anton Tananaev's user avatar
0 votes
0 answers
412 views

Is upsert on view possible?

I have this table CREATE TABLE IF NOT EXISTS projects_t ( name TEXT NOT NULL, metadata JSONB, content BYTEA, version TIMESTAMP WITH TIME ZONE DEFAULT now(), PRIMARY KEY (name, ...
Rémi Desgrange's user avatar
5 votes
1 answer
5k views

Is there a way to speed up a DISTINCT query?

I have a table t in a database (PostgreSQL 10.4): \d t; Table "public.t" Column | Type | Collation | Nullable | Default ----------+------------------------+------...
lugger1's user avatar
  • 153
2 votes
0 answers
2k views

Update sequence based on another column

Let's say that I have this table : CREATE TABLE test ( id uuid unique default uuid_generate_v4(), version int, another_column text, PRIMARY KEY (id, version) ); What I want to achieve is, ...
Rémi Desgrange's user avatar

1
4
5
6 7 8