Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
2
votes
0answers
56 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
2
votes
0answers
26k views

List of escape sequences for Postgresql string data types

I'm trying to find the documentation of a complete list of escape sequences for string data types in Postgresql. So for example, if you need to escape a quote character inside of a quoted string, you ...
1
vote
3answers
148 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
1
vote
3answers
12k views

How to update complex jsonb column?

I have a table with following definition: create table json_test ( filter_data jsonb); and i insert to it values like this: '{"task_packets": [ { "state": "PROCEEDING", "...
1
vote
2answers
18k views

How To Remove Spaces Between Characters In PostgreSQL?

I want to match two tables based on a specific column which is a card number, there are spaces between characters in some of card numbers in the second table like below: 'NES 123_4_5' 'MTS 678_9_0' ...
1
vote
2answers
470 views

Uninstalling / upgrading PostgreSQL on OSX

The problem I have PostgreSQL 9.3 installed on my MacBook Pro. I don't remember how I installed it (there are numerous ways), and now I want to upgrade to PostgreSQL 9.5. Foresnsics The running ...
1
vote
1answer
4k views

`ON CONFLICT DO UPDATE` causing deadlocks?

I have a project where I'm trying to use the PostgreSQL ON CONFLICT DO UPDATE clause, and I'm having huge numbers of deadlocking issues. My schema is as follows: webarchive=# \d web_pages ...
1
vote
1answer
79 views

how to combine multiple timestamps and keep the most recent one?

I have a table with many timestamps in it, some of which may be null. Is there a way to combine them all and keep the most recent timestamp so I don't have to reference each timestamp individually? ...
1
vote
2answers
3k views

How to copy a PostgreSQL database at a filesystem level between servers

I have a large PostgreSQL database on a server with a failing disk. I cannot run pg_dump because of disk errors. I have another server with the identical OS (Ubuntu 16.04) and the identical version ...
1
vote
1answer
100 views

Repeat total count value

I have a table called paths that looks like this: id parent_id -- --------- 1 null 2 1 3 2 4 3 5 4 6 5 7 5 8 5 9 ...
1
vote
1answer
2k views

Set values conditionally in Postgres UPSERT

We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1 We ...
1
vote
3answers
139 views

Select all before first break

I have a table with three columns: id, sort, name in PostgreSQL 9.5 database. I need to select all rows starting from sort=1 and before the first break (ordered by sort). For example I have: id | ...
1
vote
1answer
65 views

How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
1
vote
2answers
2k views

Optimizing query with type casting

I had a query that was taking way too much time despite having all the indexes I could think of. Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
1
vote
1answer
55 views

Does cancelling (stopping early) a CLUSTER command cause a full rollback in Postgres 9.5?

Let's say I want to CLUSTER a table. There's no way to know how long it will take so I just run it. After a couple hours, I need the data so I cancel the CLUSTER command. Does CLUSTERing work with ...
1
vote
2answers
2k views

extension “pg_buffercache” already exists and relation “pg_buffercache” does not exist

When I try to create the extension pg_buffercahe using the following command: ➤ psql://postgres@postgresql-95-2:5432/postgres CREATE EXTENSION pg_buffercache; ...it returns the error: ERROR: ...
1
vote
1answer
1k views

How to modify a Postgres tablespace location

I am using a workstation that has 3 different hard drives. Some time ago I got a large database to reverse engineer that would not fit in the system drive. Therefore I created a new table-space in one ...
1
vote
1answer
193 views

how to execute sql inside a column in postgres

I am trying to execute SQL statements that exist within a column of a database table. The records of this table has 'tests' in each row. sid name sql 1 test1 SELECT source_system_name, ...
1
vote
1answer
191 views

Count number of writes to different tables

I have recently setup Continuous Archiving for Point-in-Time Recovery in a postgresql DB, but the archive is growing at an unexpectedly high pace (tens of gigabytes per day). The DB is a couple of GB, ...
1
vote
2answers
24k views

Increasing Postgresql 9.5 max_connections to at least 300

Right now I am using the default postgresql.conf which has maximum connections set to 100. I need to increase max_connections to 300. Postgresql 9.5 is the only thing running on my Linode. I am ...
1
vote
1answer
89 views

Ordering in postgresql

I'm facing an issue whereas I do not understand. Running PostgreSQL on mac and have this query: select tldid, tldaction, to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, ...
1
vote
1answer
50 views

Query performance depends on parameter value

In the system I'm working with there are many type hierarchies, where each type corresponds to a table. For example f_dp_rkkbase -> f_dp_rkk -> f_dp_inputrkk. To distinguish between different ...
1
vote
1answer
596 views

max_stack_depth not changed even stack changed in limits.conf

The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log: DETAIL: "max_stack_depth" must not exceed ...
1
vote
1answer
83 views

Update “oldest” row

I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n). I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
1
vote
1answer
112 views

How to improve performance of query for distinct suppliers in big sales table?

I have two tables, one with store data: Table "public.store" Column | Type | Modifiers -----------+------------------------+----------- id | integer ...
1
vote
2answers
92 views

How do I return two aliases from the same table query?

How would I do something like this: SELECT * from notifications WHERE read=false AS unread_notifications and WHERE read=true AND deleted=false AS read_notifications; Result Example: ...
1
vote
2answers
240 views

Looking for ways to optimize sql query with inner join

My join query seems to be using the right index but is still slow. I am not sure where the issue lies and would appreciate any tips. The query and its explain is as follows: SELECT events.*, ...
1
vote
1answer
3k views

Failed import of date using COPY

I'm trying to import a CSV file into a table in Postgres, and it's bombing out on the first date field. The dates are in the format dd/mm/yyyy and there isn't a date in the field in question in the ...
1
vote
2answers
138 views

Working of postgres remote connection

How does psql remote connection work (default configuration)? Does it make use of ssh or any other protocol? I am using linux machine (centos 6.8) and postgresql9.5 version.
1
vote
1answer
1k views

Limit of psql_history

I can't find (using ctrl-r within psql and less ~/.psql_history) a particular query that I executed a couple months back. I also recently upgraded from 9.3 to 9.5 so I'm not sure if there is a limit ...
1
vote
2answers
859 views

PITR restore in postgresql

I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows. wal_level = archive archive_mode = on archive_command = ...
1
vote
2answers
11k views

Start, stop, configure PostgreSQL through Mac terminal

I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into ...
1
vote
1answer
747 views

Postgres sharding installation with citusdb

We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end. We are using a 16*2.4 GHz processors with ...
1
vote
1answer
42 views

What does it mean when Postgres query state is null

What does it mean when a query state is null? my_database_name> SELECT pid, datname, usename, application_name, query_start, state, state_change FROM pg_stat_activity; +-------+------------------+-...
1
vote
2answers
93 views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...
1
vote
1answer
33 views

Improve Performance by Left Join and Group By Count - Postgresql

I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN. How do I improve the left join? I am using the below index create index session_opportunity_index on public.session ...
1
vote
1answer
197 views

Postgres table with 35m rows slow queries. how can i improve the performance?

SELECT count(c.id) as clickCount FROM clicks c LEFT JOIN links l on c.link_id = l.id LEFT JOIN user_agents ua on c.user_agent_id = ua.id AND ua.robot IS NULL WHERE l....
1
vote
1answer
244 views

Truncation vs Deletion When `session_replication_role = replica`

When I first run SET session_replication_role = DEFAULT; CREATE TABLE users ( id serial PRIMARY KEY ); CREATE TABLE posts( id serial PRIMARY KEY, user_id INTEGER REFERENCES users(id) ); ...
1
vote
1answer
114 views

Should I stop my PostgreSQL server before reboot?

Every week I do an apt-get upgrade and dist-upgrade on the server where my PostgreSQL is running. After that, I do a sudo reboot now. Should I stop the PostgreSQL service before this reboot?
1
vote
1answer
31 views

Can sub-selects change in one single query in a read committed transaction?

I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are: BEGIN; WITH first AS (...
1
vote
1answer
166 views

How to synchronize a Windows database for Linux?

I have some database (PostgreSQL 9.5) on a Windows server, and I would like this data to be synchronized to a Linux server, that is connected in an application. Is it possible to synchronize?
1
vote
1answer
60 views

Postgresql 9.5 JSON problems

I have to update a complex query that takes JSON fields: SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType from SELECT context->>'Source' as Product, ...
1
vote
2answers
361 views

PostgreSQL: why does an extra WHERE clause make an index-only scan slow?

I have a query that is being made 18x slower by the presence of a single WHERE clause. The query plan is an index-only scan and the clause in question looks at one of the fields in the index. This ...
1
vote
1answer
2k views

Postgresql-Server won't start after moving data directory

I am running a local postgresql server (9.5 & 9.6) on my Ubuntu 17.04 machine. After moving my data directories to another hard drive following this guide the server can't start. I.e. with sudo ...
1
vote
2answers
97 views

PGSQL: Which is faster, many constraints or a single trigger?

I have a table that currently has 12 check constraints. I'm making some updates and I'm wondering if this is the best approach. I see three options: Keep using the 12 constraints. They're a bit ...
1
vote
1answer
136 views

Index for range of timestamp with varchar fields

I am using postgress database (Version 9.5.4), I have a table structure like this, create table test ( id serial primary key, company_name varchar(255) not null, department varchar(255) ...
1
vote
1answer
451 views

SELECT query slowing down the wal replay in replication server

While running a SELECT query (bit large query ~1GB of data output which took ~15minutes to execute) in hot standby replication host, its halts the wal archiving process. Once the Query done with the ...
1
vote
2answers
137 views

How to calculate column (integer) changes in hourly buckets? [closed]

I have following table: id serial pk created_at timestamp with time zone not null user_id integer not null points integer not null For a given time period (min_date and max_date, both timestamp with ...
1
vote
1answer
2k views

how can i lock or disable connections to postgres DB while performing maintenance? [duplicate]

I never have this issue with MySQL but PostgreSQL is ALWAYS a pain when trying to do any kind of maintenance. So I am trying to truncate and load new data into databases on PostgreSQL server but ...
1
vote
1answer
644 views

Efficient pagination for big amount of data [closed]

I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '...

1 2 3
4
5
7