Questions tagged [postgresql-9.0]
PostgreSQL version 9.0
17 questions
0
votes
0
answers
30
views
How do I successfully migrate a (very) old PostgreSQL database with PostGIS objects to a new version of PostgreSQL and PostGIS?
I've been trying to migrate a PostgreSQL database on version 9.0.1 with PostGIS objects to Postgres version 12.9 (both on linux) and I've been encountering numerous errors. I believe the source ...
- 680
0
votes
1
answer
42
views
How can I find or log inbound traffic connecting to a PostgreSQL database instance?
I have a postgres instance where I need to find out who/what is connecting. The installed version of postgres is 9.0.1. The instance sits on a linux box, SUSE I believe. Where could I look if it is ...
- 680
9
votes
3
answers
25k
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 ...
- 632
0
votes
1
answer
452
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 ...
- 21
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 ...
- 145
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 ...
- 145
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
...
- 145
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 ...
- 411
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 ...
- 301
5
votes
2
answers
102k
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 ...
- 201
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 ...
- 123
2
votes
1
answer
198
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 ...
- 21
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 ...
- 319
14
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 (...
- 319
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
...
- 181
18
votes
1
answer
65k
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.
- 519
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
--------------...
- 905