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)

3
votes
3answers
996 views

PostgreSQL 8.3: Slow GROUP BY on large table

I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow... select run_id, count(*) from result group by run_id; I have an index ...
3
votes
2answers
277 views

Identifying postgres db health tool similiar to mysqlcheck

I'd like to run a check against databases in postgresql to verify that everything is ok after the server crash. On mysql it would be mysqlcheck. I would like to know the postgres equivalent.
2
votes
2answers
116 views

Is there a way to avoid duplicating the definition of a foreign key?

I was wondering about this example CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references ...
0
votes
2answers
2k views

Open source alternative to SQLMaestro for PostgreSQL

I'm looking for oen source alternative to SQLMaestro for PostgreSQL because it has visual database designer which not avaliable in pgAdmin which make database desinging so easy. PS I'm not sure if ...
3
votes
1answer
348 views

Query optimization or missing indexes?

I have this query to aggregate some data from t2 into t1. This is done to optimize the application I am working on so that there are less queries to the database. I chose the below approach to make ...
1
vote
1answer
78 views

There is an equivalent of login.sql on PostgresSQL?

When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed. It's usefull to set preferences... There is an equivalent ...
1
vote
1answer
354 views

Determining the full postgresql size on windows

While opening the Programs and Features tool panel on my Windows 7 (64-bit), I found that my postgresql (8.4) install was taking 108 GB. I do not use postgresql myself, I just use a tool that need a ...
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
461 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
947 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
542 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
503 views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
1
vote
1answer
918 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
292 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
126 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
697 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
477 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
344 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
279 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
195 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
523 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
755 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
426 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
405 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
308 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
931 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
477 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 ...