PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

2
votes
1answer
275 views

How do I resolve Postgresql error, “could not determine which collation to use for string comparison”?

When ever I run this SQL script I get: ERROR: could not determine which collation to use for string comparison This is what is on line 34: CREATE TEMPORARY TABLE weight_options AS SELECT ...
2
votes
1answer
496 views

Dropping indexes doesn't seem to release space to the OS. Can it?

I'm running PostgreSQL 9.1. I have noticed really alarmingly bad indexes are taking up a lot of space on my server, e.g: evlampts=# SELECT pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS ...
1
vote
1answer
41 views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
1
vote
1answer
72 views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
1
vote
2answers
59 views

Should I create an index for non key columns?

I have a table in a PostGreSql database defined as following: CREATE TABLE public."MATCH"( "ITEM_A_ID" bigint DEFAULT 0, "ITEM_B_ID" bigint DEFAULT 0, "OWNER_A_ID" bigint DEFAULT 0, ...
1
vote
1answer
91 views

How do I access a old saved tablespace after reinstalling postgres?

So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to ...
1
vote
1answer
201 views

How to do a minor upgrade postgresql 9.3.0 windows to 9.3.1?

What is the recommended way to perform an upgrade from PostgreSQL 9.3.0 to 9.3.1 (minor upgrade) using the Enterprise DB built windows installer? Should I uninstall first or just install over the ...
1
vote
2answers
167 views

Get time format in PostgreSQL

If I want get Ubuntu system time, I have two options: $ cat /etc/timezone US/Eastern $ date Sun Sep 15 14:45:02 EDT 2013 How can I find out if PostgreSQL is using utc or a utc offset, such as ...
1
vote
2answers
183 views

Vacuum vs Auto Vacuum

I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer. 1) Out of vacuum or Auto vacuum which is ...
1
vote
0answers
320 views

Postgres 9.1.6 Error index contains unexpected zero page at block 0

I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine. When I try to run a query on the replica DB I get the error below: ERROR: index ...
1
vote
1answer
294 views

Moving postgresql data directory

I moved postgresql's data directory by following the following steps: Stop postgres cp -a source_data_directory destination_data_directory export PGDATA=destination_data_directory Changing data ...
1
vote
1answer
433 views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...
1
vote
1answer
347 views

Create database on new partition

I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package. I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be ...
1
vote
1answer
293 views

Use GIN to index bit strings

I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
1
vote
2answers
106 views

Difficult categorising of rows

First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. ...
1
vote
0answers
123 views

pg_dump dumps casts after views using them [closed]

My small but annoying problem is that the regular backups of a database of ours always list the user-defined casts after those views that would make use of them. For this reason I have to maintain a ...
1
vote
2answers
344 views

Tables accessed during last periods in postgresql

I want to check that which tables are updated during the last period or say in descending order of accesstime of tables, then how can I view that for postgresql ?
1
vote
1answer
193 views

Show all variations of 2 characters in postgres

How would I do this in postgres : Lets say I have an x and a y and I want to show all possible combinations : xx yy xy yx How can I make postgres do this for me ?
1
vote
1answer
571 views

storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there?

I have a email inbox that will soon hit 4 million messages, I have an app that runs different policies over the inbox to associate emails with each other much like the conversation plugin for ...
1
vote
1answer
2k views

How can I configure PostgreSQL 9.0 to start automatically under Windows XP?

Yesterday I installed PostgreSQL 9.0 on Windows XP, and after the installation the DB was running. After booting the system this morning, the data base server did not start automatically. How do I ...
0
votes
1answer
28 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
0
votes
1answer
53 views

Postgres EXPLAIN ANALYZE behavior

I want to benchmark performance of a particular complicated query JOIN for various values of primary key ID. Most previous approaches say to use EXPLAIN ANALYZE for the query in question and get the ...
0
votes
3answers
61 views

Postgresql: Make Column Default a Query

Is there a way I can set up a column in PostGreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
0
votes
1answer
89 views

PG: permission denied for relation <table>

I have ran the following SQL: CREATE USER bspu LOGIN; CREATE DATABASE bsp OWNER bspu; GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu; \c bsp CREATE TABLE users ( id SERIAL PRIMARY KEY, ...
0
votes
2answers
105 views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...
0
votes
0answers
91 views

Can't connect to service after updating ODBC driver

I am upgrading a VB6 program at work and one of the changes is that it now uses PostgreSQL 9.2.4 instead of 8. I was getting a 'client encoding mismatch' error, so I updated the ODBC driver, and the ...
0
votes
0answers
689 views

Cannot run pgAdmin from the terminal: No such file libwx_gtk2u

(Probably irrelevant) I'm using Puppet to setup/manage a class of computers that are equipped with PostgreSQL and PostGIS, and this implies that each computer goes through an unattended install: ...
0
votes
1answer
110 views

“Truncate” or “Delete/Vacuum Full” for deleting some of the table rows

I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided ...
0
votes
2answers
412 views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...