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
1answer
131 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
264 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
79 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
115 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
62 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
23 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
124 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
22 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
67 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
35 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
23 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
47 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
62 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
60 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
60 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 ...
4
votes
1answer
120 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
57 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
51 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
32 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
40 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
37 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
31 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
54 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
30 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
45 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
136 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
115 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
66 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
31 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
82 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
53 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
48 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
26 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
67 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
69 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
83 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
63 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
32 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
33 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
70 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
60 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
121 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
54 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
115 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
33 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
50 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
48 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
30 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 ...