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)

2
votes
1answer
613 views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
2
votes
2answers
1k views

Getting last modification date of a PostgreSQL database table

I'm trying to get when my table was modified by checking its file modification date as it is described in this answer. But the result is not always correct. The file modification date updates in ...
3
votes
2answers
4k views

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
2
votes
1answer
65 views

What does “unpackaged” mean in PostgreSQL extensions?

I've read the docs and a few blog postings, but I still don't get what the "unpackaged" tag in an extension file name is for. When would I use it and not use it?
4
votes
1answer
3k views

Passing an array or record to a stored procedure in PostgreSQL

I have a task to pass arrays, records and in some cases array of records as a parameter to the stored procedures in PostgreSQL. Any ideas?
0
votes
1answer
314 views

PostgreSQL encoding conversion error

I have a Postgresql database in LATIN9 encoding. I recently migrated my web applications (PHP) from iso_8859_15 (latin9) to UTF8. So, now, customers are able to specify UTF-8 characters in web forms ...
1
vote
2answers
152 views

Postgres slave max_connections = master's max_connections?

I am using binary replication for Postgres. Is it true that the max_connections has to be the SAME for both the master and slave in postgresql.conf? Can they be different?
2
votes
0answers
663 views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
0
votes
2answers
21k views

Can't connect to the postgres server ls: /tmp/.s.PGSQL.5432: No such file or directory

*Update*I included an update at the bottom of this post with details from the logs explaining the problem that I haven't been able to fix. Following along with this Railscast ...
0
votes
0answers
345 views

Yet another slow query Postgresql 9.3

I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, ...
2
votes
1answer
191 views

How to generate time in milliseconds in PostgreSQL stored procedure?

I have been reading the Postgres documentation about timestamps, but I am still a little bit confused. I need to store moments in times, measured in milliseconds, between the current time and ...
1
vote
1answer
44 views

What indexes would I create for a query like this?

Here's a query I've got: SELECT "balance_transactions"."fee" FROM "balance_transactions" JOIN charges ON balance_transactions.source = charges.balance_id WHERE "balance_transactions"."account_id" = ...
7
votes
1answer
778 views

Unexpected Seq Scan when doing query against boolean with value NULL

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM. CREATE INDEX index_table_on_auto_renew ON table USING ...
2
votes
2answers
1k views

Migration from SQL Server 2008 to Postgres 9.2 [closed]

I have a rather large database (2 GB of data, 25 million rows in the largest table) on a shared web hosting. The database engine is Microsoft SQL Server 2008. Due to high fees for the SQL Server web ...
0
votes
2answers
55 views

Help with query statement

EDIT This query seems to work best so far. select host,plugin_id,month,year, row_number() over (partition by plugin_id order by plugin_id) as plugin_split from table where risk in ('High') group ...
4
votes
0answers
142 views

PostgreSQL Transaction Committing for hours

I'm running into an issue whereby I have two connections from a user to my PostgreSQL server which have been running for about 4 hours and have been in a commit state for quite some time (at least 1 ...
0
votes
2answers
94 views

How to change table/indexes structure for a query with two aggregated colums

Do you have any suggestion on how to improve a table structure or its indexes in order to have a single query run faster? The table has this structure: docid | character varying(17) | ...
0
votes
0answers
145 views

Is there failover server parameter in PostgreSQL connection string?

Is there failover server parameter in PostgreSQL connection string that can be used to automatically switch from primary server to secondary sever ? We have been using similar functionality on MS SQL ...
0
votes
2answers
375 views

how Postgres works internally [closed]

I would like to learn how postgres works i.e how queries are handled internally,the read and write performance ? I don know where to start,help me with refrences
0
votes
1answer
61 views

How to alter column from float(8) to numeric(64,8)

I am working on a website that store lots of numbers in a very specific precisions (8 digits). Most of the columns are using numeric(64,8), but I discovered that a handful are using float(8) instead, ...
0
votes
0answers
123 views

Can’t connect to local PostgresSQL server

I have the Postres app installed an running on my Mac. And it has worked beautifully. I don’t know what changed, but now I cannot connect to it from Rails, PG Commander, PG Admin or the command psql ...
0
votes
0answers
765 views

Stress-test PostgreSQL 9.2 and pgpool

i am experiencing some trouble testing Postgres cluster and load balancer on pgpool. I i wrote script script.sh on bash which asynchronously uses Postgres testing tool pgbench causing number of ...
1
vote
0answers
1k views

High availability PostgreSQL 9.3 server on Windows

I'm trying to set up PostgreSQL 9.3 server in HA mode on 2 Windows servers. Final solution should be deployed to Microsoft azure network, so there are few limitations. Since Microsoft is doing ...
3
votes
1answer
791 views

PostgreSQL JSON field performance with large documents

How well is the Postgre JSON type optimized for large JSON documents? I am especially concerned about partial retrieval (e.g. the cost of getting the last N items of a JSON array or looking up one ...
4
votes
1answer
267 views

PostgreSQL does not use all available IOPS on a single operation

Using PostgreSQL 9.3 on Windows 7x64, CoreI7, SSD. Assume a PostgreSQL table with 8 Int32 columns and following C# code to fill the table. using (var connection = new NpgsqlConnection("User ...
4
votes
1answer
4k views

Superuser is not permitted to login

I created a superuser portal with the following command create role portal with superuser password 'portal' When I tried to login into postgres with portal user I am getting the following error in ...
0
votes
0answers
61 views

How big of the parameter “shared_buffers” setting on postgresql [closed]

According the document from http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server. Even on a modern Linux system, the stock kernel will likely not allow setting shared_buffers to over 32MB ...
0
votes
1answer
603 views

Postgres 8.3 faster than 9.3?

Our software product currently ships on Windows 7 with Postgres 8.3 as its database. On a busy site, there may be 24 automated systems generating 100 rows (x100 columns) per minute, with 3-10 human ...
1
vote
2answers
101 views

Wrong index used in Postrgres

I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a fairly large database (some tables with approx. 1 mil. records) and I have the following query: SELECT * FROM ( SELECT DISTINCT ...
0
votes
1answer
50 views

pg_dump inserting C source code in dump file

Am backing up daily my DB with pg_dump. The strange issue is that when I try to restore it with psql, errors are thrown. After opening the dump file with vim and analyzing the line that is causing the ...
0
votes
0answers
76 views

pgpool not selecting new master

I have a master-slave Postgres setup in basic streaming replication I'm trying to get behind pgpool-II. I have servers replicating find and connections to pgpool look to be getting load balanced ...
0
votes
1answer
227 views

PostgreSQL: Cannot Connect to Server via PgAdmin, Password Auth Failed [closed]

I've been attempting to connect to my server's PostgreSQL database. The error message I am getting is "Error connecting to the server: FATAL: password authentication failed for user "postgres" I've ...
0
votes
1answer
86 views

Subtracting dates give me error in Postgresql

I use age to get the interval between to date fields. Their type is timestamp. My code is as below: select age(timestamp MySecondDateField, timestamp MyFirstDateField) The data in my field ...
2
votes
0answers
294 views

Postgres: PANIC: WAL contains references to invalid pages

I have a slave database that is being replicated from a Master DB. All of a sudden, my slave DB just exited and wasn't able to restart. Here are the error messages when it exits. Similar ones appear ...
0
votes
1answer
71 views

Rotate Postgres log at 5GB

How to make postgres 9.1 log to have maximum size 5GB and to be circle when comes to 5GB ? At the moment my log file is over 500GB. /var/lib/postgres/
7
votes
1answer
6k views

MongoDB performance vs. PostgreSQL with 5.5 million rows / documents

Can someone help me compare these queries and explain why the PostgreSQL query executes in just under 2000ms and the MongoDB aggregate query takes almost 9000ms and sometimes as high as 130K ms? ...
-1
votes
1answer
205 views

Group by for UNION not working Postgresql

I can't find a way to make Group by works in Postgresql, How can i make group by works so each name will be only one , not repeated many times like what i got in this picture? Here is a code for what ...
2
votes
1answer
113 views

How to create Select query function in the Postgresql?

I want create select function in postgres and get all data from table. I many example see and try but not perfact execute function. What perfact syntax for select query function create in postgres? ...
0
votes
1answer
156 views

Issues with 'plus symbol' output in PSQL on update query

Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field: grouper=> select * from master_list WHERE group ~ 'JustAdopted'; group | ...
1
vote
1answer
217 views

Postgres streaming replication to a different schema on another server?

We've got a system with about a dozen Postgres 9.3 databases representing a dozen different applications. In order to do reporting across systems, we have a script that dumps each database and loads ...
3
votes
1answer
123 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
22
votes
4answers
16k views

Most idiomatic way to implement UPSERT in Postgresql nowadays

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert ...
0
votes
1answer
818 views

Postgresql indexes in memory

I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes ...
1
vote
1answer
518 views

I just upgraded PostgreSQL with yum on Amazon Linux, now PostgreSQL won't start. What do I do?

I just ran yum update on my Amazon Linux instance as part of an upgrade to 2012.09 and it upgraded PostgreSQL from 9.1 to 9.2. Now PostgreSQL won't start. What do I do? (This is a very incomplete ...
1
vote
1answer
256 views

Design DB for users with different information fields?

Let's say I want to register all faculty of a university and they are in different fields and have different resume information. Here we have some common fields and some field specific fields. for ...
0
votes
2answers
236 views

How to list all table columns in sorted order?

I know that \d table_name lists all columns for that table, but is there a way to list them sorted in alphabetical order?
4
votes
3answers
2k views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I have installed oracle_fdw successfully. After I create extension oracle_fdw and foreign table it shows the following error. Is this a server configuration problem? --env Oracle : 10.2.0.1 ...
25
votes
3answers
42k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
0
votes
2answers
406 views

How do I use an Ispell dictionary with Postgres text search?

Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.
0
votes
1answer
59 views

Postgresql Write Only Primary Server Bottleneck for Streaming Replication

I'm wondering about whether CPU/RAM plays an important role for the primary server for a Postgresql Streaming Replication setup where you have one primary server that receives all the write operations ...