Stack Overflow
Stack Overflow

Questions tagged [postgresql-8.4]

This is for PostgreSQL questions specific to version 8.4.

Many of the changes in PostgreSQL 8.4 are new or improved administration and monitoring tools and commands. Each user has their own favorite features which will make day-to-day work with PostgreSQL easier and more productive for them. Among the most popular enhancements are:

  • Parallel Database Restore, speeding up recovery from backup up to 8 times
  • Per-Column Permissions, allowing more granular control of sensitive data
  • Per-database Collation Support, making PostgreSQL more useful in multi-lingual environments
  • In-place Upgrades through pg_migrator (beta), enabling upgrades from 8.3 to 8.4 without extensive downtime
  • New Query Monitoring Tools, giving administrators more insight into query activity
  • Greatly Reduced VACUUM Overhead through the Visibility Map
  • New Monitoring Tools for current queries, query load and deadlocks

Version 8.4 also makes data analysis easier through the advanced ANSI SQL2003 features of windowing functions, common table expressions and recursive queries. Enhancements to stored procedures, such as default parameters and variadic parameters, make database server programming simpler and more compact. Of course, there are also performance improvements included in this version.

For more about PosrtgreSQL 8.4, you may check release notes

363 questions
609
votes
12 answers

Generating a UUID in Postgres for Insert statement?

My question is rather simple. I'm aware of the concept of a UUID and I want to generate one to refer to each 'item' from a 'store' in my DB with. Seems reasonable right? The problem is the following line returns an error: honeydb=# insert into items…
fIwJlxSzApHEZIl
  • 11,861
  • 6
  • 62
  • 71
241
votes
5 answers

How to add "on delete cascade" constraints?

In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter? # \d scores Table "public.scores" Column | Type |…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
167
votes
9 answers

how to exclude null values in array_agg like in string_agg using postgres?

If I use array_agg to collect names, I get my names separated by commas, but in case there is a null value, that null is also taken as a name in the aggregate. For example : SELECT g.id, array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE…
Daud
  • 7,429
  • 18
  • 68
  • 115
124
votes
9 answers

Using psql to connect to PostgreSQL in SSL mode

I am trying to configure ssl certificate for PostgreSQL server. I have created a certificate file (server.crt) and key (server.key) in data directory and update the parameter SSL to "on" to enable secure connection. I just want only the server to be…
Lolly
  • 34,250
  • 42
  • 115
  • 150
100
votes
3 answers

Options to retrieve the current (on a moment of running query) sequence value

How is it possible to get the current sequence value in postgresql 8.4? Note: I need the value for the some sort of statistics, just retrieve and store. Nothing related to the concurrency and race conditions in case of manually incrementing it isn't…
zerkms
  • 249,484
  • 69
  • 436
  • 539
93
votes
7 answers

How do I get tables in postgres using psycopg2?

Can someone please explain how I can get the tables in the current database? I am using postgresql-8.4 psycopg2.
user1395784
  • 931
  • 1
  • 6
  • 3
89
votes
3 answers

Split comma separated column data into additional columns

I have comma separated data in a column: Column ------- a,b,c,d I want to split the comma separated data into multiple columns to get this output: Column1 Column2 Column3 Column4 ------- ------- ------- ------- a b c d…
Gallop
  • 1,365
  • 3
  • 16
  • 28
67
votes
1 answer

Iterating over integer[] in PL/pgSQL

I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this: declare a integer[] = array[1,2,3]; i bigint; begin for i in a loop raise notice "% ",i; end loop; return true; end In my actual…
Dipro Sen
  • 4,350
  • 13
  • 37
  • 50
66
votes
6 answers

Select today's (since midnight) timestamps only

I have a server with PostgreSQL 8.4 which is being rebooted every night at 01:00 (don't ask) and need to get a list of connected users (i.e. their timestamps are u.login > u.logout): SELECT u.login, u.id, u.first_name FROM pref_users u WHERE u.login…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
66
votes
3 answers

How to get a status of a running query in postgresql database

I have a select query running very long. How will I get a status of that query, like how long will it be running? Whether it is accessing a data from the tables or not. Note : As per pg_stat_activity the query state is shown as active and not in a…
Arun Padule
  • 681
  • 1
  • 6
  • 7
62
votes
19 answers

postgresql - can't create database - OperationalError: source database "template1" is being accessed by other users

I logged in to source database template1 and now I can't create database. When I try to create database, I get this error: OperationalError: source database "template1" is being accessed by other users DETAIL: There are 5 other session(s) using the…
Andrius
  • 19,658
  • 37
  • 143
  • 243
61
votes
5 answers

How to get the total number of tables in postgresql?

Is there any way by which I can get the total number of tables in a Postgresql database? The postgresql version I'm using is PostgreSQL 8.4.14.
harry
  • 1,410
  • 3
  • 12
  • 31
55
votes
5 answers

Hashing a String to a Numeric Value in PostgreSQL

I need to Convert Strings stored in my Database to a Numeric value. Result can be Integer (preferred) or Bigint. This conversion is to be done at Database side in a PL/pgSQL function. Can someone please point me to some algorithm or any API's that…
Salman A. Kagzi
  • 3,833
  • 13
  • 45
  • 64
54
votes
5 answers

currval has not yet been defined this session, how to get multi-session sequences?

My objective is to get a primary key field automatically inserted when inserting new row in the table. How to get a sequence going from session to session in PostgreSQL? doubleemploi@hanbei:/home/yves$ psql -d test Mot de passe : psql…
MUY Belgium
  • 2,330
  • 4
  • 30
  • 46
27
votes
1 answer

How to save results of postgresql to csv/excel file using psycopg2?

I use driving_distance in postgresql to find distances between all nodes, and here's my python script in pyscripter, import sys #set up psycopg2 environment import psycopg2 #driving_distance module query = """ select * from…
Heinz
  • 2,415
  • 6
  • 26
  • 34
1
2 3
…
24 25