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)

4
votes
3answers
561 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
0
votes
2answers
6k views

Using a Select in a Where clause for another Select

I have made a draft remote application on the top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functionning of the application. For each final business result that I ...
2
votes
0answers
458 views

Best book on query optimization, rewriting, refactoring [closed]

I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient. But in a more general sense, I need a book that explains ...
0
votes
2answers
941 views

Interactive INSERT / UPDATE function to implement UPSERT

I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 ...
6
votes
3answers
539 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
8
votes
2answers
11k 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
2k 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
138 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
522 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
66 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
501 views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
1
vote
1answer
916 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
504 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
168 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
2k 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
290 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
158 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
125 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
695 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
476 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
322 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
232 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
1k 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
2k 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
343 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
278 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
519 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
203 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
194 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
521 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
754 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
424 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
654 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
404 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
1answer
166 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
307 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
926 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
210 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
476 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 ...
5
votes
2answers
4k 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 ...
3
votes
2answers
203 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 ...
1
vote
1answer
543 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
176 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
2k 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
66 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
357 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
2k 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 ...