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)

1
vote
1answer
419 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
2answers
1k 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
97 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
411 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
232 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 ...
2
votes
1answer
2k 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
277 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
140 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
5k 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
2k 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 ...
2
votes
1answer
854 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
514 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
406 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
187 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
162 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
408 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
437 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 ...
9
votes
2answers
10k 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
189 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
2k 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
787 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
140 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
72 views

Postgres Automated Install

Is it possible to automatically create a database after a postgres one click install (from Enterprise DB)?
6
votes
1answer
605 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
580 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
401 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
641 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
694 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
87 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
53 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
996 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
876 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
467 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
277 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 ...
2
votes
3answers
2k views

How can I store a pdf in PostgreSQL

I have to store .pdf files in a table. I have a table, state, with columns: id_state, name, pdffile (bytea) I want to store the pdf files in the pdffile column. How can I do this?
1
vote
0answers
186 views

Does spgist extension in PostgreSQL 9.2.3 support float or int array?

SP-GiST is an abbreviation for space-partitioned GiST in PostgreSQL 9.2.3. It can be used to find the similar tuple by creating the sp-gist index on the column. e.g: create index pt_spgist_idx on ...
1
vote
1answer
59 views

Script to get duration

I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
1
vote
1answer
680 views

Postgres copy data with \xYY as plain string instead of interpreting as encoded string

I have a log file full of URLs, generated by Bro IDS. When Bro logs a URL with non-ascii characters in it, it inserts \xYY where YY is the hexadecimal character code. Also, some URLs contain "\x". ...
1
vote
1answer
316 views

How to Reorganize Microsoft Access Table

I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of ...
5
votes
1answer
585 views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
6
votes
3answers
5k views

PostgreSQL Sequential Scan instead of Index Scan Why?

Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
3
votes
3answers
819 views

What is the best way to store X509 certificate in PostgreSQL database?

I'm working on web authenticate system where users will digitally sign random tokens and this will be checked with X509 certificates stored on the server. Therefore I have to store several X509 ...
0
votes
1answer
328 views

Postgresql Run 'Jobs' At Specific Times

I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute . Scenario : PostgreSQL 9.2 Need To Run A ...
4
votes
2answers
5k views

How should I tune Postgresql for 20 GB of RAM?

I've been fortunate enough to have the use of a 20GB Linode instance running Ubuntu 64 bit. I want to try to optimize PostGres for this service, but I don't know what I should prioritize changing. I ...
0
votes
1answer
429 views

UPPER Case issue postgreSQL when importing from text file

I have ruby and rails app, and I have cron task to download text file and import it to the database using \copy hotels FROM 'db/ActivePropertyList.txt' DELIMITER '|' There is a header field which ...
1
vote
1answer
893 views

PostgreSQL no relations found

I am running ruby and rails app with postgresql database, and I want to do batch import from text file to the database. I've configured the database and if I run psql and \l to list all databases, I ...
0
votes
3answers
7k views

Export Postgres table on remote server to CSV file on local machine?

I have access to DB on remote server where I can do only select command, So, I can execute: COPY products TO '/tmp/products.csv' DELIMITER ','; But on that server I don't have permission to ...