PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

6
votes
2answers
4k views

Can I have one table be an “alias”, or “symlink”, to another?

I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables. After a discussion with a colleague about the potential to ...
2
votes
0answers
36 views

Splitting a wide table [duplicate]

Possible Duplicate: Database Design: New Table versus New Columns I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important ...
12
votes
3answers
1k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their ...
3
votes
1answer
338 views

PostgreSQL memory spike upgrade from 8.2 to 9.1

We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers. Everything seems well with the restore of the databases, ...
5
votes
1answer
554 views

Upgrade Postgres without doing a pg_dumpall

I'm upgrading from postgres 8.4 to 9.0 and looking at the manual it looks like this is my only choice: pg_dumpall -p 5432 | psql -d postgres -p 6543 Which means I'll need to stop any writes to the ...
2
votes
1answer
187 views

How do I resolve Postgresql error, “PL/Perl function must return reference to hash or array”?

The follow code used to work in Postgresql 8.4.11 with perl v5.10.1: =# select * From testfunction(); testfunction ------------------------ http://www.google.com/ However, after doing ...
3
votes
1answer
2k views

Pgpool, Postgresql and Apache tuning (1000 concurrent spatial queries) [closed]

I'm trying to configure a load balancing system. I've a python script, invoked through mod_wsgi on Apache, that generates a query and executes it on pgpool: request-> wsgi python -> pgpool -> ...
5
votes
1answer
738 views

Difference between idx_tup_read and idx_tup_fetch on Postgres

On Postgres 8.4 when you do: select * from pg_stat_all_indexes where relname = 'table_name'; It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?
0
votes
2answers
2k views

How to convert SQL Server .MDF database to Mac OS PostgreSQL database on Mac OS X?

I need to import a SQL Server database which is in the .mdf format into a PostgreSQL database. My system is Mac OS X. How to do it in the easiest way?
11
votes
3answers
8k views

Created user can access all databases in PostgreSQL without any grants

I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access ...
1
vote
1answer
331 views

Partial vacuum and whole table vacuum

From postgresql document, I know vacuum can skip pages that are already marked in visibility map, so vacuum progress only vacuum pages which requires vacuum,and we call this partival vacuum; ...
1
vote
1answer
596 views

Behavior of PostgreSQL COPY FROM

Let me preface this question by stating that my experience with DBA is limited to an introductory undergraduate university course, and that I'm just getting started with PostgreSQL. I would like to ...
5
votes
1answer
506 views

Is there a repository for pgadmin plugins? [closed]

I've been using PostgreSQL with PGAdmin III for a while now and it's been bugging me that there is a plugins menu option that is empty. I've Googled some and found a plugin here and there but I wanted ...
8
votes
1answer
14k views

How to get a working and complete PostgreSQL DB backup and test

I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users. From what I've read, and it ...
5
votes
5answers
2k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
13
votes
1answer
570 views

Why is there a PostgreSQL Turtle?

I was just checking out the propaganda page for PostgreSQL and I found this little piece of art: It is the "Turtle Style" PostgreSQL logo. How old is this, and did PostgreSQL ever officially use a ...
1
vote
4answers
558 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
4
votes
1answer
358 views

How do I resolve Postgresql error, 'no collation was derived for column “foo” with collatable type citext'?

Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error: ERROR: no collation was derived for column "vin" with collatable type citext ...
2
votes
1answer
387 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 ...
1
vote
0answers
161 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 ...
3
votes
2answers
1k views

Why is pg_dumpall throwing an "OID does not exist'' error?

When I run pg_dumpall I'm now getting this: $ pg_dumpall -f may2012 pg_dump: schema with OID 7549789 does not exist pg_dumpall: pg_dump failed on database "dealermade", exiting How do I resolve it? ...
1
vote
1answer
221 views

How to know there is a maintenance job running on a postgresql database

I have a python script on postgresql database which is fetching records from sql server database and inserting it to postgresql DB after some filtering. this jobs is running by cron every two minutes. ...
1
vote
2answers
6k views

Recover from Postgres FATAL: could not open file pg_tblspc/

I have a pretty big PostgreSQL 9.1.3 running on Ubuntu 10.04. The data is spread over multiple tablespaces = physical drives. One of these drives is gone, so the directory of that tablespace doesn't ...
1
vote
1answer
154 views

Postgres table size doubles are transaction completes

I have a table with 50 million rows and 40+ text columns. Size of the table is around 17GB. Inside a transaction, I add another integer column to the table and update all the rows with corresponding ...
1
vote
1answer
489 views

PostgreSQL vs MySQL benchmarks when a 2nd level cache is included

Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by ...
12
votes
1answer
2k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...
3
votes
1answer
385 views

Oracle to PostgreSQL

I'm having som test databases running Oracle 10 & 11 that I plan to try to migrate to PostgreSQL. Some googling hinted that the perl script "ora2pg" could be of use, and free of charge. However, ...
1
vote
1answer
364 views

PostgreSQL schemas, select same table from all schemas

My app is used by about 80k users every month. I want to create separate PostgreSQL schemas for each user (a-la SaaS, for protection). My app allows users to search other user's profiles, via: ...
5
votes
5answers
4k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
2
votes
3answers
5k views

How to convert PostgreSQL to MySQL

How can I convert database from PostgreSQL to MySQL? I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error: ERROR ...
2
votes
2answers
2k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
18
votes
3answers
7k views

Is the CPU performance relevant for a database server?

This is a purely theoretical question. Let's say I have an application deployed on multiple servers. A load balancer, Multiple/scalable applications servers A (single) database server (for the ...
7
votes
1answer
3k views

Fast general method to calculate percentiles

I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile. An obvious solution is to count and sort the column and then do a ...
2
votes
1answer
154 views

Is it possible to sync a Pervasive SQL database to a PostgreSQL database, and vice-versa?

I was wondering if it is possible to sync a Pervasive SQL database to a PostgreSQL database. If I set this up I would need the ability to sync both ways either on command or via a schedule. Does ...
5
votes
1answer
4k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
0
votes
1answer
5k views

How to export a table on postgresql command line

Anyone can do tell me how to do that? (I know maybe is a dumb question but I'm trying to learn how to use command line of postgresql) ~$ psql mybase psql (9.1.3) Type "help" for help. and then ...
9
votes
2answers
11k views

How to view data in pgAdmin III

I can't believe they make it this hard. I am at a loss about how to view the data in my database. Is there an easy way to see what data is in my tables with pgAdmin III? Alternatively, is there a ...
-1
votes
1answer
320 views

Important distinctions between DBMSs [closed]

DBA is a career I'm strongly considering after school, but my limited experience mostly involves some PL/SQL Oracle web programming, some basic PHP/MySQL web pages, and some queries in SQL Server. ...
3
votes
1answer
1k views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
2
votes
1answer
664 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 ...
7
votes
2answers
11k views

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under ...
1
vote
0answers
121 views

PostGIS - Effects of polygons with large number of verticies [closed]

I have a large table that stores, among other attributes, a geometry type. I have a GIST index on this attribute. However, when I perform even the most basic queries [say, select count(*) from table ...
5
votes
2answers
2k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
0
votes
1answer
140 views

Postgres Client - save table sorting

Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. ...
4
votes
1answer
1k views

Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL

I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id). Clients compute new intervals for one variable and store ...
4
votes
3answers
2k views

PostgreSQL - Help Optimizing SQL performance - full text search

I have a big table ~15M records called entities. I want to find top 5 entities matching "hockey" in their name. I have a Full text index built for that, which is used : ...
25
votes
1answer
3k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
4
votes
3answers
1k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
1
vote
1answer
2k views

pg_restore: [archiver] unsupported version (1.11) in file header postgresql

My server is working on Ubuntu with PostgreSQL 8.4 and the development machine I'm using has Debian Lenny with PostgreSQL 8.3. My machine can't be upgraded to PostgreSQL 8.4 and I need to restore a ...
4
votes
3answers
12k views

How to drop all connections to a specific database in postgres without stopping the server?

I want to drop all connections (sessions) that are currently opened to a specific postgresql database but without restarting the server or disconnecting connections to other databases. How can I so ...