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)

1
vote
2answers
73 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
49 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
19 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
46 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
62 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
37 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
20 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
80 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
46 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
20 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
91 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
48 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
44 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
29 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
74 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
638 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
39 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
115 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
112 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
24 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
54 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
38 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
31 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
24 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
18 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
339 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
32 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
64 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
174 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
167 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
104 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
57 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
56 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
41 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
29 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 ...
0
votes
1answer
32 views

Easy way to replace an YAML array of IDs with strings from a joined table

I have a database full of camp data. Each camp has many campers and has many camp_weeks. In the table of campers, there is a YAML encoded array of IDs for the camper's camp weeks. The project ...
0
votes
1answer
42 views

Why does 'plpgsql' not appear in the pg_extension table?

I have two Postgres 9.1 installations. One works properly, the other does not. On the working installation: dc=# select * from pg_extension; extname | extowner | extnamespace | extrelocatable | ...
0
votes
0answers
50 views

scheduled job using pgagent in postgresql doesnt run

I am running postgresql 9.1 on linux ubuntu on Oracle Virtualbox. I have installed pgagent which I am trying to use to run scheduled jobs created through pgadmin III. For testing, when I run a ...
2
votes
1answer
50 views

Does postgres efficiently re-use space from deleted rows?

From the docs: In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row [...] But eventually, an outdated or deleted row version is no longer of interest ...
1
vote
2answers
128 views

When normalizing a relational database, should multiple “type” tables be combined into one?

Before making substantial changes to the content, functionality and design of my website (I'll be refactoring everything, almost a complete rewrite), I'm setting up a new PostgreSQL database to hold ...
0
votes
1answer
49 views

Index creation before or after loading data

I have a situation where I need to create a table in Postgres and add approximately 3 million rows. Currently I am adding an index to one field after all the data is loaded, but I was wondering ...
0
votes
0answers
16 views

Connecting to postgres server from remote client gives error

I need advice of the following please. I can login to PGAdminIII from my workstation and read/write to my database. However trying to login from the command line gives me an error. After entering ...
0
votes
2answers
41 views

Thai and English in a postgresql

I have a postgresql DB with Thai and English values/strings in several tables. Currently I have the following settings | Encoding | Collate | Ctype | | UTF8 | en_US.UTF-8 | en_US.UTF-8 ...
1
vote
1answer
37 views

RANGE clause error within window function

I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it: SELECT ...
2
votes
1answer
110 views

Using PgBarman for PostgreSQL 9.3 backup

I'm setting up a backup strategy for a PostgreSQL 9.3 db. My job includes: take db backups every day/week/month use a Synology NAS as remote backup device I'm thinking of using Postgres ...
0
votes
1answer
50 views

Postgres database dump and restore on different database [closed]

I have two Postgres databases on the same server, which have the same schemas. The goal is to have DB1 as the production and DB2, as the database that will receive all the data that is migrated from a ...
2
votes
1answer
97 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 ...
1
vote
0answers
51 views

Any experience with cstore_fdw?

It looks like there is a (finally) an open source columnar and compression extension for Postgres. I wonder if anyone had any experience with it yet? I've worked with Greenplum for years, but I've ...
1
vote
1answer
77 views

Decrease WAL file size for backup

I'm setting up a backup strategy based on WAL files archiving and transmission via rsync. As DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the ...