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)

0
votes
1answer
35 views

Postgresql Chained Slaves and Slave Promotion

So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from ...
0
votes
0answers
35 views

PostgreSQL and Single Table Inheritance

I read about STI (Single Table Inheritance) and like the idea. Does PostgreSQL handle 95% NULL values in some columns if I have about 1M rows well? Does it impact performance if there many columns ...
1
vote
1answer
50 views

Postgres: my “data/backup” directory is way too large

In my postgres 9.3 data directory, there exists a subdir called "backup" that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup? ...
0
votes
2answers
71 views

What is the Maintenance DB asked by pgAdmin?

pgAdmin dialog for adding new database connections asks for a "Maintenance DB". In order to be able to connect, I set it to the database I want to connect (and also, I have the rights to connect). ...
0
votes
0answers
31 views

Where is psql connecting?

TL;DR: It was a network issue. Read the updates at the bottom for what I found. Typing psql, I can connect: $ psql psql (9.3.2) Type "help" for help. ben=# Typing psql -h localhost, I can't ...
1
vote
0answers
150 views

pgAdmin goes “out of memory” with large queries

I am trying to run query with results stored in file from PostgreSQL. Now I am trying to use pgAdmin for this task: running query to file option. Problem starts with large queries. When I try running ...
1
vote
1answer
105 views

SQL hourly data aggregation in postgresql

I am a newbie with database so I'm seeking your help with this one. I have a table containing time series data. 2012/01/01 00:10, 10 2012/01/01 00:30, 5 2012/01/01 01:00, 10 2012/01/01 01:40, 10 ...
1
vote
2answers
91 views

Function execute query, manipulates result and then return the same result

I need a function that does something like this pseudocode: function get_data() RETURN SET OF something... as BEGIN myResultSet = select id, some_other_column from ...... limit 20000; update ...
1
vote
1answer
30 views

Limit balancing results by a column

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed. I also have a function that returns me the ...
1
vote
2answers
69 views

how should i do this trigger?

I am a newbie to the sql world :) Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ? Here is the code : DELIMITER ...
1
vote
2answers
57 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
0
votes
0answers
23 views

Longest IPv4 Prefix Match using PostgreSQL

Given an IP address 192.168.0.1, and a table with a column next_hop_subnet storing subnet IP addresses, do you see any problem with the following PostgreSQL logic, accuracy or performance-wise: ...
2
votes
1answer
85 views

Extremely long query time after upgrading to Postgres 9.3 from 9.1

We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. ...
0
votes
0answers
53 views

GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys

I'm trying to execute the following statement in a Rails app that uses postgis. Postgres 9.3.4, Postgis 2.1.3 (with homebrew on Mac). ActiveRecord::StatementInvalid: PG::InternalError: ERROR: ...
1
vote
2answers
85 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
0
votes
1answer
52 views

PgBouncer Uncertainties

I'm trying PgBouncer for the first time with a simple Python web application (Flask + psycopg2), and I have a hard time interpreting the meaning of its log messages by simple googling. I'm basically ...
0
votes
1answer
30 views

How to CREATE TABLE … LIKE without the NOT NULL Constraints?

I'm trying recreate a table's structure inside a function by using some dynamic SQL. EXECUTE 'CREATE TABLE ' || my_table_name || '_bk' || ' (like ' || _my_table_name || ')'; That will be ...
1
vote
1answer
54 views

PostgreSQL WHERE clause with two OR'ed conditions, priority to first

I'm storing user data (username, email, password hash) in PostgreSQL, and I can only enforce uniqueness on username. I'd like to let users provide either their username or their email when they login. ...
1
vote
1answer
79 views

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
0
votes
0answers
44 views

ZLM 7.3, postgres database must be vacuumed within x transactions

We have a legacy ZLM 7.3 IR4 primary server running on a SUSE 10.4 server with a local postgresql (8.1.23) database. We are getting the following warning from the nightly vacuum cron script ...
0
votes
0answers
25 views

PG Commander, how I can see how long the query is running already?

For postgres queries I use both PG Commander (osx) and pg_admin (ubuntu). With PG Commander I can't seem to figure out where I can monitor the running time of a query. In pg_admin there is a tiny ...
1
vote
1answer
132 views

Have Postgresql query planner use nested loop w/ indices over hash join

I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
0
votes
1answer
61 views

Compare two similar Postgres databases for differences

I occasionally download publicly available data sets in the form of Postgres dBs. These datasets are updated/modified/expanded over time by the repository host. Is there a Postgres command or ...
-1
votes
1answer
25 views

postgres: how to use subquery correctly?

I have the following query CREATE TABLE test_buffslip AS SELECT ST_INTERSECTS( (SELECT ST_Buffer(geom, 0.005) FROM segments where class = 7) ,geom) FROM segments; resulting in ...
0
votes
0answers
158 views

PostgreSQL --initdb fails on pg_authid - FATAL [closed]

I have installed postgreSQL. but when I try to do initdb it leads to FATAL ERROR.. $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/ The files belonging to this database system will be owned ...
1
vote
2answers
60 views

How can I get PostgreSQL to not advance sequences when COPY fails?

I'm importing CSV files into a relatively simple PostgreSQL database. (The CSVs are sometimes created manually from information in a book, sometimes created from spreadsheets with a little massaging ...
1
vote
1answer
60 views

Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint

I am importing from the GUI a CSV file into a table. I have created the following table: CREATE TABLE jira_tickets.daqa_rpt_tbl ( daqa_report_id bigint NOT NULL DEFAULT ...
0
votes
1answer
41 views

Better way to restore to existing pg database

We want to restore to an existing 9.3 pg database. What we ended up doing was: 1) delete the database and recreate again, 2) use pg_restore to restore. 3) reboot the ubuntu server to allow rails ...
1
vote
0answers
84 views

Postgresql - How to Convert all Column Names to Lowercase

I have a travel survey with a series of tables (lots and lots of columns) that I exported from MS Access into a postgres database. I've search around a lot for an explanation of how to convert all the ...
18
votes
6answers
677 views

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
2
votes
0answers
40 views

Postgresql import user from linux

I install postgresql 9.2 on a Ubuntu 12.10 machine I tried to import linux's user to postgresql but only way I can figure out is CREATE USER Jack WITH PASSWORD '1234'; and do it 10 times (if ...
0
votes
2answers
127 views

trigger_file doesn't work

I set streaming replication. Replication works just fine but it doesn't care about trigger_file. I have nothing fancy there except all what many of tutorials advices: recovery.conf: standby_mode = ...
1
vote
1answer
155 views

Recursive calls in stored procedure and temporary tables

I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to ...
1
vote
1answer
28 views

Organizing Database Objects in a Database that are not part of the systems original Database Objects?

I've been working at a place for about 2 years now with a series of heterogeneous legacy database applications, which are tied together using 3rd party Microsoft Access databases (*.mdb). Soon we ...
1
vote
1answer
74 views

Table variable not Recognized in stored procedure

The following stored procedure for a postgresql database attempts to insert into a temporary table 'result' of type result_load_status ,which is a table in database, and return this temporary table. ...
1
vote
0answers
43 views

TRUNCATE in transaction: dead lock

In a unittest we run this: BEGIN; TRUNCATE table1; TRUNCATE table2; ... UPDATE ...; ROLLBACK; Unfortunately this results in a dead lock if a cron job starts and this job operates on table1 and ...
2
votes
1answer
33 views

How can I know whether postgres is currently running synchronous or asynchronous replication?

I see from ps that: postgres: wal sender process replicator X.X.X.X(38681) streaming 0/17AF5438 But is there any way for me to know whether it is synchronous or asynchronous? I made the relevant ...
1
vote
1answer
32 views

capability and performance of tags implemented as array vs text vs full text

In terms of search capability and performance, what are the pros & cons of implementing tags as an array or text or full text field? I am seeing limitations of capability in choosing to implement ...
0
votes
1answer
19 views

Understanding postgres prompts

What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.
1
vote
0answers
18 views

Storing generated documents that can not be updated by changes in relationships

I have a system running with PostgreSQL and Yii Framework, which generates a few medical documents that, once ready, can't be changed. For example: If my full name comes out on this document today, ...
2
votes
3answers
509 views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
0
votes
0answers
36 views

PostgreSQL Failover strategy

I'm lost when it comes to choose the failover technology for my postgresql database. The options I currently see are: PG Cluster Streaming Replication Warm Standby (log shipping?) Hot Standby Is ...
1
vote
2answers
82 views

In PostgreSQL is there a way to rank/weigh columns so as to order the results?

Let's say I am searching through three text columns. I don't just want results that match a given word or phrase, but I also want to weigh them (column A > column B > column C) - thereby ...
0
votes
2answers
346 views

Moving a Postgres database from standalone local DB to Amazon RDS

I have a working Ruby on Rails 4 app running locally on my Mac. I'm running Postgres locally. I want to move the database (and only the database, not the whole app) to Amazon RDS Postgres. What is ...
5
votes
1answer
171 views

If PostgreSQL starts after copying data, is the data ok?

I'm copying the data for a PostgreSQL database, that is, /var/lib/postgresql, from one server to another. The servers are identical except for the fact that one is 32bit and the other one is 64. I ...
2
votes
2answers
112 views

Optimize a query on two big tables

I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
3
votes
1answer
63 views

How can I achieve clustering of rows without the exclusive lock and logging overhead of the `cluster` command?

The CLUSTER command on a big table can take a long time, and blocks both reads and writes to the table while it runs. I don't need the data in my table to be strictly sorted in index order, I just ...
0
votes
1answer
85 views

Restore postgres in command line on ubuntu failed

This question may be asked many times. We are trying to move a postgres db from windows (psql 9.3) to ubuntu runing postgres 9.3. Here is what we did: In windows, create backup for db cis with ...
5
votes
1answer
49 views

Is there a configuration setting for how many pages ANALYZE will scan?

Is there a setting that controls how many records/pages are scanned by the PostgreSQL ANALYZE command? It looks like by default it scans 30,000 pages and 30,000 records. The PostgreSQL Server ...
0
votes
0answers
35 views

pgAdmin Slony Creation Scripts

I have installed pgAdmin 1.18.1 but whenever I am trying to create a new cluster using Slony-I, it is always saying that it is missing some scripts. I have copied some .sql scripts from my Slony ...