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)

2
votes
1answer
658 views

Auto Increment Index Field For Create and Update

I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created ...
2
votes
2answers
343 views

Plpgsql seems to be deleting and inserting instead of updating - Why?

I am using postgreSQL 7.4. I have a large table , call it table_a: key1 INT NOT NULL, key2 INT NOT NULL, data INT NOT NULL, itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, ...
2
votes
2answers
501 views

How to hide metadata from Postgres users?

I need to provide views-only access to some subset of a database, for use by report-writing tools. It's easy enough to use permissions to prevent access to the data in the underlying tables, but I ...
2
votes
1answer
19 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
2
votes
1answer
29 views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
2
votes
1answer
50 views

Why write ahead logs in PostgreSQL are generated every second

PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute. Is it possible to make them occur less frequently? Is it possible to increase the WAL ...
2
votes
6answers
92 views

Efficiently storing irregular/repeating intervals (think calendar/events)

I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing. Right now ...
2
votes
1answer
64 views

How to reuse an update trigger for multiple tables in postgresql?

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table: CREATE TRIGGER ...
2
votes
1answer
38 views

Are TCP loadbalancers an alternative to Postgres 9.X read slaves load balancers like pgPool II or pgBouncer?

To scale read traffic on PostgreSQL streaming replication slaves, I want to be able to load-balance the requests. Postgres documentation suggests using tools like pgPool and pgBouncer, but I am ...
2
votes
2answers
54 views

pg_dump/pg_restore: different PostgreSQL version, another database name, another tablespace

I am going to move a database from the old server to a new one. the old one runs PostgreSQL 9.1, the new one has 9.3. I want to change the database name. I want the new database to be in a dedicated ...
2
votes
2answers
48 views

How can streaming replication be kept lagged by a certain 'delay' time?

Postgres has had streaming replication since 9.0 and since 9.1 it is possible to pause and resume the application of the streamed WAL records using pg_xlog_replay_pause() and pg_xlog_replay_resume(). ...
2
votes
1answer
46 views

How to create Select query function in the Postgresql?

I want create select function in postgres and get all data from table. I many example see and try but not perfact execute function. What perfact syntax for select query function create in postgres? ...
2
votes
1answer
28 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...
2
votes
2answers
153 views

Merging two backups form a postgresql database

I have a kind of big postgresql database and two backup files (each uncompressed dump file ~56 mb) representing 2 states of the data in time. 1st state - backup from a couple days ago 2nd state - ...
2
votes
2answers
696 views

save/export PGAdmin server list configuration settings

I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings
2
votes
2answers
66 views

Is this an example of an Enum (Enumerated Type)

I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm ...
2
votes
1answer
280 views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
2
votes
1answer
67 views

getting the output of delete statement in UDF in postgres

In Postgres in a user defined function I do execute format('delete from table where session_id = %L', id); ran without execute postgres (may) return(s) the following: DELETE 0 Is there any way I ...
2
votes
1answer
453 views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
2
votes
2answers
216 views

Equivalents to `ora_rowscn` in other databases?

Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, ...
2
votes
1answer
169 views

PostgreSQL maintenance

I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III. I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. ...
2
votes
2answers
148 views

Trigger update does not seem to finish

I'm new to PostgreSQL triggers & functions and such. I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update ...
2
votes
1answer
326 views

I need help understanding Postgres's archive cleanup functionality

I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform ...
2
votes
1answer
755 views

Postgresql out of memory while trying to a request of ~500mb

I started getting out-of-memory errors on os postgresql-9.1 server running on a x64 Debian with 4GB of RAM, dedicated to postgresql. Top returns something like this, which seems that the problem is ...
2
votes
1answer
221 views

pgAdmin to display multiple result sets

With MS SQL Query Browser I can run multiple queries at the same time and each result set will display in it's own window. Does PostgreSQL/pgAdmin have this functionality? Plugin perhaps?
2
votes
3answers
233 views

Restoring SQL_ASCII dumps to a UTF8-encoded database

I've got a Postgres 8.4 environment where the encoding on all our databases is set to SQL_ASCII - We're finally migrating to Postgres 9.2, and I would like to migrate everything over to UTF8 encoding. ...
2
votes
1answer
2k views

How to run recurring tasks on a Postgresql database without a cron-like tool?

I would like to call a stored procedure on a regular basis. On Oracle, I would create a job for this. I have found that Postgresql can mimic this well by using an external tool (cron etc) and PgAgent. ...
2
votes
2answers
64 views

Postgresql constrains on FK

I am trying to design a (part of a) database which has to accomplish the following: There is a students table, containing a bunch of students. There are educations in the database. Each student can ...
2
votes
1answer
183 views

Encryption in PostgreSQL

Does PostgreSQL support data encryption features at transactional and the table level of database? If yes, how? Oracle provides this via database and network encryption. Oracle provides encryption at ...
2
votes
1answer
131 views

How to get user-specific settings in PostgreSQL?

I have to collect some information of PostgreSQL roles. One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters ...
2
votes
1answer
522 views

PostgreSQL concurrent increment of counter

I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
2
votes
1answer
294 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": ...
2
votes
1answer
613 views

How to view the current settings of Autovacuum in Postgres?

I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration. Postgres 9.1 is the version I'm most interested in.
2
votes
2answers
172 views

String handling in postgres 8.4

This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist). I thought I ...
2
votes
1answer
105 views

Postgresql server causing a lot of IO activity

I have a postgresql server running on a small linode partition. It is running a web app for a site which gets on average 0 users / minute (it has not launched yet). At one point, I loaded up the ...
2
votes
1answer
164 views

INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

I am trying to write my first trigger. It is an insert trigger as follows: <b>Fatal error</b>: postgres7 error: [-1: ERROR: INSERT has more target columns than expressions at character ...
2
votes
1answer
867 views

Setting up a Postgresql Partition with foreign key

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed. I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them. ...
2
votes
2answers
129 views

What is the way to paginate last updated rows from multiple tables?

I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results. Row1 = Post's - text, username Row2 = Photo's - text, ...
2
votes
1answer
231 views

Optimal PostgreSQL setup for Windows 2008 R2

I have PostgreSQL 9.2 setup on Windows 2008 R2 64bit with 96 gigs of RAM and 8 cores. What would the optimal settings be for shared_buffers, effective_cache_size, work_mem, etc.? I realize that these ...
2
votes
1answer
162 views

Which database to use? [closed]

Hey there. I am looking for a free to use for commercial website database that should have these design features, in the given order: Auto-Sharding (getting more free space by adding more server ...
2
votes
1answer
590 views

In Postgres, how do I cast character varying to an enum type?

I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type. When trying to alter the table ALTER TABLE logs ALTER COLUMN interface_type ...
2
votes
1answer
1k views

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
2
votes
1answer
452 views

How to switch master server and warm standby (with streaming replication) in PostgreSQL?

I would like to know what is the way to switch the role of 2 servers when using warm standby? I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with ...
2
votes
1answer
467 views

Archive database on Postgres

I have a table with more than 60mio records, where every month I have to add about 2-4 milions. It's not insert only table, I need to read from it and update recently added rows (status changes, ...
2
votes
1answer
805 views

Violates foreign key constraint

I'm trying to implement the below represented UML and I can't figure out where is my mistake since I have already place a value into the table. -- Schema: public DROP SCHEMA IF EXISTS public ...
2
votes
1answer
188 views

PostgreSQL asynchronous replication (and possible data loss)

I'm trying to wrap my head around Postgres' asynchronous replication. The way I'm reading the documentation, if the master goes down after data was committed, but before it's shipped to slaves, the ...
2
votes
1answer
101 views

Does updating a partially indexed field add or remove it from an index in Postgres

We can create a partial index for a field in Postgres. I am looking at using this to stop rows which are archived from being in an index on a created_at field: First we create the index: test=# ...
2
votes
2answers
151 views

Which performance can I expect?

I'm running a Postgres 9.1 database on a Debian Squeeze server. It's a paravirtualized XEN guest system with 4G RAM and 1 CPU core, based on a i7-2600 host system with Linux software-RAID1. I'm ...
2
votes
1answer
353 views

How to stabilize performance on frequently updated table in PostgreSQL

I'm trying to find-out a way how to prevent performance degradation in my PostgreSQL database. The table is updated every minute with 100,000 - 200,000 INSERTs. Every 5 minutes the table is updated ...
2
votes
2answers
758 views

How to conditionally stop a psql script (based on a variable value)?

Let's consider the following example (from the start of a psql script): \c :db_to_run_on TRUNCATE the_most_important_table; -- tried to avoid similarities to anything that exists out there Now if ...