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
2answers
750 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
5k 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
265 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: ...
20
votes
5answers
5k 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
634 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
967 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
802 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
240 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
4k 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
1k 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
390 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 ...
3
votes
2answers
727 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
325 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
342 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
3k 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
539 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, ...
3
votes
2answers
5k 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 ...
2
votes
1answer
4k 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
186 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
437 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 ...
6
votes
3answers
2k 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 ...
4
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
1k 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
189 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 ...
6
votes
2answers
12k 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
914 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
1k 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
130 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 ...
7
votes
2answers
3k 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
564 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 ...
5
votes
2answers
13k 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
558 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
501 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
508 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
678 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 ?
4
votes
1answer
448 views

PostgreSQL exit status when running a file

When running PostgreSQL with a single SQL command error codes are returned as expected: % psql -c "SELECT * FROM AWDASDASDASDAS" my_db ERROR: relation "awdasdasdasdas" does not exist LINE 1: SELECT ...
9
votes
1answer
3k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
2
votes
2answers
122 views

Database Agnostic Conditional

I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out ...
2
votes
2answers
5k views

Which business intelligence tool is better for use with a PostgreSQL database?

I'm trying to find a good BI tool which can be connected to a PostgreSQL database. I need the following abilities: Reporting Services Dashboards OLAP Data Mining. The BI server should work on ...
10
votes
1answer
1k views

How does PostgreSQL handles Checkpoints in the middle of a WAL-enabled backup?

On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes). Now I add a binary packup of the PG_DATA ...
1
vote
1answer
10k views

How can I convert a string to a double precision in PostgreSQL?

How can I convert a string to a double precision in PostgreSQL ? I tried something like : update points set latitude2 = cast(latitude as double) ; where latitude is a string and latitude2 is a ...
10
votes
3answers
3k views

How should I represent a latitude and longitude in Postgres without using PostGIS?

How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.
2
votes
2answers
589 views

Effective backup and archive strategy for database and linked files

I am using Postgres to store a variety of application data for a webapp. Part of the application involves storing and retrieving user uploaded files. I am storing the files in the filesystem with ...
5
votes
3answers
2k views

Postgresql row-level encryption

Looking over the docs, I didn't see any mention of row-level encryption. Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with ...
4
votes
3answers
2k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
7
votes
1answer
1k views

PostgreSQL: Disk space not released after TRUNCATE

I haveTRUNCATEd a huge (~120Gb) table called files: TRUNCATE files; VACUUM FULL files; The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space? UPDATE: The ...