Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-8.4]

PostgreSQL version 8.4

Specifically for PostgreSQL version 8.4 (Released Jul 01, 2009)

57 questions
88
votes
2 answers

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue to the column creation one that is solved with…
GuidoS
  • 1,047
  • 1
  • 8
  • 7
45
votes
3 answers

Using PostgreSQL 8.4, how to convert bytea to text value in postgres?

In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly fine i.e. results in Octet format String. See…
Amit
  • 591
  • 2
  • 5
  • 10
30
votes
2 answers

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration interface of our Django-based site. We're using Postgres…
David Eyk
  • 537
  • 1
  • 5
  • 11
24
votes
3 answers

How do I install pgcrypto in PostgreSQL 8.4?

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first. But I don't know how to install it. There is no…
Jonas
  • 33,945
  • 27
  • 62
  • 64
20
votes
7 answers

Form groups of consecutive rows with same value

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp , id_type integer ) ; INSERT INTO tmp (date, id_type) VALUES ( '2017-01-10 07:19:21.0', 3 ), (…
Lelo
  • 303
  • 2
  • 6
18
votes
3 answers

How to upgrade PostgreSQL from version 8.4 to 9.4?

I want to upgrade my PostgreSQL from version 8.4 to 9.4. The documentation is not very clear to me. Will I lose my old databases if I do the upgrade? How can I backup my old databases if I am to lose them after the upgrade? How can I upgrade my…
Ghasem
  • 549
  • 1
  • 5
  • 12
15
votes
1 answer

What are the privileges required to execute a trigger function in PostgreSQL?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that privileges set to a role do not matter when executing a trigger function. I think I have seen some day that the privileges required to execute a trigger…
user18077
12
votes
1 answer

Postgresql Rename Table without Updating View Definitions

I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off? So I rename the table with: ALTER TABLE xyz RENAME TO abc; And my…
Brandon
  • 223
  • 2
  • 6
12
votes
1 answer

PostgreSQL Transaction Committing for hours

I'm running into an issue whereby I have two connections from a user to my PostgreSQL server which have been running for about 4 hours and have been in a commit state for quite some time (at least 1 hour that I have been watching it). These…
ETL
  • 988
  • 6
  • 18
11
votes
2 answers

TOAST Table Growth Out of Control - FULLVAC Does Nothing

Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features and be in line with 30ish other PGSQL servers. This was done by a separate IT group who administrates the hardware, so we don't have…
BrM13
  • 113
  • 1
  • 1
  • 7
9
votes
2 answers

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - one as part of the PRIMARY KEY and the other a…
UrsineWelles
  • 143
  • 1
  • 6
8
votes
1 answer

pg_dump does not finish

I am trying to backup our postgres database (8.4.17), which is about 25GB in size. pg_dump database_name > database_db_dump_2014-05-05.sql unfortunately, the backup stops after about 600MB in, and does not continue. It always stops when trying to…
devsnd
  • 221
  • 1
  • 2
  • 5
7
votes
2 answers

Creating sequence for longer numeric values

I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API. The value must be 20 digit long, numeric and unique. Best approach seemed to create an auto increment big-int…
Mp0int
  • 459
  • 1
  • 6
  • 14
7
votes
1 answer

Safe way to stop/abort REINDEX

I am running a REINDEX but it takes long time and still working. The application using the DB is not responding right now. Is there a safe way to stop it and do it another time? PostgreSQL version is 8.4.
Omid Kosari
  • 173
  • 1
  • 6
6
votes
1 answer

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the language and re-add it, as that could screw some…
Jeremy Holovacs
  • 1,271
  • 4
  • 20
  • 27
1
2 3 4