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)

17
votes
2answers
18k views

How to I grant a user account permission to create databases in postgresql?

Title says it all. I cannot figure out how to give a user account the ability to create and drop databases. Is there a way to do this with GRANT ?
3
votes
1answer
2k 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. ...
6
votes
2answers
3k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about 1MM ...
2
votes
2answers
141 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, ...
1
vote
1answer
677 views

PostgreSQL How do I convert struct text to plain *char in C functions?

I'm using my internal C function which doesn't know about postgresql's text struct, how do I pass text argument when char * expected? #include <stdio.h> #include <string.h> #include ...
1
vote
0answers
71 views

PostgreSQL Fuzzymatching Error

I get such errors when using these functions, what's is wrong? pgdb=# SELECT distance('foo','zoo'); ERROR: parse error - invalid geometry LINE 1: SELECT distance('foo','zoo'); ...
0
votes
1answer
686 views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
1
vote
1answer
1k views

Postgresql nested row_to_json not working

This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like ...
0
votes
1answer
627 views

Postgres 9.1 query plan not using check_constraints on partitions after large data load

I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip ...
0
votes
1answer
197 views

Postgres DB - Commit xlog files after server crash

After a server crash, it would seem that all transactions going back a number of months are not showing up in the live Postgres DB. Investigation has found all xlog files that does contain the data ...
0
votes
1answer
4k views

Save output from multiple SQL SELECT commands to a file

Within a Postgres function I would like to run a number of SQL SELECT commands and append the output to the same variable. After all the SELECT commands have run this output should be written to a ...
2
votes
2answers
362 views

Database Restoration Issue

I am new with postgresql. When I attempt to restore database a .sql.gz file into the database with psql, the restore does not complete when the database size is too large (32Gb, for example). These ...
1
vote
1answer
173 views

PostgreSQL replication

I have two projects working on different databases. The problem is: I need to replicate changes in the databases only for a few tables. I need it to synchronize the data. Need an advice how can I ...
4
votes
2answers
160 views

Performance of different precedence pseudocolumn solutions with LIMIT

I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests. The OP wanted to get the first matching row given ...
1
vote
2answers
959 views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
3
votes
1answer
540 views

Execution plan in simple query in PgSQL depends on offset clause, why?

Update on 2013-01-16 11:08: see below I am working on PostgreSQL 8.4.14 with the following table fed with 53k lines: CREATE TABLE botany.taxon_data_cache ( taxon_id int NOT NULL, ...
0
votes
1answer
360 views

MAX function and group by usage - postgresql

I am building a ranking algorithm using IMDB's formula: http://stackoverflow.com/questions/1411199/what-is-a-better-way-to-sort-by-a-5-star-rating/1411268#1411268 The formula for calculating the Top ...
0
votes
1answer
247 views

Storing 5TB web server access log vs OLAP DB

We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop. It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it. ...
8
votes
1answer
2k views

PostgreSQL pg_stat_activity shows COMMIT

We recently replaced our database server with an upgraded machine with 4 x quad core CPUs and 32Gb of ram. We also repurposed our old box to serve as a slave with streaming replication. Both boxes are ...
1
vote
1answer
3k views

Retrieving data from inner join using LIMIT and OFFSET

These are my two tables: table1 qid[PK] |gid[PK] |abcd | xyz | date ---------------+---------+---------+------+------------ 00001 | qwe | 54 | a | 1994-11-29 00002 ...
3
votes
1answer
393 views

Comparing table structure and contents using pg_dump output from two different databases

If I have the output of pg_dump from two different databases, how can I get the difference in structure and contents of all the tables between these two databases?
2
votes
1answer
315 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 ...
0
votes
1answer
605 views

pg_hba.conf from PostgreSQL

I have no idea how to configure that pg_hba.conf file, to connect with my server via pgAdmin. Right now i was trying to add: host all all host.name.org.pl trust host all all host.name.org.pl md5 ...
2
votes
1answer
270 views

sorting groups of related rows by average values while keeping the groups together

I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible. Consider the following table. corrmodel=# SELECT * from data limit 1; id | datasubgroup_id | datafile_id | ...
1
vote
1answer
223 views

PostgreSQL: how can queries possibly be “immutable” / functional?

I've read that PostgreSQL can have "functional" indexes. For that you need the immutable keyword. Seen that PostgreSQL is a CRUD DB I don't understand at all how it can possibly work in a functional ...
1
vote
2answers
692 views

inet_client_addr in PostgreSQL don't return IP

i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return is '::1/128'. This is a problem with my script or with the server? CREATE OR REPLACE FUNCTION ...
3
votes
2answers
1k views

How do I know what indexes to create for a table

Is there a way I can figure out the best way to know which indexes to create for a table?
1
vote
2answers
475 views

Database Design - One table from many source(One to One)

I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, ...
7
votes
1answer
705 views

Equality query on NVARCHAR column yields multiple results in SQL Server 2012

I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard). I've noticed an interesting phenomenon when querying unicode words. Given the definition: CREATE ...
5
votes
1answer
492 views

PostgreSQL: Recommendation size for shared_buffers

I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago. I am looking for help about the proper memory values for the configuration file. Currently, I ...
3
votes
2answers
220 views

How to prevent query execution after exiting the \e editor in psql?

Is there an option that can change the "exit external editor -> query runs automatically" behaviour of the psql \e switch ? (Other than intentionally mangling the query syntax).
0
votes
1answer
431 views

Is there a recommended way to update a database schema when updating and app's RPMs?

I have a pretty standard server layout: 2 Application servers running Tomcat on Redhat. 2 Database servers running Postgres on Redhat. One database is a master and replicates to the slave. I'd ...
5
votes
1answer
1k views

How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
3
votes
1answer
252 views

PostgreSQL Return Joined Results of Select

I'm learning PostgreSQL and was wondering if I can put long selects in stored procedures like this one: CREATE OR REPLACE FUNCTION api.book_list() RETURNS TABLE ( id BIGINT, published ...
1
vote
1answer
550 views

PostgreSQL - using password/md5 authentication with empty password

I have the following role in my system: CREATE ROLE test LOGIN INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; There is no password. Trying to connect to the db from within the same machine using ...
6
votes
2answers
5k views

Preventing PostgreSQL from starting on boot in Ubuntu

I am using PostgreSQL 9.2 and it runs automatically when I start the system (Kubuntu). How can I avoid this? I need to start it manually, and I don't want it to start unattended because I need to ...
4
votes
2answers
215 views

Custom Postgres function not working

I wrote this function that is supposed to iterate through a text array and convert each element (which is a typecasted integer) to hex, append to a text variable, then return the hex. However, I get ...
2
votes
1answer
615 views

Lost postgreSQL password in Windows installation

I have postgresql 9.2 installed on a windows XP machine. I have lost the postgres password. I looked at answers given to a similar question asked 6 months ago. The answer given there required the ...
0
votes
1answer
1k views

libreadline.so.6

when I was installing PostgreSQL from source code I had to install the libreadline.so.6 library. From that moment I can't update my kubuntu system. The error is: Setting up ubuntu-keyring ...
2
votes
1answer
181 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 ...
3
votes
1answer
1k views

SET extra_float_digits = 3

I'm using postgresql 9.2 with my web application that has three servlet with three select. I turn on the log. When i send my first query: 'select * from "Nomi" limit 4' before this the log show me ...
4
votes
1answer
3k views

ERROR: database “dbname” does not exist

According to the documentation, so long as I'm not connected to a database, I can either delete a database in the console using: DROP DATABASE dbname; Or I can drop it using the wrapper tool ...
0
votes
1answer
68 views

PostgresSQL: Cannot get to console under different user name

I normally get to the console in UBUNTU by typing: sudo -u postgres psql But I need to delete a database owned by a different user. When I type: sudo -u username psql It tells me "database ...
3
votes
2answers
518 views

How do I solve an error code 127 when doing a pg_dumpall through a cronjob?

I have this simple script to backup all my databases in postgres pg_dumpall -U pg_user > /tmp/tmp.txt DUMP_STATUS=$? echo "Dump status: $DUMP_STATUS" > /tmp/status.txt The dump ...
5
votes
1answer
3k views

pg_dump and ERROR: missing chunk number 0 for toast value

I'm using PostgreSQL 8.4.15. While running pg_dump to backup a database, I got the following error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 0 for ...
0
votes
3answers
976 views

Postgresql 9.2 won't start after rerunning installer

All, I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that ...
0
votes
1answer
310 views

Why doesn't more memory necessarily make Postgres more performant?

The Postgres resource consumption docs state, under the shared_buffers: ...it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Why ...
1
vote
3answers
4k views

Computer restarted and Postgres 9.2 service cannot be started?

My firewall is down and when I tried to start the Postgres service it gave me an error that said: the service started and then stopped. Some services stop automatically if they are not in use by other ...
16
votes
5answers
4k views

Best cloud platform for PostgreSQL [closed]

Currently, in our project, we use dedicated servers for our PostgreSQL database. In theory, we can run anything on some cloud platforms. However, PostgreSQL configuration is strictly related to ...
1
vote
2answers
9k views

Connect to Postgresql 9.2 from Eclipse Data Tools Platform

GOAL: My goal is to write sql code within Eclipse to query a Postgresql database. The reason I want to write my queries within Eclipse is to then write R or Python code to perform statistical analysis ...