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.

learn more… | top users | synonyms (1)

0
votes
0answers
50 views

Can't install postgis on PostgreSQL 9.4 [closed]

I need to install postgis into my PostgreSQL 9.4 server locally. I've taken the shotgun approach and installed all related packages for postgis: sudo apt-get install postgis* however, executing: ...
1
vote
1answer
54 views

Poor query performance due to recheck condition on index scan

We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server. I get a problem when ...
4
votes
3answers
234 views

Custom sorting of a query

The application I am working on displays some tasks based on status, and date (in that order). However, for a particular status, the sort condition should be inverted. For example, a table which looks ...
6
votes
2answers
186 views

Count data using multiple date ranges

Probably this has been asked before, but I can't figure it out. I have a phone_clicks table (sql fiddle http://sqlfiddle.com/#!15/855e0/1) CREATE TABLE phone_clicks ( id integer NOT NULL, ...
1
vote
0answers
31 views

How to return updated data from UPDATE … FROM query?

UPDATE "tbl1" SET "col" = 'val', FROM "tbl2" WHERE "tbl2".id = 1 RETURNING * This query returns values from tbl2 instead of updated data from tbl1 - how can I get updated data? (PG 9.3).
0
votes
1answer
36 views

Distinct rows by order

I have the following SQL, generated from ActiveRecord in Rails 4 (partly with the Geocoder gem) problem is, that even though DISTINCT is used, hopefully to get only unique items, the items returned ...
2
votes
3answers
126 views

Aggregate query with min

Having a product_stock table with following structure: product_id warehouse_id stock price Which has bunch of records (same product can be in different warehouses): 1, 1, 0, 500 1, 2, 5, 505 1, 3, ...
0
votes
1answer
58 views

Where do you find the citext module in postgres 9.3 on ubuntu 14.04?

So I'm trying to use citext, but it appears to be MIA from ubuntu 14.04 postgres install. Where can you find this mythical beast called citext for postgresql 9.3? $ manage dbshell Password for user ...
3
votes
1answer
228 views

how can an additional natural join with the same table twice *reduce* the row count

This is real results from my database. I spend some time trying to reproduce this behavior with an SSCCE but failed. How is the last of the below results possible? SELECT COUNT(*) FROM rr.resource ...
1
vote
1answer
60 views

Recommended Postgres settings to speed up indexing on a 100GB database?

I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk. I will be using this server to serve both my site and my database (i.e. it's not just a database server). ...
0
votes
0answers
43 views

pg_hba.conf and postgresql.conf in old installation directory

I have installed the PostgreSQL 9.4 on my MacBook via MacPorts, and I am trying to edit pg_hba.conf, but I don't see it in my current installation. Furthermore, psql is reporting that config_file is ...
0
votes
1answer
51 views

What is the most efficient way to count the number of rows in a table?

I am using postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
1
vote
0answers
18 views

Techniques for checking that data loaded correctly in Postgres?

Apologies for the newbie question! I am working with a largeish dataset (500m rows, 150GB of data) and Postgres 9.1 running on Debian. I have a Python import script for importing the source data, ...
0
votes
0answers
72 views

upgrading posgresql 9.0 to 9.4 with pg_upgrade on CentOS 6.6

I'm trying to upgrade a postgres v9.0 database to v9.4 with pg_upgrade on CentOS 6.6, but I keep hitting a wall. I am calling pg_upgrade thru a shell script as postgres, first exporting the needed ...
0
votes
1answer
21 views

Tool to find CPU utilization for a particular query in Postgres

I just want to know is there any free tool to identify CPU utilization for a particular query in PostgreSQL.
-1
votes
1answer
63 views

Out of the box why is a simple query from a 50gb table taking 5x longer in postgres vs sql server--help? [closed]

As you can see I'm a newbie and been flailing for 2 weeks. I am doing a simple query such as: "select * from all_visits where reason_for_visit in ('CE233','4E233','GH399') This query in Postgres ...
0
votes
1answer
22 views

how to delete all rows with empty field via function/trigger combo in Postgres v 9.3?

In PostgreSQL 9.3 I am trying to write a trigger function that will be called after updates to table test2 and that will delete rows of test2 where field "email" is empty. Here's my attempt: CREATE ...
0
votes
1answer
17 views

PostgreSQL copy text type column to text[] array type column

I have two columns, material_size (text type) and material_size_temp (text[] array type). When I ran UPDATE parts SET material_size_temp[0] = material_size; I would get values like [0:0]={".021 x ...
2
votes
1answer
39 views

Postgresql import from SQL Server - how to auto-generate sequences

As the title states I'm switching from SQL Server to Postgresql via SQL dump. The tables and views have been migrated correctly, alas the IDENTITY columns have not been properly converted into ...
0
votes
1answer
51 views

Select on multiple tables with group by

Using Postgres 9.3, 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| | ...
4
votes
1answer
53 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
1answer
41 views

Working with Materialized View

I have a materialized 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
58 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
33 views

anonymous blocks inside a transaction [closed]

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$ ...
0
votes
0answers
23 views

CPU utilization for a particular query in Postgres [duplicate]

Is there any way to know how much CPU particular query is utilizing in PostgreSQL?
0
votes
1answer
38 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
27 views

Please help: kerberos authentication setup on postgresql

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
1answer
28 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
1answer
29 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
28 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
2answers
43 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
25 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
9 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
34 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
82 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. ...
1
vote
1answer
83 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
43 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
25 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
51 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
38 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
40 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
20 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
60 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
48 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
58 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
1answer
56 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
25 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
32 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
65 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
48 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 ...