Questions tagged [postgresql-9.0]
PostgreSQL version 9.0
15
questions
5
votes
3answers
10k 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
1answer
268 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
1answer
4k 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 ...
4
votes
1answer
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
1answer
7k 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
...
0
votes
1answer
111 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
3answers
3k 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 ...
1
vote
2answers
52k 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 ...
2
votes
3answers
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 ...
2
votes
1answer
180 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 ...
1
vote
1answer
2k 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 ...
12
votes
2answers
9k 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 (...
7
votes
1answer
6k 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
...
15
votes
1answer
35k 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.
11
votes
3answers
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
--------------...
