All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

3
votes
0answers
673 views

Fastest way to get the length of the intersection of two text arrays in PostgreSQL

This is what I am currently using: CREATE OR REPLACE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS $$ select array ( select unnest($1) intersect select unnest($2) order by 1 ...
0
votes
1answer
2k views

Restore PostgreSQL data using PITR

I am unable to restore the database to a previous time using the PITR steps mentioned on PostgreSQL website Article 23.3.3. Recovering with an On-line Backup ...
17
votes
3answers
16k views

psql: Show current database

In psql I can use \c <database_name> to connect to a given database. How can I see what database I am currently connected to? When I do: my_db> current_database(); I get: ERROR: ...
0
votes
1answer
971 views

Attributes from an hstore-column as separate columns in a view?

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs. You could query that column as such: SELECT name, attributes->'device' as ...
4
votes
1answer
2k 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 ...
6
votes
1answer
389 views

Hierarchical structure with limited number of records per user

I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem. Any user can refer only 3 other users at max. So for ...
6
votes
2answers
9k 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 ...
0
votes
0answers
140 views

Converting a Ruby on Rails site from MySQL to PostgreSQL? [closed]

Whats the best way to convert a MySQL database to PostgreSQL so that it does not create errors, and safely transferred? My developer is redeveloping my site and he suggested it use PostgreSQL ...
3
votes
1answer
115 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?
1
vote
2answers
4k views

Postgres client unable to detect UTF-8 database

Context I am deploying a Vagrant box for my development team, and have encountered a problem restoring UTF-8 collated dumps. I don't believe it is related to the now-infamous "UTF8" has no equivalent ...
0
votes
1answer
706 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
0answers
575 views

Create new user without typing current user password

On a VPS, I need to create a new user on PostgreSQL 9.3 so I have tried sudo -u postgres psql -c "create user new_user with password 'secret';" This works but it asks me for the current user ...
0
votes
0answers
601 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
478 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
48 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" = ...
1
vote
2answers
165 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
2
votes
2answers
409 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?
8
votes
0answers
378 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 ...
2
votes
3answers
8k views

Counting postgres open connections without SQL query or connecting to postgres itself

For a monitoring script I need to count the number of open connections to a postgres database. This is done easily enough by opening up a client connection and running a count on the pg_stat_activity ...
0
votes
2answers
135 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) | ...
2
votes
2answers
4k 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
1answer
192 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
2answers
2k 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
0answers
327 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 ...
2
votes
0answers
3k 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 ...
0
votes
0answers
2k 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 ...
3
votes
1answer
2k 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 ...
0
votes
0answers
311 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
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 ...
20
votes
2answers
20k 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
1answer
90 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 ...
4
votes
1answer
3k views

How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?

I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege. db=> create role replicator login password 'something'; CREATE ROLE db=> alter ...
0
votes
1answer
1k 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
118 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 ...
4
votes
1answer
1k views

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
1
vote
1answer
85 views

restoring Postgres dump with tablespaces

I'm trying to restore a dump done with pg_dumpall > mybak.sql from a server that has 1 table in a different tablespace that the default one. On the target machine I have recreated the directory ...
0
votes
1answer
833 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 ...
9
votes
1answer
621 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
1
vote
1answer
170 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
2answers
3k views

Installing and configuring postgresql 9.3 on ubuntu 13.10 using puppet

I'm having some trouble getting postgresql[1] to install with puppet[2] on a blank ubuntu box. I need to install postgresql 9.3 on Ubuntu 13.10. Since postgresql 9.3 is apparently not in the official ...
1
vote
1answer
1k views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
0
votes
1answer
131 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/
4
votes
1answer
684 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 ...
-1
votes
1answer
794 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 ...
8
votes
1answer
9k 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? ...
2
votes
1answer
261 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? ...
2
votes
1answer
204 views

PostgreSQL's Query Hierarchical data poor performance

Badly need some technical assistance on this query performance issue I'm working on. Summary of the data structure is: 1 project has many homeprofiles, 1 homeprofile has many tasks, 1 task has many ...
1
vote
1answer
582 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
256 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 ...
2
votes
1answer
2k views

Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB. These DB are running on ...