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)

3
votes
1answer
475 views

Solution for deploying multiple instances of Django Server with Database replication

have written a Django application that reads / writes data into a postgres database, at the moment I have been deploying one of these devices per client. I am faced with having to deploy multiple ...
10
votes
1answer
988 views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
23
votes
3answers
19k views

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...
7
votes
1answer
539 views

Concept of Schema in PostgreSQL

I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design. Why should I use it? Can it affect me in future if I decide to not think ...
2
votes
1answer
293 views

pgpool connect failed: No such file /tmp.s.PGSQL.0 replication mode = true

Setup: I have two instances of Postgres 9.0.3 and pg_pool 3.14 all running on the same Ibuntu (10.04) machine and am attempting to evaluate pgpool. I can attach to pgpool using psql -p <pgpool ...
7
votes
4answers
782 views

Should I invest the time to change the column type from CHAR(36) to UUID?

I have a few million rows in my database already. I didn't know about the PostgreSQL UUID data type when I designed my schema. One of the tables has 16M rows (about 3.5M to 4 M records per shard), ...
1
vote
1answer
245 views

How to generate PostgreSQL COPY bin files?

My Googlefu is failing me. Are there any code snippets, either in Python or FORTRAN, that shows how to create binary files to be used by the COPY command in PostgreSQL?
4
votes
2answers
597 views

Postgresql 9.0 adding new database to streaming replication

postgresql 9.0 streaming replication + hot standby is running. Now i need to add one more database to current cluster. What is the best way of doing this? I have a backup file about 20GB. should i ...
5
votes
2answers
2k views

About “Transaction ID Wraparound”

Now , I read the document about "Transaction ID Wraparound " , but there are something that I really don't understand, the document is the following url ...
8
votes
2answers
3k views

Load balancer versus connection pooling — is there a difference?

I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is ...
4
votes
3answers
6k views

How to change the datatype of a column from integer to money?

I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error: cannot cast type MyColumn to money I have tried these two statements, but just haven't ...
3
votes
2answers
804 views

PostGreSQL 8.3 - issues with autovacuum

I posted this on StackOverflow and it was suggested this query was better suited here. I'm trying to encourage the use and monitoring of autovacuum in some PostgreSQL 8.3 databases. One objection I ...
4
votes
2answers
6k views

How do I detach all other users from a postgres database?

I need exclusive access to a database. Is it possible using an SQL command to "detach" all other users from a postgres database. Or maybe closing all other connections and then gaining exclusive ...
2
votes
1answer
286 views

Postgres planer JOIN removal

I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following: ...
23
votes
5answers
6k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
2
votes
1answer
730 views

Does the pgpool-II extension allow for parallel execution of any query?

I read about pgpool-II here and it states that: Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently ...
5
votes
2answers
1k views

Single data type for imprecise date values, as allowed by ISO 8601

How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values? ISO 8601 allows date values with reduced precision. ‘1964’, ...
1
vote
1answer
929 views

About “pg_ctl promote” of PostgreSQL9.1

Today I test the new feature about "replication features" of PostgreSQL9.1, First I have installed the Hot-standby of PostgreSQL9.1 successfully. It is not a problem。 What I want to test is that I ...
4
votes
2answers
251 views

What is the consensus on using NOT NULL constraints on all text-based fields in a table?

Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying. I'm curious what the feelings are with using something like NOT NULL ...
4
votes
3answers
5k views

How can I insert if key not exist with PostgreSQL?

I have a table: CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY) Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the ...
2
votes
2answers
2k views

Understanding time format of the EXPLAIN command - Postgres

When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals ...
6
votes
3answers
426 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
1
vote
2answers
2k views

How to find the fitting version of pgadmin III?

If you have different versions of PostgreSQL database servers you need a pgadmin III version with the respective pg_dump and pg_restore included. I am running pgadmin III on a windows machine. For ...
4
votes
2answers
837 views

Creating a GroupBy query to include a result for when no results match

Let's say I need a query to see sales results by Month and I want the result-set to include results for each month whether there were any sales in that month or not. And let's say my table "sales" ...
2
votes
2answers
367 views

What does “GIN doesn't support full index scans” mean?

I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be ...
2
votes
2answers
357 views

Database Transfer from PostgreSQL to MySQL

Is there any method to tranfer the data and schema from a postgresql database to mysql?
3
votes
1answer
4k views

How can I generate scripts for all functions in pgAdmin?

I am using pgAdmin version 1.10.5, and I've checked the properties and menu options but couldn't find anything on the subject. For example, I just want to generate a script that includes the ...
1
vote
2answers
1k views

Is the mapping between PostgreSQL system tables and information_schema views documented?

If you connect pgadminIII to a PostgreSQL 9.x server, you'll find two catalogs exposed in the user interface: ANSI (information schema), and PostgreSQL (pg_catalog). I'm looking for documentation ...
2
votes
1answer
605 views

What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?

We're using PostgreSQL v8.2.3. Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we don't use other features of pgpool like Replication, Load Balancing, ...
4
votes
2answers
7k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
3
votes
1answer
5k views

PostgreSQL: Change log file location

How can I change the location of the log file in PostgreSQL? Can it be done without stopping the cluster?
1
vote
2answers
214 views

Postgresql Cost-Based Vacuum Delay parameters

How to set the these parameters on postgresql.conf ? What I should consider while setting them? What should I monitor? #vacuum_cost_delay = 0ms #vacuum_cost_page_hit = 1 ...
2
votes
1answer
497 views

What are the pro's and con's of using PostgreSQL vs MySQL for binary data?

I read that MySQL has binary safe blobs, but PostgreSQL does not. So far what I've found suggests that "binary safe" is something that really relates to functions and handling of data, not the data ...
7
votes
3answers
3k views

Is there a way to access temporary tables of other sessions in postgres?

I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis ...
5
votes
2answers
1k views

PostgreSQL: Last access time to table

I am in charge of a large PostgreSQL database, with few dozen tables. I suspect that many of these tables are never accessed. What's the best way to check when was the last time a certain table was ...
6
votes
4answers
2k views

Determine when a PostgreSQL database was last changed

I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed? Instead of using pg_dumpall, I'd ...
1
vote
3answers
198 views

PostgreSQL: 8.3/8.4 interface chages?

I would like to upgrade a PostgreSQL 8.3 server to PostrgreSQL 8.4. There are many software components that interact with the server over the network, and I am concerned with compatibility issues. Is ...
7
votes
3answers
16k views

How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?

I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I'm using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the ...
4
votes
1answer
1k views

PostgreSQL: Corrupt primary key, inconsistent table

While recovering from a cloud failure, I found that some tables on a PostgreSQL database are behaving strangely. These tables are indexed using a primary key, but a pg_dump yielded duplicate fields, ...
3
votes
2answers
2k views

PostgreSQL: Using schema and table names from other tables?

Consider the situation where I have all my schema names in one table and all my table names in another table. Is it possible to do something like in the following? SELECT value FROM (SELECT ...
2
votes
2answers
134 views

Get a count of shippable orders

I have two tables: orders and order_lines. I need a fast, inexpensive way to display a count for orders that are going to be shipping today. The order_lines table is associated with the orders table ...
9
votes
2answers
4k views

How do I create an index to speed up an aggregate LIKE query on an expression in postgres 8.4?

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 ...
4
votes
2answers
1k views

Estimate average and median efficiently in Postgres?

I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which ...
2
votes
2answers
616 views

PostgreSQL: Separate tables vs single table to perserve disk space?

I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data ...
7
votes
2answers
18k views

List the database privileges using psql

I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL ...
4
votes
2answers
636 views

Index Generation in Postgres

We have a database with a single table made up of around 700 million entries. We update the database by adding new entries on a server then transfer the server to the production server using pg_dump: ...
4
votes
1answer
554 views

Is the PostgreSQL 'connection limit' shared across all users in a role?

I have created a role that many users can share: > create role foo_read_only connection limit 5; I have then assigned permissions to that role: > grant select on table1 to foo_read_only; ...
3
votes
3answers
579 views

ts_rank massively slows my query, how can I improve performance?

Here's the table definition Table "public.kb_article_contents" Column | Type | Modifiers ------------+----------+----------- article_id | smallint | not null contents | text | not ...
3
votes
1answer
6k views

psql denies access to file when using COPY FROM

When I do this: COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV; On this: -rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv psql tells me this: ERROR: could not open ...
2
votes
3answers
918 views

MySQL and PostgreSQL Licence issue

Our Company wants to use MySQL for one software product. This product is not open-source. Is it legal to use it freely ? If not, how about PostgreSQL ? Is this okay for our purpose ?