PostgreSQL version 9.0
Specifically for PostgreSQL version 9.0 (Released Sep 20, 2010)
Questions tagged [postgresql-9.0]
18 questions
18
votes
1 answer
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.
Worker
- 519
- 2
- 7
- 13
14
votes
2 answers
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 (enum), race_id (int FK)
Where lap_no is unique for…
DaveB
- 319
- 2
- 6
- 16
11
votes
3 answers
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
---------------
(1,mysitename)
(1 row)
The second query return a…
hegemon
- 905
- 1
- 6
- 8
9
votes
3 answers
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 in (select test_id
from test where User_id = 2…
Saddam Khan
- 642
- 1
- 7
- 25
8
votes
1 answer
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
. .
. .
For each id, I want to select…
Tudor
- 191
- 1
- 4
5
votes
3 answers
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 space to the O/S.
We do not have enough space to do a…
Brendan Hill
- 301
- 1
- 4
- 11
5
votes
2 answers
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 abnormally before or while
processing the request.
I…
geogeek
- 201
- 1
- 3
- 6
5
votes
1 answer
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 just show the table structure i want to use for the…
daniel franklin
- 145
- 3
- 9
3
votes
1 answer
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 a view or table of the base geometry-less…
daniel franklin
- 145
- 3
- 9
3
votes
1 answer
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
SELECT ls.f_table_schema, ls.f_table_name,
…
daniel franklin
- 145
- 3
- 9
2
votes
3 answers
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 like this table might be a good candidate for a…
Larsenal
- 123
- 6
2
votes
1 answer
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 you please highlight how can I do this?
user24908
- 21
- 1
1
vote
1 answer
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 following simplified schema
race_table
==========
race_id…
DaveB
- 319
- 2
- 6
- 16
1
vote
1 answer
Do I need to CREATE LANGUAGE plpgsql in PostgreSQL 9+
I have a PHP web application using a PostgreSQL database.
Ideally, to install this web application should be easy and only require a database name and user.
Then, the web application loads an SQL file and imports the tables, indices, and…
François J.
- 21
- 3
0
votes
1 answer
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 corrupted and the only valid backup we have is…
John P
- 411
- 1
- 3
- 7