PostgreSQL version 9.0

learn more… | top users | synonyms (1)

0
votes
1answer
18 views

Postgres: Missing data after Binary Copy - needs VACUUM FULL?

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 whole $PGDATA Dir via rsync of a LVM Snapshot and used archive/...
0
votes
1answer
77 views

Upgrade PostgreSQL 9.0 to 9.5 in Centos 7 does not work

We have an environment with Postgres 9.0 and centos 6.2 and want to migrate to Postgres 9.5 and Centos 7.2. We installed a new server with Centos 7.2 and Postgres 9.0.23 and restored a database ...
4
votes
1answer
1k 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 ...
2
votes
1answer
342 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 ...
2
votes
1answer
850 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
2answers
3k 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 specifc field. Consider the following table: lap_id (Serial), lap_no (int), car_type (enum),...
0
votes
0answers
78 views

Postgres 9.0 recovery from inadvertent drop-all-tables event

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 ...
10
votes
3answers
1k 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 --------------...
5
votes
1answer
6k views

Passing an array or record to a stored procedure in PostgreSQL

I have a task to pass arrays, records and in some cases array of records as a parameter to the stored procedures in PostgreSQL. Any ideas?
1
vote
1answer
16k 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 ...
0
votes
3answers
806 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 ...
1
vote
1answer
948 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 ...
6
votes
1answer
3k 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 ...