PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

0
votes
1answer
246 views

No way to set maximum retrieved rows in pgadmin query tool

I'm using version 1.16.1, and I can't find a "max(imum) rows" param to tweak. Any help would be appreciated.
4
votes
1answer
1k views

How to simplify a nested SELECT with PostgreSQL arrays?

I'm trying to speed up and simplify a SQL query against an imported OpenStreetMap database (OSM). The database is stored in a PostgreSQL 9.2.4 server. This OSM import features two particular tables, ...
4
votes
1answer
98 views

How to get the information of the optimizer of PostgreSQL?

I am curious about the information like total optimization time the time spent on each stage of optimization the number of alternative plans and the cost of each alternative plan.
0
votes
0answers
95 views

Identical Postgres index scan taking 5x longer on server

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
0
votes
1answer
198 views

Pattern matching and substring replacement in PostgreSQL [closed]

I would like to do pattern matching and substring replacement in PostgreSQL with a "select-case-when-then-update-query". Example: tableA field1 |field2 | field3 ...
0
votes
1answer
334 views

How to install and configure Postgres-XC in windows?

Can anybody suggest me any resources of the complete installation procedure of postgres-XC in windows. I've installed postgresql-9.2 in my windows and can use it. Now I need to know the installation ...
12
votes
1answer
2k views

How can I benchmark a PostgreSQL query?

I want to benchmark a query containing an user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark? I know that timing can be turned on with \timing ...
0
votes
1answer
102 views

PostgreSQL created a PostgreSQL user on mac. Is this necessary?

I just installed PostgreSQL on a Mac (Mountain Lion) today. The installer created a mac user PostgreSQL on my machine. After installation, the user still exists. My question is that why postgreSQL ...
1
vote
1answer
264 views

How to monitor or do vacuum without stop all?

I have postgres9.1 I try to run full vacuum on a specific table , but it run for more than 5 hours. I stop all process against this table and i cannot have the process down any longer. Is there a ...
4
votes
1answer
123 views

How to view the full, flattened query underlying a Postgresql view?

If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?
6
votes
2answers
824 views

Seeting up a PostgreSQL database on another hard drive than my system one on OS X?

I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data. Is there a way to create new ...
0
votes
1answer
98 views

Syntax error when trying to import database from two PostgreSQL databases

Following on from this prior question, I'm trying to export a database from one server and put it on another using phpPgAdmin. My process is like this: select the database in server 1 and hit the ...
0
votes
1answer
256 views

Postgresql Run 'Jobs' At Specific Times

I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute . Scenario : PostgreSQL 9.2 Need To Run A ...
6
votes
2answers
226 views

Postgres performance difference on insert

I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions? I went quite straight forward way (i know that it is not ...
1
vote
1answer
136 views

Restrict access for Windows administrator in PostgreSQL

Is there a way to protect PostgreSQL from user with Windows administrator rights? Like this question but for Postgres.
1
vote
2answers
132 views

Group Database Entries by time difference

I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
3
votes
1answer
532 views

Is replication from SQL Server 2008 to PostgreSql possible?

Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.
5
votes
2answers
2k views

Problems connecting to Windows-based Postgresql

Other computers are having trouble even finding the running Postgresql 9.1 on my Windows 7 machine. Local connections work just fine of course. In my pg_hba.conf I have: #office subnets host all ...
1
vote
1answer
2k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
1answer
340 views

Is it safe to call pg_ctl reload while doing heavy writes?

I am running Postgres 8.4, and I would like to change the authentication method for a user. It's my understanding that I have to do this in pg_hba.conf, and then reload the configuration files with ...
2
votes
2answers
690 views

Changing PostgreSQL port using command line

My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7). I saw this solution before: http://stackoverflow.com/questions/187438/want-to-change-pgsql-port ...
0
votes
0answers
380 views

Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs

I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs. All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
2
votes
4answers
1k views

Database design - People and Organisations

Databases are not my primary skill and I am finding I need some assistance please. The software we are building has "Customers". The Customer could either be a Person or an Organisation. I really ...
0
votes
1answer
670 views

Use cases for hstore vs json datatypes in postgresql [closed]

In Postgresql, the hstore and json datatypes seem to have very similar use cases. When would you choose to use one vs. the other? Initial thoughts: You can nest with json; you can't with hstore ...
8
votes
2answers
4k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
4
votes
1answer
89 views

Why do psql queries take longer when inserting many rows. Why is it non linear

So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this: INSERT INTO contacts (numbers) SELECT distinct array[ (random() * 99999999)::integer, ...
0
votes
1answer
180 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
4
votes
2answers
355 views

Is it ever a good idea to denormalize for integrity?

I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers. These problems can take a variety of forms - they might be multiple ...
1
vote
1answer
140 views

Foreign Key to Multicolumn Primary Key?

I have three connected table in my design. Basic logic is when a category deleted, also delete records in category_product table which has deleted category's id in its cat_id column. But when I tried ...
1
vote
1answer
88 views

Big jump in search time for Postgres Index query for results with high selectivity

I am doing some performance comparison of databases and lucene for full-text searching. So I use Postgres to create an Index for the data to search: CREATE INDEX bodies_index ON bodies USING gin ...
0
votes
0answers
445 views

Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability. I have 1 master and 1 hot standby configured to replicate the data from ...
1
vote
0answers
79 views

PostgreSQL datasource URL?

My first attempt to use PostgreSQL: I have the elephant icon on the top right: running on port 5432. I want to use LibreOffice as an interface: Connect to an existing database: PostgreSQL ...
1
vote
1answer
731 views

PostgreSQL stored function that returns arbitrary resultset

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
8
votes
2answers
1k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
1
vote
1answer
432 views

Behavior of PostgreSQL COPY FROM

Let me preface this question by stating that my experience with DBA is limited to an introductory undergraduate university course, and that I'm just getting started with PostgreSQL. I would like to ...
0
votes
0answers
282 views

PostgreSQL: Slave doesn't replicate the Master's data

I'm using PostgreSQL 9.2.3 on both computers, both of which run Win2008R2. I've setup streaming replication by following this guide: ...
1
vote
1answer
245 views

Table for optional parent/child relationship

Assuming we have the following table: Item, Parent, Child and Parent is the parent of child. The item can either belong to a parent or child and not both. I have other tables that are similar to ...
2
votes
1answer
1k views

Violates foreign key constraint

I'm trying to implement the below represented UML and I can't figure out where is my mistake since I have already place a value into the table. -- Schema: public DROP SCHEMA IF EXISTS public ...
0
votes
1answer
41 views

Storing no of players the last seven days in an online game

I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how ...
1
vote
2answers
452 views

Dropping a group of schemas with similar name patterns

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object. In my case, I need to drop some schemas, all of the ...
4
votes
1answer
543 views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
0
votes
1answer
362 views

Cannot create PostgreSQL user

I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04. Using the following StackOverflow question, I tried to create a user/role in postgressql: ...
1
vote
1answer
731 views

PosgreSQL: setting high work_mem does not avoid disk merge

This is not quite my day with postgres. On my server machine with PosgreSQL 9.2.3 I have set work_mem to 4MB to avoid Sort Method: external merge Disk: 2072kB but it did not help: cwu=# vacuum ...
2
votes
2answers
2k views

Creating a PostgreSQL SERIAL column using pgAdmin3

When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)? Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
2answers
926 views

Why PostreSQL 9.2 does not use index scan if it can?

My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan. Here is my table. The index I am talking about is name_left_prefix cwu=# \d web_city; ...
1
vote
2answers
128 views

“Rewinding” a Postgresql database

I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
1
vote
1answer
159 views

How to allow a user to create databases only with a certain prefix?

We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must ...
4
votes
1answer
172 views

Is there better way to 'touch' a row than performing a dummy update?

I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update: ...
4
votes
1answer
1k views

How to fine-tune Postgres for IO usage?

I have a Postgres database I am hosting in a quad core server with 8 GB of RAM. At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When ...
5
votes
1answer
738 views

How to improve performance on PostgreSQL when using multiple concurrent processes?

Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...