Questions tagged [postgresql-9.0]

PostgreSQL version 9.0

Filter by
Sorted by
Tagged with
13 votes
2 answers
11k views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column. Consider the following table: lap_id (serial), lap_no (int), car_type (...
0 votes
1 answer
120 views

Postgres 9.0 recovery from inadvertent drop-all-tables event [closed]

Due to a accidental config file check-in, a unit test wiped out our entire production database (did a drop, then re-created the tables). Trying to restore from backup revealed the recent backups were ...
4 votes
2 answers
97k views

PostgreSQL Error: server closed the connection unexpectedly

I cannot connect to PostgreSQL server from PgAdmin III , so I get this error: Error connecting to the server: server closed the connection unexpectedly This probably means the server terminated ...
18 votes
1 answer
62k views

Pass an array or record to a function in PostgreSQL?

I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.
5 votes
3 answers
5k views

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 ...
9 votes
3 answers
23k views

Postgres ERROR: tuple concurrently updated

I have a large table test in which in user_id 2 have 500000 records. So I want to delete this record in chunks of 100 records but it is given error. Here is my query: delete from test where test_id ...
0 votes
1 answer
422 views

Postgres: Missing data after Binary Copy - Is VACUUM FULL required?

I'm running Postgres 9.0 on a relatively big (10TB) and old Database. I must move that database to new server hardware, so I copied the whole $PGDATA directory via rsync of a LVM Snapshot and used ...
2 votes
1 answer
197 views

Setup Master-Slave replication with DML changes in Slave

I have a master database (PostgreSQL 9.0) and I need to setup a streaming replication on a slave. Slave db can read/write and make schema level changes too which shouldn't write back to master. Can ...
5 votes
1 answer
4k views

Creating crosstab() pivot table in PostgreSQL 9.0

I have a rather complicated issue for myself which I hope someone can help me with. I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table. To keep it simple I will ...
3 votes
1 answer
6k views

Add column with a sum total to crosstab() query in PostgreSQL 9.0

Following on from my previous question: Creating crosstab() pivot table in PostgreSQL 9.0 I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create ...
3 votes
1 answer
9k views

Find substrings within between 2 string fragments

I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code. CREATE OR REPLACE VIEW vw_actions AS ...
11 votes
3 answers
2k views

Where does the magic column "name" come from?

I got this by accident: db=> select name from site; ERROR: column "name" does not exist LINE 1: select name from site; ^ db=> select site.name from site; name --------------...
2 votes
3 answers
2k views

Vacuum settings for mostly append-only data

I have a table with the following characteristics: We INSERT a few 100k rows each day We never UPDATE the rows We DELETE "old" data once a week From my shallow knowledge of Postgres VACUUM, it seems ...
1 vote
1 answer
3k views

Postgresql querying trends

Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches So I have a database which records motor races, with the ...
7 votes
1 answer
8k views

How can I correctly choose maximum number of occurrences of a string while grouping by another field?

I am using Postgresql 9.0. I have the following fields in a table: id, name. id name 1 John 1 Mary 1 Mary 1 Mary 1 John 1 Mary 3 Paul 3 Paul 3 George ...