PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.
0
votes
0answers
4 views
PgBouncer optimal settings for a high OLTP workload
Hope you are all well. I am trying to figure out how to best optimize our pgbouncer settings. I need to be able to set it so as to allow quick disconnects after a transaction finishes. Whats ocurring ...
0
votes
0answers
14 views
postgres - select on multiple tables with group by
I have problem with a select over few tables.
I have simcards stock and I would like to get something like this:
| network | supplier | total topups (EUR) | 1st topup qty|
| three | three ...
3
votes
1answer
24 views
SQL - Inverse Selection
I have two tables in my schema
students(id text, matric text)
attendance(id serial, a_date date, matric text references students matric)
To get records showing date and student present, I can run ...
0
votes
0answers
16 views
Working with Materialized View
I have a materialised view which takes around 57 second to be created and I'm using Postgresql 9.4.
When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH ...
3
votes
0answers
17 views
Postgres pg_basebackup reports directory exists but is not empty
I have postgreSQL 9.4.1 installed on a Win 2012 Server.
I am looking to setup replication between this and another server of the same spec. So I am following guidance on preparing the master ...
1
vote
1answer
22 views
anonymous blocks inside a transaction [on hold]
I have the following script:
DROP TABLE IF EXISTS a;
CREATE TABLE a(
val VARCHAR NOT NULL
);
DO
$do$
BEGIN
INSERT INTO a(val) VALUES('a1');
INSERT INTO a(val) VALUES('a2');
END;
$do$
...
1
vote
0answers
16 views
CPU utilization for a particular query in Postgres
Is there any way to know how much CPU particular query is utilizing in PostgreSQL?
0
votes
1answer
20 views
PostgreSQL - is it possible to use a plpgsql function in a bash script?
Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up).
Problem : Since between the partial-backup ...
0
votes
0answers
13 views
Procedure to set-up authentication method for postgresql connectivity by using windows as front-end and RHEL as backend
Requirement: Backend working with RHEL and postgresql and front-end working with windows,.net. please consider how the authentication method must be done. our main idea will be to have the ...
0
votes
0answers
22 views
Set And Get Single Value Based On An ID In PostgreSQL
I asked this question on stack overflow but thought that it might be better to ignore the programming language and just focus on the database element of my question, as that is where I am going ...
0
votes
1answer
17 views
Moving PostgreSQL database to new server
I had PostgreSQL 8.3 running on a windows machine. The hard drive recently became unbootable, but the data is fine. We got a new machine and I'm re-installing Postgres. The new installer only has ...
0
votes
0answers
19 views
Web interface to a database open to internet [on hold]
I want to demo a new index that we built for postgres. To demo it we want to open up a web-shell where any user can write a SQL query and apply it against a live postgres database to measure response ...
0
votes
0answers
18 views
Errors importing data using COPY comand at postgresql 9.3.5
I am trying import a database table to Postgres 9.3.5 database server using COPY command as follows:
COPY comment (generatedid, id, "timestamp", message, bugreport_id, personcontainer_id) FROM ...
0
votes
1answer
22 views
What does a CONSTRAINT have to do with my unique index?
I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
0answers
14 views
syntax error at the end of input (jdbc:postgres) [on hold]
The below postgresql query works fine if directly run on server through PuTTY. But throws error "Syntax error at the end of input" if run through java program. The postgresql version is 9.1.9 and uses ...
0
votes
2answers
24 views
Match multiple components of a subquery CTE in PostgreSQL
I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
0
votes
0answers
9 views
can one database participate in bdr master to master, as well as a master to slave setup?
This might seem like a silly question but I have to ask.
Let's say we have databaseA in POP1. We need to replicate over to POP2 and POP3. But within each each POP, we also have databaseB that is a ...
0
votes
0answers
20 views
Do Statement-level AFTER triggers occur asynchronously after the statement returns?
I'm using an AFTER INSERT OR DELETE OR TRUNCATE ... FOR EACH STATEMENT trigger on a table to refresh a materialized view, and I'm concerned that it will significantly affect the performance of those ...
0
votes
0answers
22 views
PostgreSQL : WARNING: there is no transaction in progress [on hold]
I'm working on a bash script that will backup a part of a database. Following the general procedure adopted:
connect to the main database
create a new schema , let's call it tmp_schema
recreate all ...
0
votes
0answers
7 views
How many postgresql / BDR database can be replicated from a server? [duplicate]
I've been reading the postgresql bdr docs found here: https://wiki.postgresql.org/wiki/BDR_User_Guide
I came across this comment in the above doc:
BDR operates per-database, not per-cluster ...
0
votes
0answers
20 views
Please Help: LDAP authentication set-up over PostgreSQL
I received LDAP details and linux user details from my client. He asked me to set the LDAP over PostgreSQL. when i did so i am getting error:
"invalid authentication method "ldap": not supported by ...
0
votes
0answers
16 views
Centos 7, Postgresql Failed to Read Permission Data Directory in mounted Partition
I've a problem with start PostgreSQL 9.4 on Centos 7 Server. Because the partition /var/lib/pgsql/9.4/data/ (which contains PostgreSQL data) is full so I want to change to another partition.
...
0
votes
0answers
21 views
postgresql 9.4 + BDR Fails
I'm trying to get postgresql 9.4 and BDR working. Following this tutorial: https://wiki.postgresql.org/wiki/BDR_Quick_Start
Actually, my sysadmin provided me with a package to test with. He's taken ...
2
votes
1answer
33 views
What is the impact of LC_CTYPE on a PostgreSQL database?
So, I've few Debian servers with PostgreSQL on it. Historically, those servers and PostgreSQL are localized with the Latin 9 charset and back then it was fine. Now we have to handle things like ...
0
votes
1answer
15 views
COMMIT after EXPLAIN ANALYZE of a read-only query
In PostgreSQL, what effect could produce issuing a COMMIT after executing an EXPLAIN ANALYZE with a read-only query?
1
vote
1answer
26 views
PostgreSQL - partial dump of large orbjects (pg_largeobject table)
Context : Our web-app is like a collaboration platform where you can share files, appointments, survey and so on. An instance of this web-app is divided in communities, each one is independent to each ...
2
votes
1answer
25 views
Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?
Let's say I have a table with 1 000 000 records.
Structure of table is:
create table individual (
id serial primary key,
surname varchar(128),
"name" varchar(128),
patronymic ...
2
votes
0answers
27 views
Execution plan of query inside pl/pgsql function
Briefly, we have a pl/pgsql function which collects some intermediate data in several temporary tables ("on commit drop") and then returns REFCURSOR which joins those temporary tables with some static ...
0
votes
1answer
16 views
Can an active Postgres serverlog be truncated?
Running Postgres 9.1 on Ubuntu 14.10 LTS.
I've got an application running on a machine that's going to run out of diskspace soon. However, the application is in the middle of a crucial run that has ...
1
vote
1answer
47 views
Grouping() equivalent in PostgreSQL?
I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below.
How can this be translated to PostgreSQL?
select top ...
2
votes
1answer
39 views
Better way to select from nested tables in PostgreSQL
I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
0
votes
1answer
38 views
Postgresql - Minor version upgrade 9.3.5 to 9.3.6 on Ubuntu
I am two servers: A & B. My master database is running on A and the version is 9.3.5. It's replica is running on B and the version is 9.3.6. I am trying to use B as UPSTREAM slave, and trying to ...
3
votes
0answers
35 views
INSERT inside PL/pgSQL function gives different result
In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function ...
0
votes
1answer
21 views
Converting hstore keys into columns in postrges
I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns.
Users can ...
1
vote
2answers
30 views
Calculating the number of times a motive was cited over a given period
I'm using PostgreSQL 9.4. I have the following surveys table. Each row includes a motive that was cited by the respondent, represented by the motive_value column (think of it as motive_id).
...
1
vote
2answers
51 views
Combine results of two queries, where the second relies on the first
I have one query which is returning
name(text), total, created_at
And another query which is returning the same thing
name(text), total, created_at
The second query usually is returning more ...
0
votes
1answer
36 views
PostgreSQL - Generate unique name on insert based on other columns
I have this table:
create table people (
id serial primary key,
name varchar,
surname varchar,
uname varchar unique
);
I want to generate the value of uname on insert, based on name ...
1
vote
1answer
25 views
Delete array element by index
Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.)
I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but ...
1
vote
2answers
40 views
Combine multiple similar queries into one
I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5.
...
2
votes
1answer
42 views
Can bdr only replicate one database per server?
I have installed the new BDR-Solution with PostgreSQL 9.4 and it works like a charm.
My Problem: I want to add more databases to the replication but every time if I added the other database and ...
1
vote
0answers
22 views
Postgres configuration, incoming connections
Hello I'm trying to figure out the best way to allow connection from certain ips/hosts to my db instance.
I've got an api(s) pointing to the database and right now we do something like this ...
0
votes
3answers
37 views
Group by transformed column
I have a database with creations and orders, and a bunch of tables linking them.
I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a ...
1
vote
1answer
33 views
Create/copy new superuser from an existing one
I have a superuser for a PostgreSQL database, let's call him userX.
How can I make a new superuser (let's call him userY) with all the same permissions, access, etc...?
Basically, I want a clone of ...
0
votes
0answers
26 views
Integrating Postgres/GIS data in a SQL Server environment without generating 'data islands'
Currently I'm facing a scenario in which a GIS expert is planning on starting to integrate mapping services for several departments within the organization I work for.
I handle around 40 mid-sized ...
0
votes
1answer
29 views
Get table name by foreign key
I have a two tables in the database. First table has a foreign key to second table.
Can I get second table name by the foreign key from first table?
0
votes
1answer
24 views
Dropped column still has value when recreated with Postgres table of 150M rows
I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working:
ALTER TABLE ...
0
votes
1answer
52 views
Pgpool II : unable to read message length between two network interfaces
Start log file:
2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for 0.0.0.0:9999
2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for :::9999
2015-03-06 01:57:56: pid 2760: LOG: ...
0
votes
2answers
38 views
What is more efficient: text(765) or varchar(765) to store a freeform searchable description?
EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back?
A little background on the question. Let's say we have sales_orders table and we want to be able to keep a ...
0
votes
0answers
35 views
Table with both primary key and unique key
My system requirement defines a unique business/domain/natural key (i.e. <yyyymmdd><branch_id><running_number>, in bigint). Should I use this unique key as primary key or I should ...
0
votes
1answer
47 views
How to avoid duplications using a function
I am using this function in a bulk insert to avoid duplication of url paths.
CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4)
RETURNS "int4"
AS $BODY$
DECLARE
...
