Tagged Questions

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)

9
votes
2answers
2k views

Force PostgreSQL clients to use SSL?

I have configured ssl = on in postgresql.conf (and installed a certificate etcetera). Does this ensure that all clients will always connect over SSL? (I.e. does ssl = on it make it impossible to ...
2
votes
2answers
723 views

Role problem of PostgreSQL

I want to create a role named cp with some defined privileges, then we will create some other roles which will be granted with cp role. I know Oracle can do this job. For examle grant resources to ...
2
votes
2answers
1k views

Reference a non primary key in another table in postgresql

I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.
2
votes
1answer
326 views

How to perform asynchronous cascade slave replication of PostgreSQL database?

This is a feasibility/architectural question (I am no database administrator). Let's imagine a PostgreSQL database running online (like those from Heroku for example). One would like to maintain a ...
1
vote
1answer
672 views

PostgreSQL 8.4 hangs on create table, reindex

I have a rather small database(200Mb) which was running on ubuntu 10.10/amazon ec2 fine for a year almost but today I ran into the following problem: When I create a table that references other table ...
2
votes
3answers
387 views

Can I limit access to postgresql 8.4 databases via ODBC on the server side?

I have a bit of a tricky problem at the moment that I could really use some help to solve. I'll do my best to explain it but please feel free to ask questions :-) We have a postgresql 8.4 database ...
6
votes
3answers
8k views

SQL to read XML from file into PostgreSQL database

How can I write SQL to read an XML file into a PostgreSQL XML value? PostgreSQL has a native XML data type with the XMLPARSE function to parse a text string to that type. It also has ways to read ...
18
votes
4answers
6k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
3
votes
1answer
2k views

postgres and passwordless authentication

We have users for out postgres database and none have passwords... Do not know the history behind it. When I try to use clients to connect to these databases, for instance, pgadmin III, the ...
7
votes
1answer
85 views

How can I see a query after rules have been applied?

From docs - 37.3.1.1. "A First Rule Step by Step" CREATE TABLE shoelace_log ( sl_name text, -- shoelace changed sl_avail integer, -- new available value log_who ...
5
votes
1answer
1k views

Load Balancing / MPP with Postgres 9.1

I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes). Since I'm ...
6
votes
2answers
2k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
2
votes
1answer
403 views

PostgreSQL archive_command on Windows

What is the best method for WAL log transfer on Windows? I have two servers with Windows Server 2008, PostgreSQL 9.0 64bit. Servers do not have any shared SAN disk, they are on different hosts, ...
7
votes
2answers
18k views

PgAdmin III - How to connect to database when password is empty?

I have installed PostgreSQL 9.1 on my PC (Win 7). I have a small Java application connecting successfully to it with login=sa and password="". The connection works. However, it is refused from ...
5
votes
1answer
421 views

Gaining visibility into long running INSERT INTO … SELECT

I have a large SQL insert statement that updates a summary table. The process normally runs hourly, and takes about 5 to 10 minutes to calculate uniques over that period. I "foolishly" chose to run it ...
2
votes
1answer
6k views

Postgres client copy (\copy) command doesn't have access to a temporary table?

I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a ...
3
votes
3answers
2k views

Postgres Server access restrict to only 1 database from list of databases

I am trying on the server side to restrict a user connecting on LAN to be able to see and access only 1 database from the list of databases I have in my system. How can I achieve this, I have tried ...
1
vote
0answers
256 views

unable to run multiple create index scripts in pgadmin query tool [closed]

I have created CREATE INDEX scripts using the indexdef column of pg_indexes in one database and tried to run the script on another database. When each line of the script is executed separately it ...
3
votes
1answer
645 views

PostgreSQL pl/perl trigger, differentiate null vs empty

I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back. On update, it generates a list of columns in the table being updated and inserts rows in the ...
8
votes
4answers
5k views

PostgreSQL designing tool

I am about to design a database which is going to run with PostgreSQL. I am used to the magnificent tool called MySQL Workbench for MySQL database. It is useful and it looks good which I kind of ...
10
votes
3answers
4k views

Should I use SQL JOIN or IN Clause?

I have a question about best approach. I am not sure which approach is best when data is considered variable in size. Consider the following 3 TABLES: EMPLOYEE EMPLOYEE_ID, EMP_NAME PROJECT ...
3
votes
2answers
551 views

Is there any index with O(1) complexity for lookup in PostgreSQL?

Is there any index type in PostgreSQL that gives O(1) complexity for lookups? on strings?
1
vote
1answer
660 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 ...
2
votes
1answer
733 views

Unicode support of PostgreSQL and its performance

How is the Unicode support of PostgreSQL? If I want to save Unicode data does it have to go through the conversion process while storing and retrieving? If yes then what is the performance of such ...
3
votes
1answer
524 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
1k 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
22k 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
580 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
315 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
894 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
255 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
623 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
3k 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
833 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 ...
5
votes
2answers
7k 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
296 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: ...
25
votes
5answers
7k 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
785 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 ...
6
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
1k 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
258 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
6k 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
461 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
889 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
376 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
360 views

Database Transfer from PostgreSQL to MySQL

Is there any method to tranfer the data and schema from a postgresql database to mysql?