All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
1
vote
0answers
15 views
Why is Server Configuration in Pgadmin3 inaccessible for me?
My Postgres server is running (localhost:5432), but Tools > Server Configuration is greyed out for me as below. I was attempting to access it to edit/locate my postgres.conf file.
1
vote
1answer
14 views
Storing email inbox information
I am working on a webmail client. I want to store the remote mailboxes information in a Postgres database. As a general rule of thumb, most IMAP servers will have a similar default folder structure ...
0
votes
0answers
11 views
Is there any way to automatically keep data in a table in sync with multiple other tables without materialized views?
I would like to create some materialized views in Redshift to aggregate the information stored in several tables and keep the data in the views in sync with the source tables.
Unfortunately, Amazon ...
0
votes
0answers
13 views
Postgres LEFT JOIN with recurring dates and users
I'm trying to get a query to show me if users have a record on a particular day, and if they don't it would return a NULL.
What I have so far is a I create a sequence of days, then LEFT JOIN it, if I ...
0
votes
0answers
6 views
Postgres archive_command on Ubuntu 16.04
Ubuntu 16.04: Postgres 9.5:
When i start psql -c "select pg_stop_backup();" command to execute for hot standby replica. It gives me warning as
archive_command as:
NOTICE: pg_stop_backup cleanup ...
1
vote
0answers
16 views
The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)
I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which ...
1
vote
0answers
17 views
psql in windows
I want to connect to Amazon Redshift and run scripts like exports to s3 and loads through command line.
is psql the only way to go?
If that is the case, is there a package that would install just ...
0
votes
0answers
18 views
Simplify Month Over Month % Change, 4 Months Moving Average %
invoice
created_at | amount | status |
------------+--------+---------
2016-06-01 | 50 | 2 |
2016-05-30 | 150 | 2 |
2016-04-29 | 32 | 2 |
2016-03-28 | ...
1
vote
0answers
14 views
Optimal query to show active records by date
I'm trying to determine what the optimal query might be for associating a date with a record that would have been active for that day.
Here is a setup to demonstrate what I'm trying to do (demo at ...
0
votes
2answers
19 views
Query Amount of entries per column from multiple tables + provide table name
I have five tables with all having the same column (objart):
I queried for the number of entries and combined the result with UNION, which worked well.
Two questions:
1) Is there a shorter/ better ...
0
votes
1answer
12 views
Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL
I know CREATE INDEX CONCURRENTLY is slower, but how many longer it will takes to finish in comparison with the traditional CREATE INDEX? 40% more or much more than that?
I am creating an index in a ...
0
votes
0answers
14 views
After crash, lost Postgresql data
I was developing a Rails application with pg when there was a power loss.
After turning on my computer again I was having:
psql: could not connect to server: No such file or directory. Is the ...
1
vote
0answers
20 views
Increase in IO Wait after adding indexes with postgres
I've recently been put in charge of a system that has been fairly untended for a period of time.
The majority of activity is via queued worker jobs based off of data feeds from other systems/data ...
0
votes
0answers
11 views
PostgreSQL user creation and prvilege setup [on hold]
In MySQL I am using these commands to create user and assign the privileges:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost';
...
1
vote
0answers
16 views
PostgreSQL: pg_upgrade failing because hstore incompatible
I'm trying to migrate from PostgreSQL 9.4.5_2 -> 9.5.3
I'm trying to use pg_upgrade (from 9.5.3 distribution) and getting the following error:
pg_dump: [archiver (db)] query failed: ERROR: ...
2
votes
1answer
21 views
Postgres - Regroup rows with at least one common attribute
I have the following table :
Letter | Figure
-------|-------
A | 1
B | 1
B | 2
C | 2
C | 3
D | 3
E | 4
I would like to group rows that share ...
1
vote
1answer
15 views
What run-time configuration parameters are used for concurrent index creation
Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value.
mhildreth=# show maintenance_work_mem;
maintenance_work_mem
----------------------
16MB
(1 ...
2
votes
1answer
22 views
Monitoring streaming replication
We're setting up streaming replication with one master and one (read-only) slave. We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date.
...
0
votes
0answers
13 views
parallel postgres libpq copies with different db connections
At the moment I'm doing bulk database inserts with the libpq copy command (PostgreSQL 9.5):
copy abc (col1, col2, col3) from stdin delimiter ";" NULL 'Null'; (PQexec)
PQputline
PQendcopy
I know ...
0
votes
0answers
14 views
potgresql foreign key index unused for some columns on one specific server
I have a table publication, with two columns type_id and sub_type_id (there are, of course, more columns, but they are not important) on a server, all containing int8 foreign keys to tables type and ...
0
votes
0answers
12 views
Why ST_Distance is not using Index?
I have a table tbl_table with column geo of type GEOGRAPHY(POINT,4326).
I have created a index using below query.
CREATE INDEX index_name ON tbl_table USING GIST(geo);
I am doing following queries ...
0
votes
0answers
10 views
PostGis - How to handle exception using function?
I am using this code to update a table and when there is any exception it should handle it, So how to do it ??
UPDATE Schema.Table1
SET Column3 = ...
0
votes
1answer
26 views
Raise Multiple Exceptions Postgres
I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function.
I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
0
votes
1answer
17 views
Restoring postgres backup with WAL-E fills up disk
I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a ...
0
votes
0answers
21 views
Postgresql restore database from file encoding error
I have problem while i import database from backup I gives many output and many "invalid command" messages. In the end it gives an encoding error. what can the reason be?
my command:
sudo -u ...
1
vote
1answer
37 views
Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial)
I have two points and I have to calculate difference between them.
I using below query in SQL Server.
SELECT geography::Point(27.185425, 88.124582, 4326)
...
3
votes
0answers
38 views
Slow Postgresql query with left join
I have a query on Postgres, and I also have added proper index. Is there anything missing here?
SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
...
0
votes
2answers
21 views
Unique pair checking query improving in pivot table
First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill.
I have seen an example online.
So I have a ...
4
votes
1answer
56 views
Is it possible to create a dynamic join?
I'm using PostgreSQL if it matters.
Assuming these two tables:
id | run
----+-------
1 | run_1
2 | run_2
3 | run_3
and
sample | elapsed | run
-------+---------+-------
samp1 | 1:09 ...
1
vote
2answers
15 views
Invoke --data-checksums using pg_createcluster on Ubuntu 14.04
How do you pass the initdb argument for data checksums to pg_createcluster when creating the initial cluster?
Passing the argument as per initdb fails for me:
pg_createcluster --data-checksums ...
0
votes
1answer
29 views
How does Checkpoint know which point to recover from in PostgreSQL?
I'm trying to better understand Checkpoint internals, specifically in PostgreSQL.
My current understanding is that Crash Recovery will always start from the latest REDO location that is marked when
a ...
3
votes
2answers
37 views
Count rows with max per group and additional condition
I have the following table:
horse_main_id | race_id | horse_name | rating100 | race_result
---------------+---------+-------------------+-----------+-------------
23 | 159 | ...
-5
votes
0answers
37 views
PostgreSQL community channels other than mailing lists and IRC? [closed]
Is there an active channel for the PostgreSQL community other than the official mailing lists and IRC?
I'd hope to find a forum-like channel. This is the 21st century after all...
0
votes
1answer
26 views
PostgreSQL9.6 Beta1：new setting remote_apply for configuration parameter synchronous_commit
PostgreSQL9.6 add new setting remote_apply for configuration parameter synchronous_commit, Reading the following document I still don't understand the difference between on the setting on and ...
0
votes
1answer
65 views
How to optimize query with order by
Have query which generate OpenERP ORM. Table have 100k rows.
SELECT "tbl".id
FROM "tbl"
WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false ))
ORDER BY ...
0
votes
1answer
31 views
PostgreSQL values to unique dictionary
I have table with one billion rows and more than 50 columns. I need to reduce size and speed up queries, backup, exports, etc. Some columns contain f.e. only hundreds of distinct values which are long ...
2
votes
0answers
40 views
Insertion into postgres database stops (hangs) after table reaches a certain size without error
We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database. (Yes it is probably better to refactor the table ...
1
vote
1answer
28 views
When PostgreSQL will use Index Only Scan over Bitmap Index + Heap Scan
Today I've been doing some reading on the difference between the two and I think I've got a reasonable idea of each.
Index Only: Accesses heap pages as it moves through the index, possibly access the ...
1
vote
2answers
30 views
Scalable way to track who's viewed a post? [closed]
I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly ...
0
votes
2answers
44 views
CASE with max()
I know how to do this in SQL Server but Postgres is different.
I want to return the max employee pay.
Select b.enumber, b.bday,
case when Max(c.id) then c.pay
ELSE c.pay
End As "Current Pay"
From ...
0
votes
1answer
35 views
Unable to backup postgresql database. pg_dump memory alloc error
After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished.
The very first thing I attempt to do is to ...
0
votes
1answer
33 views
Get A Count From Table
I want to see all fsid limit 25 from my table -- I tried this query
Select *
from btmt
WHERE Count(fsid) > 1
limit 24
But this throws an error of
Aggregates are not allowed in WHERE ...
1
vote
1answer
29 views
Does PostgreSQL consider how evenly data is distributed on disk when choosing a seq scan?
I have a table items with an indexed column type_id. The table contains about 80 million rows. At the moment the first 5 million (in order of insertion) and the last 3 million have type_id = 8, and ...
0
votes
0answers
36 views
Postgres query taking 1minute to fetch one row
I am using postgres version 9.4 in my application.
I have a partitioned table named sql_sent_sms which is partitioned on the basis of primary keys.
I have a query which uses join on same table with ...
0
votes
1answer
20 views
Different behaviour between oracle and postgres regarding alter table primary key command
In Oracle 11g, when you write
alter table abc add constraint abc_pk primary key (one, two, three);
Automatically an index for one, two, three is added.
In PostgreSQL 9.5, when you do the same, ...
0
votes
0answers
41 views
Connecting Oracle 11g to Postgres via dblink
I have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow.
I've seen ...
4
votes
3answers
48 views
What is the easiest way to backup PostgreSQL and send backups to Google Drive?
I am new with PostgreSQL, So my question is: what is the easiest way to make regular backups and send them to Google Drive. Perhaps, there is a tool that can make it or something else.
1
vote
1answer
32 views
PostgreSQL won't allow remote connections
My Postgres installation won't allow me to connect remotely.
I set the listen_addresses field in the postgresql.conf to '*' and added the line
host all all 0.0.0.0/0 ...
3
votes
1answer
32 views
Postgres: Preventing objects with same name
I just spent one hour debugging a query which had previously worked but suddenly created strange results. The cause of this bug was that I have a table in one schema which I reference in the query. ...
1
vote
0answers
15 views
How can I upgrade PostgreSQL while using PgQ queue system?
I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then:
sudo ...
