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)

5
votes
2answers
604 views

Custom unique column constraint, only enforced if one column has a specific value

Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is "true", then ...
0
votes
1answer
42 views

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth. For example: create or replace function ...
3
votes
1answer
1k views

Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost. ie: CREATE OR REPLACE FUNCTION my_function() RETURNS setof record AS $BODY$ -- ...
2
votes
1answer
742 views

PostgreSQL CREATE EXTENSION file not found error on Mac OS

On PostgreSQL 9.2.3 when trying this: CREATE EXTENSION btree_gist; I get this error: ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": ...
1
vote
1answer
160 views

Postgresql query speed

This is my first time seriously attempting to get into the guts of the speed of a query on Postgres, and I was hoping for some more explanation of exactly what is going on. I am using Postgres v 9.1 ...
8
votes
1answer
3k views

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); ...
0
votes
1answer
933 views

Cannot `create function` in plpython3u, permission denied

As postgres user, I have create extension plpython3u; in my database then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true but ...
1
vote
0answers
2k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
2
votes
2answers
1k views

postgres backup / restore: restored database much smaller?

I am worried that my restored database is very different from the original: #check size of postgres database postgres@db1:/tmp$ psql -c "select pg_size_pretty(pg_database_size('test_db'));" ...
1
vote
1answer
500 views

Cannot use python3 as stored procedure language in posgresql database

I want to have python3 in my postgresql database for writing stored procedures. Being in the psql client, when I enter the command create extension plpython3u I get the error: couldn't open ...
3
votes
1answer
71 views

Does avg_query for PgBouncer include update and inserts?

Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage: avg_query Average query duration in ...
2
votes
2answers
2k views

Recursive query using plpgsql

I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure. I have a data table and a table to link the data together: DATATABLE ----------- id ...
3
votes
2answers
505 views

pessimistic locking and client death

We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
1
vote
1answer
356 views

Log statements from within triggers in Postgresql

I have several sql statements running in some of my plpgsql triggers. I've enabled logging with the log_statement = 'all'. It logs the 'triggering statement', but not the statements within the query ...
4
votes
1answer
877 views

Will table inheritance in PostgreSql suit this situation?

Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL. So my main aim is to have a Main schema in this DB, which I am going to use to ...
3
votes
1answer
91 views

PostgresSQL: Get single attribute of UDT in SELECT statement

I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work. Creation of type ...
0
votes
1answer
380 views

A stored procedure to bulk load in Postgres or Oracle given a URL [closed]

Is there a stored procedure available in Postgres or Oracle that does a bulk load given a URL, as in: url_bulkload csvFile.ctl Where csvFile.ctl is # # csvFile.ctl -- Control file to load CSV ...
3
votes
1answer
225 views

Avoiding repetition without creating a view

Suppose that I have a query Q1 and I need to run a query like the following: Q1 union select * from (some query that uses Q1 outcome) I would like to do that: Without creating any view Without ...
1
vote
1answer
1k views

Create log file only for my executed queries

I have been using the following to set up the log file: log_statement='all', log_collector='on', log_destination='on', log_filename='filename' After that, for testing I just executed a query ...
2
votes
1answer
254 views

How to Model / Quickly test for set membership in SQL?

Each row in a table needs to be protected a list of application specific roles lets say something that looks like this example below Desired Table Structure pkey | data | roles ...
1
vote
1answer
135 views

clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server

After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1. Below is the actual command: ...
3
votes
1answer
4k views

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
4
votes
1answer
1k views

PostgreSQL 9.2: vacuum returning disk space to operating system

Vacuum usually does not return disk space to operating system, except some special cases. From the docs: The standard form of VACUUM removes dead row versions in tables and indexes and marks the ...
1
vote
1answer
761 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 ...
0
votes
0answers
465 views

Create Log file for insert,update and delete records and retrive the log details

How to create/update log file for each modifications(insert,update,delete) in my database. i cant find any feasible methods to achieve so. can anybody know how to implement this.? if so then please ...
0
votes
1answer
319 views

Retrieving all PK and FK

I have a big database that I need to extract all primary keys and foreign keys from each table. I have pgAdmin III. Is there a way to do this automatically and not go over each table manually?
2
votes
3answers
1k views

get account activity in postgresql

I have a old DB with bunch of accounts. One of them is used for sure but for 5 of them or so there is no information. Is there any info in the system table in Postgresql when was the last login or ...
1
vote
4answers
175 views

How to determine if a postgres database needs to be vaccumed?

Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed? UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to ...
6
votes
1answer
160 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
1
vote
1answer
374 views

Postgresql: Difference between pg_dump and backup/restore?

Don't they essentially do the same thing by storing all the data in a file and dumping it into another database? Is it that pg_dump just adds the data on to whatever is already there and restore ...
8
votes
6answers
4k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
1
vote
0answers
414 views

SOLVED: Postgres 8.4.5: Bad query plan vs Good query plan when changing date range on large data table

SOLVED. I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000; Now the queries are running very fast, down to ...
8
votes
2answers
8k views

Permission denied in Postgres

I added the user myuserto Postgres. Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres. Then I tried to ...
4
votes
1answer
176 views

After Rackspace server creation, PostgreSQL query planner doesn't work as expected

We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
2
votes
3answers
1k views

Where are PostgreSQL databases stored on my computer?

So this might be a stupid question, but I'm really new to this. So I'm making a Postgres database on the postgres server, that I started up with this command: sudo -u postgres psql And then I did ...
5
votes
1answer
696 views

Does PgBouncer create a separate pool for every database config entry?

If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this. For example, ...
1
vote
0answers
135 views

postgresql 8.3 possible checkpoint and memory misconfiguration

I am using postgresql 8.3 in a read/write heavy application where data are being written to the database from a background process while they are also being read/written from a web application. At ...
1
vote
1answer
68 views

Postgres Automated Install

Is it possible to automatically create a database after a postgres one click install (from Enterprise DB)?
5
votes
1answer
558 views

How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?

Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling. What is the best pattern to use for this kind of set up? Should I have one-engine-per-process, ...
5
votes
2answers
527 views

How to properly configure PostgreSQL RAM usage?

Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
2
votes
0answers
379 views

How to properly configure pgBouncer transaction level pooling?

What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
2
votes
2answers
588 views

QGIS PostGIS Authentication Failure with “trust” option

I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, ...
1
vote
0answers
627 views

Remote connection to PostgresSQL in Windows 2008 Server is prompting the error below [closed]

The error: Unable to read data from the transport connection. an existing connection was forcibly closed by the remote host. The conditions: I have the following connection string: server = ...
4
votes
0answers
85 views

Postgres TupleTable like data structure

I'm writing my own Group by operator (non-hashed implementation) and I'm currently looking on a good data structure to store the result groups. There are two requirement in my mind: the table need to ...
3
votes
2answers
4k views

How do I remove duplicate records in a join table in PostgreSQL?

I have a table that has a schema like this: create_table "questions_tags", :id => false, :force => true do |t| t.integer "question_id" t.integer "tag_id" end ...
1
vote
0answers
51 views

After streaming replication has failed, how to get it back again?

I have a similar problem to this: Replication has failed; how to get going on again? Essentially my slave failed, and now complains "requested WAL segment 0000000100000135000000E4 has already been ...
2
votes
0answers
890 views

Is there a Postgres admin GUI that can execute a master file containing multiple sql files

I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file. example: ...
0
votes
0answers
811 views

Install PostgreSQL 9.2 on Windows using WIN1252 encoding.

I had installed PostgreSQL 9.2 earlier and it always installed with the encoding being WIN1252 (the default database was WIN1252). I then some time ago reinstalled it with the encoding being UTF8 (I ...
1
vote
2answers
416 views

Performing SELECT on EACH ROW in CTE or Nested QUERY?

This is a problem in PostgreSQL I have a table which stores the tree of users; +------+---------+ | id | parent | |------+---------| | 1 | 0 | |------|---------| ...
4
votes
3answers
253 views

how to chain postgres RULEs?

I have implemented data denormalization strategy using postgresql RULEs. I picked rules instead of triggers for performance reasons. Schema is structured like this: Application has many clients ...