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)

0
votes
0answers
35 views

How can I calculate the difference between my time column in seconds? [on hold]

I have got a large database, one of the columns is the time with this format "08:27:22" and I am looking to find out the difference of seconds between the "open doors" and "close door" events. The ...
9
votes
3answers
119 views

Combining separate ranges into largest possible contiguous ranges

I'm trying to combine multiple date ranges that may or may not overlap into the largest possible contiguous date ranges. For example: Data: CREATE TABLE test ( id SERIAL PRIMARY KEY NOT NULL, ...
0
votes
0answers
25 views

Postgres uses a lot of cpu

First of all, I am a bit newbie in case of postgres database but I was requested to check this out. The problem is that postgres processes occupy time to time almost 100% inside the linux system - I ...
3
votes
1answer
780 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
2
votes
1answer
115 views

Problems restoring backup from base backup + WAL with WAL-E

I'm trying to do nightly restores of the main database on a separate node. I want to do weekly full restores and nightly restores by consuming the latest WAL files. On the master I have wal_level = ...
0
votes
0answers
18 views

Postgres Query Optimisation (Rails App)

I have a very slow running SQL query that is run from my Rails app using the Model.find_by_sql method. I've made a number of optimisations but it's still extremely slow. It would be great if anyone ...
0
votes
1answer
725 views

pgpool2 setup on master-slave replication using postgreql 9.1

I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1). 192.168.0.109 = master 192.168.0.110 = slave my ...
1
vote
1answer
136 views

Unsupported languages in Postgres Full text search

My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search. If I were to go ...
2
votes
1answer
30 views

GRANT USAGE on all schemas in a database? postgres

I want to GRANT USAGE to a user/role for a given database. The database has many schemas. I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but ...
1
vote
1answer
19 views

Spiky COMMIT results in performance slowdowns with Postgres 8.4

We just installed performance monitoring on our web application, and we're seeing a spiky pattern in response times from our Postgres 8.4 server. The spikes correspond with significant slowdown in our ...
0
votes
1answer
23 views

PostgreSQL Backup and restore, sizes are different

I Have a Wordpress database in my postgreSQL, in phppgadmin this database is shown with the size of 273 MB. I've ran sudo -u postgres pg_dump -Fp -f wordpress.sql wordpress to do the database backup, ...
2
votes
1answer
18 views

Postgresql: Aggregate objects into an (json) array (subquery issues)

I'm sorry for the vague title, but I simply don't know the right words to describe this. I have this query that transforms a bunch of columns into an object that works just fine: SELECT ...
26
votes
3answers
20k views

Most idiomatic way to implement UPSERT in Postgresql nowadays

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert ...
0
votes
1answer
10 views

How to update array fields in a table in Postgresql?

Say I have a table defined as following: CREATE TABLE my_tbl ( id bigint, a1 bigint[], a2 bigint[] ); I would like to create a stored procedure to append values to a1 and a2. Is this ...
8
votes
1answer
34k views

How do I reset the postgres password for PostgreSQL on Windows?

I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I ...
3
votes
2answers
20k views

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the Postgresql database on my Windows 7 professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
2
votes
0answers
13 views

PostgreSQL query - works locally but not on AWS RDS

We have a large query that works fine locally but deadlocks on AWS RDS. According to cloudwatch we are not resource bound on CPU, disk or iops and nothing else should be accessing these tables. The ...
0
votes
0answers
21 views

how to migrate data between two distincts tables

I have a database with millions of records in a table users. I need to collect this data, tranform to a new format (removing a field, for example) and save it to another database. I'm currently doing ...
0
votes
0answers
28 views

Starting the Database Server postgres [on hold]

OS X 10.10, postgres 9.3.5, installed from here I had to initialize the datacluster manually, as stated on the site, through $ /usr/local/pgsql/bin/initdb -U postgres \ That worked fine. At the ...
0
votes
0answers
17 views

Unable to point out Exact cause behind anonymous block failure(Postgresql) [on hold]

DO $$ DECLARE customer_list varchar[]; customer_schema varchar(100); BEGIN customer_list=ARRAY(select cname from customer); -- Get a ...
2
votes
2answers
126 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
0
votes
0answers
33 views

Tuning a Postgres INSERT only database

I am designing a database which will have many inserts and selects, but no updates. Are there any considerations to take (in order to have high query performance) while knowing no tuple updates will ...
0
votes
1answer
12 views

Use a value for a Postgresql setting in a trigger

We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them. I'd like to be able to use ...
1
vote
1answer
25 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...
1
vote
1answer
157 views

Partial unique constraint spanning multiple tables in postgres

To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so: CREATE TABLE possession ( possession_id serial ...
0
votes
1answer
111 views

mongodb on-premise: will it work? [on hold]

We are planning to package MongoDB-PostgreSQL hybrid solution in our application that will be deployed on customer site on-premise. It will be packaged as a software virtual appliance. Our reason for ...
0
votes
0answers
9 views

Print to console as SQL executes

When executing multiple SQL commands, such as in the pgAdmin SQL Editor, how can I print some strings to the console (output pane in pgAdmin). I want to monitor execution. UPDATE x SET a = b WHERE a ...
1
vote
1answer
26 views

postgresql won't install dependencies

Please help me figure out why postgresql isn't playing nice with updating. I can't find anything on SO or Google that remedies this problem. This is a production server and a distribution reinstall ...
1
vote
1answer
13 views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
1
vote
1answer
329 views

Confused over encoding/locale in postgresql

I am a little confused over the difference between encoding and locale as it pertains to the postgresql database. When initializing the database, you can specify both an encoding and a locale. ...
2
votes
1answer
110 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
0
votes
2answers
21 views

How to use Italian locale for Postgres messages

On Oracle I have set: export NLS_LANG=Italian_ITALY.UTF8 .. on Unix shell and now I get status and error message in Italian. How to set in Postgres shell (psql)? I have set: SET lc_messages TO ...
3
votes
1answer
86 views

How to index for timestamp queries

I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 vendors a ...
2
votes
1answer
47 views

Was using `set datcollate='C'` a mistake?

In a pre-production environment running django 1.6 on PostgreSQL 9.1 we had a problem with database records arriving in a different order from what the application was expecting. So we ran this ...
3
votes
2answers
158 views

Have: Postgres database files. Need: Functioning database

I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously ...
0
votes
0answers
13 views

A PostgreSQL user with SUPERUSER and all permissions to particular database can't read a specific column [on hold]

I am trying to edit a website. I created a user for myself and granted all permissions and set up that user as SUPERUSER, using the user postgres. I still can't read a specific column (which is ...
0
votes
1answer
48 views

Is there a timeout option for remote access to PostgreSQL database?

I'm working via pgAdmin III on a remote PostgreSQL database. After a not so long period (say 10-15 min.) without doing anything in pgAdmin, the connection automatically expires. I therefore have a ...
5
votes
1answer
332 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
1
vote
0answers
16 views

postgresql pg_dump no matching schemas [on hold]

I have a local postgres 9.4 installation with pg_dump. I am trying to do a remote pg_dump where I'm looking to dump only the schema DDL with NO data. I am getting the error: pg_dump: No matching ...
0
votes
1answer
35 views

Postgres automatic insert time stamp with time zone (india) [closed]

Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we ...
3
votes
2answers
636 views

Do I risk losing the benefits of indexing if I have an index on every column?

I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
0
votes
1answer
35 views

pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

I try to configure pgAgent on Windows server 2008 to make automatic backup on the database. pgAgent is installed on the server and the service is running normally. I use PgAdmin3 and PostgreSql 9.3 I ...
0
votes
0answers
21 views

Postgresql indexing for IS NULL

I have two similar queries, whose only difference (in the last two lines) is that one uses IS NULL while the other compares defined values. Query #1 takes only a few minutes: UPDATE ...
0
votes
1answer
42 views

Reduce the number of queries to the same table

I need to build some reports using two tables: AuthorTitles: id(pk), author_id, title_id AuthorReports: id(pk), author_id, title_id, items_sold, date My report needs to output the fallowing: ...
2
votes
1answer
509 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...
2
votes
0answers
20 views

Warm Standby PostgreSQL 9.4 error; FATAL: the database system is starting up [on hold]

I want to setup a Master and a warm standby, so far here's what I have setup: Master postgresql.conf listen_addresses = '*' wal_level = archive archive_mode = on archive_command = 'copy "%p" ...
0
votes
1answer
21 views

Postgres: Disable “similarity” column output on “SELECT SIMILARITY”?

I've tried disabling similarity column output on SELECT SIMILARITY but still cannot. Below query will generate a column similarity: SELECT SIMILARITY(title, 'Nation'), title FROM gallery WHERE title ...
2
votes
1answer
275 views

REST webservice as a Foreign Data Wrapper in postgresql

I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
0
votes
1answer
122 views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
0
votes
1answer
391 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...