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

learn more… | top users | synonyms (1)

1
vote
2answers
152 views

How to determine how much disk-space postgresql 9.1 requires as temp_space to run a query?

Given a query q to execute. Is there a way to determine how much disk-space postgresql will (or used) as temporary_space in order to execute it (during its execution) ? I'm under Ubuntu 12.0.4 with ...
4
votes
2answers
1k views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...
2
votes
1answer
435 views

Is there any way to save the execution plan in a file in PostgreSQL?

By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. For example, if the optimizer chooses to do a hash join for a two-table ...
2
votes
3answers
39k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
2
votes
1answer
1k views

How to get query runtime in PL/pgSQL to be as fast as console query runtime?

Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ...
0
votes
2answers
457 views

Why we can backup postgresql database backup without password?

I want to backup my PostgreSQL database with pg_dump command that takes a lot of parameters. One of them is --no-password. Why this parameter is provided? I mean if anyone can backup database, so ...
0
votes
0answers
492 views

PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

I am working on a financial software where we have huge table of ticks with more than 800M rows. A single row: symbol_id, broker_id, date, time, bid, ask The problem is the table has many ticks ...
1
vote
0answers
72 views

Pg_restore table size differences

What would account for the difference in table sizes between one PostgreSQL database and another restored from its dump file? On an Ubuntu 12.04 host, I'm running PostgreSQL 9.1, and I dumped my ...
3
votes
0answers
558 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
1k 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 ...
13
votes
3answers
12k 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
826 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 ...
3
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
369 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 ...
5
votes
2answers
7k 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
137 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 ...
2
votes
1answer
105 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 ...
1
vote
1answer
626 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
529 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
555 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
427 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
162 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
350 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?
6
votes
0answers
308 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
7k 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
129 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
3k 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
153 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
1k 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
277 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
278 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 ...
16
votes
2answers
16k 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
84 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
985 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
117 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
74 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
676 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
494 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
157 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
909 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
120 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/