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
0answers
4 views

Inserting raw JSON inside an array via a prepared statement

I am using this excellent format to do bulk inserts via libpqxx: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
1
vote
0answers
25 views

Normalized bulkinsert

I have been shown an incredible way to bulkinsert multiple rows to a child table while simultaneously inserting a row into the parent: WITH p AS ( INSERT INTO parent_table (column_1) SELECT ...
1
vote
1answer
28 views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
0
votes
1answer
11 views

postgresql being slow on count distinct for dates

I have a very simple, but very big, table. Its schema is like this (yadda int, yadda1 int, yaddate date, ... other stuff). Now, yaddate has an index by itself and it is also in other indexes ...
0
votes
1answer
11 views

Unable to Start PostgreSQL. kernel's SHMMAX parameter exceeded you limit

When I am trying to start my PostgreSQL I get the following error in the Postgresql.log file: FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was ...
0
votes
1answer
28 views

Postgresql condition on subquery results

This is a new question based on the solution to my previous question I have the following tables: users int (pk) user_id date last_visit int (fk) local_site sites int (pk) site_id site_stats ...
0
votes
1answer
16 views

Insert a file into PostgreSQL BYTEA field using PHP

The previous answers have been horribly complex for the scope of this project. I want to take the $_POST result as a variable and insert it into a BYTEA field of a table. $_SESSION['username'] = ...
2
votes
1answer
38 views

Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

Steps to reproduce Create database CREATE DATABASE citiesdb WITH OWNER = citiesowner ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' ...
0
votes
0answers
21 views

While impoting 150 GB file on Amazon RDS, why connection is failing?

I am having Postgres database dump file of 150 GB on Amazon EC2 instance. While dumping the data on RDS from EC2 server I am getting error. The output of the command is as given below. It's giving ...
1
vote
1answer
31 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
0
votes
0answers
25 views

Update query taking 8x times in hot standby environment of Postgresql

I configured streaming replication (hot standby) and tested the update query on it. The update query is taking 8 times more when compared with stand-alone PostgreSQL (9.2 version) system. Some ...
2
votes
1answer
22 views

Recovering a partial PostgreSQL archive

I have $PGDATA/base but nothing else. It was taken from an installation after the postgreSQL service was shutdown, so is clean. Is there any way to recreate the other elements in $PGDATA that allow ...
2
votes
1answer
27 views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
0
votes
0answers
30 views

PostgreSQL DB size increasing after restoring the same database backup [on hold]

I have already asked this in stackoverflow. This is my batch script to take backup: @ECHO OFF d: cd Apps\RemedyStar 9.0 KREMCOS\bin\Debug\PG pg_dump.exe -i -h localhost -p 5432 -U postgres -F c -b ...
2
votes
2answers
86 views

How slow is slow in postgres?

I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now ...
1
vote
1answer
73 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
-1
votes
0answers
27 views

Blogs with real life experience in PostgreSQL [on hold]

Can anyone point me to any blog posts or articles describing someone's real life experience with Scalability using PostgreSQL?
2
votes
1answer
46 views

Why write ahead logs in PostgreSQL are generated every second

PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute. Is it possible to make them occur less frequently? Is it possible to increase the WAL ...
1
vote
1answer
33 views

Increased performance from single statement, multiple row inserts?

I'm currently obsessing over bulk inserts via prepared statements with libpqxx. I am far from a database expert and even further when it comes to Postgres, but just thinking superficially, it doesn't ...
0
votes
1answer
27 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
3
votes
0answers
24 views

Is PostgreSQL or ElasticSearch/Solr the right tool for this kind of query?

Here is an example of the current PostgreSQL query: SELECT a.*, a.al_location <-> '(someLon, someLat)' AS d FROM al_app.al_activity AS a WHERE to_tsvector('simple', a.al_keywords) @@ ...
1
vote
1answer
35 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
0
votes
0answers
14 views

Postgresql not working: error

My Postgres was working fine on my ubuntu 12.10 before i installed postgres-xc and execute a command initdb for changing encoding. After that it started showing error like: psql: could not ...
-3
votes
0answers
53 views

Fastest way to Restore 50TB DB [on hold]

I am trying to find out if there other ideas as what the fastest way is to restore a 50TB DB.
3
votes
3answers
47 views

PostgreSQL 9.0 how to reclaim disk space after delete without rebuilding table?

Our PostgreSQL 9.0 Windows production server is running low on space. In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the ...
0
votes
1answer
34 views

postgresql bigserial or sequence using jpa

I'm a java developer working with jpa, before to define the indexes in my entities i would like to known which one is faster o better to use?.
0
votes
1answer
62 views

Adding unsigned 256 bit integers in PostgreSQL

I was wondering if there would be any way to do the following in PostgreSQL: UPDATE cryptotable SET work = work + 'some big hexadecimal number' where work is an unsigned 256 bit number. Right now ...
1
vote
0answers
16 views

Data Corruption on base backups created with wal-e?

I have streaming replication setup on Postgresql 9.1 with two machines on AWS EC2. The master has a cron job that does a daily base backup using the wal-e tool and uploading it to Amazon's S3 storage. ...
0
votes
1answer
38 views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
-1
votes
2answers
42 views

Select records for a day and a day - 1 day

From one table, I want to get the records from one column position when the created_at = Today and when the created_at = created_at - 1.day COALESCE(array_agg(DISTINCT pr.position)) AS position, ...
1
vote
1answer
29 views

How to Configure PostgreSQL Hotstandby Automatic Failover

So I have configured two postgeSQL servers to have hotstand by streaming, now my question is, how can I configure it so that when the master server goes down, the slave server will become the new ...
1
vote
1answer
18 views

What is the “last” data that get's backed up, when running pg_dump on a live server?

I'm wondering: If I start doing a pg_dump of a very large database (it would take hours), that is still running actively receiving writes, what is then the last data that goes into the backup? Is it: ...
1
vote
1answer
30 views

log_min_duration_statement setting is ignored

I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows. I have 2 databases that are actively in use and they are used from a remote server. I ...
-3
votes
0answers
20 views

parallel inserts on postgres database [closed]

I want to insert multiple records from multiple clients into a single postgres database. Does it effect my database performance? if it effects how can i recover my potgres database.! can any one ...
5
votes
2answers
85 views

PostgreSQL, integer arrays, index for equality

I have a huge list of integer arrays (300,000,000 records) stored in Postgres 9.2 DB. I want to efficiently search these records for an exact match (equality only). I have heard of the intarray module ...
3
votes
1answer
28 views

What does “ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table ”fruits“” mean?

After being bitten by the bug mentioned in the release notes of 9.3.4, I have realized that a very small part of my data is corrupt, after doing a failover. The bug showed it self as duplicate rows, ...
0
votes
1answer
29 views

Why is it not possible to alter a view with a “CREATE OR REPLACE”?

The Postgresql documentation is clear on this point: you can not change the columns of an existing view. The new query must generate the same columns that were generated by the existing view query ...
3
votes
2answers
50 views

Postgres Streaming Replication lagging, using lots of CPU and little I/O

We have 2 dedicated DB Servers. One is master and other is its streaming slave. Postgres version 9.0.17. We have similar hardware on both servers and using 15krpm SAS drive in Raid 10 for Database ...
4
votes
1answer
134 views

How to apply a patch in PostgreSQL on Windows?

How to apply a patch in PostgreSQL? I found an advice, but it seems to be suited for *NIX only. My PostgreSQL (9.3.1 EDIT: 9.3.4 now) was installed from an exe installer on Windows XP (32 bit), so I ...
1
vote
1answer
63 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
2
votes
0answers
83 views

Why does my table hold duplicate primary keys?

To my big (and worrying) surprise I just realized that my table with millions of rows, has a few handful of rows, where the primary key (ID) is a duplicate! I don't understand how this can ever ...
0
votes
1answer
28 views

Installing pgcrypto Extension

I have this password hash function that hashes a password to sha256. I've already got it running in pgadmin3 in a local database. I've done it by importing the extension, a feature that has been added ...
0
votes
0answers
20 views

Organizing a couple tables in postgresql

I have a data structure that is represented like this: { 'question_id': 1, 'is_required': False, 'question_text': [ { 'id': 1, ...
3
votes
1answer
78 views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
4
votes
2answers
75 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
1
vote
1answer
36 views

PostgreSQL 9.1: vacuumdb vs. autovacuum

Have a question on the difference between just allowing autovacuum to take care of vacuum'ing or having to run a full db vacuum in a certain interval (every night?, every week?). We used to run a ...
2
votes
0answers
69 views

Why does this query cause the Postgres worker process to consume so much memory?

I have a PostgreSQL 9.3.4 instance running in a CentOS 5.x VM (Linux 2.6.18-308.8.2.el5xen) with 6GB RAM. I've set shared_buffers to 1GB and work_mem to 1MB (a low value for testing). Here are my ...
3
votes
3answers
184 views

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity. This are the message columns : id sender_id receiver_id body activity_id I'm ...
1
vote
1answer
49 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
6
votes
2answers
106 views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...