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
1answer
46 views

List distinct column values where those rows share other column values

I'm trying to write a query to detect possibly-invalid data in a PostgreSQL table. We have a table of city names like this: # `city_names` id | name | language | dialect | city_id -----------...
1
vote
2answers
384 views

PostgreSQL - summing arrays by index

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array ...
1
vote
2answers
2k views

How to restore postgreSQL schema dump to schema with different name

I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema. The dump type is "custom". So effectively I want ...
1
vote
1answer
110 views

Does PGStrom calculate relational comparisons?

I prefer index calculation and comparison be executed on the GPU. I have stumbled upon PGStrom. It seems one could infer that it will calculate index comparisons, but the claim doesn't seem to be ...
4
votes
1answer
4k views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
1
vote
1answer
147 views

Connection pooling with 3 databases but 1 database as master for repmgr replication?

I'm using pgbouncer for connection pooling against 3 database on different servers, now I'm planning to use repmgr for replication, my question is, can I keep doing connection pooling on these 3 ...
4
votes
1answer
235 views

PostgreSQL performance worse when adding CPUs

I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this: Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs. Ubuntu 14.04, standard PostgreSQL 9.3 ...
0
votes
0answers
30 views

Postgresql: Domain of two values

I have to create a custom domain that is an interval between two years, but i actually need both years. For example: '2013/2014' '2015/2016' Is there a way to pack both years inside a single domain,...
1
vote
1answer
874 views

How to connect to an Amazon PostgreSQL database using SSL

I'm trying to log into an Amazon PostgreSQL DBS using SSL. I have all credentials but my psql version doesn't seem to support SSL. I've tried searching Postgres docs but all I see is how it has ...
1
vote
1answer
72 views

How much should I care about prepared statements for simple queries?

I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements. The question originates from the fact that I recently noticed ...
1
vote
1answer
481 views

Format timezone to +-#### instead of +-##

I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O". PHP Docs: O | ...
0
votes
1answer
47 views

Revoke delete from table for database owner

I am trying to revoke the DELETE privilege on a table for a database owner. I execute the command: REVOKE DELETE on my_table FROM database_owner; But when querying this via SELECT ...
1
vote
1answer
246 views

Replicating a postgres database for testing

I want to replicate a live server postgres database to our dev server for testing purposes but the problem is in inbuilt postgres replication feature the slave is always read only. Is there any better ...
0
votes
0answers
197 views

pgAgent does not run

I referred this to set up a scheduled job and did everything as it mentioned(ran the pgagent.sql as well) I need a very simple task to run periodically. In the 'Definition' tab in the steps, few ...
3
votes
1answer
566 views

Proxy authentication for PostgeSQL

The following seems likely to be a fairly common situation, but I haven't seen any solutions. I have a PostgreSQL database containing views that are specific to the $user, in order to restrict data ...
0
votes
1answer
237 views

How the xlogs files travels form Master to Slave

I have some doubts about how the workflow proccess in PostgreSQL replication using archiving logs. If I have this configuration in Master postgresql.conf: archive_command = 'test ! -f /path/to/...
0
votes
2answers
250 views

for archive_command use an script rather than a command

I want to create a configuration to get a PostgreSQL HA. In my Master I want to configure a postgresql.conf file with this: archive_command = 'local_backup_script.sh "%p" "%f"' instead of: ...
1
vote
0answers
42 views

Does pg-pool-II support two phase commit?

When pg-pool II is used with sharding, what happens if I want to do a transaction which involves two nodes ? Does pgpool support two phase commit ?
1
vote
1answer
115 views

BitmapIndexScan vs IndexScan tuning

I have the following test case: CREATE SCHEMA IF NOT EXISTS long; SET search_path TO long,pg_temp,pg_catalog; CREATE TABLE long WITH (autovacuum_enabled=off) AS SELECT id, (random()*1000)::int AS ...
3
votes
1answer
3k views

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last ...
3
votes
2answers
4k views

Why wouldn't VACUUM ANALYZE clear all dead tuples?

We run a "manual" VACUUM ANALYZE VERBOSE on some of our larger tables after we do major DELETE/INSERT changes to them. This seems to work without issue although sometimes a table's VACUUM job will run ...
2
votes
2answers
57 views

PostgreSQL query slows due to ORDER BY even with an index

I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes ...
1
vote
1answer
20 views

Find the number of where statements that matched in postgresQL

Right now I have a query that looks something like this SELECT id FROM products WHERE products.details->'make' = ? OR products.details->'model' = ? OR products.details->'year' = ...
0
votes
0answers
35 views

PostgreSQL is not installing correctly

I am installing postgreSQL in windows 7 64 bit system. The problem I am facing is that the setup is not finishing properly, the window showing installation is not terminating. The icons are visible ...
0
votes
1answer
141 views

Compare the same column on different dates

I have two tables project_key: project_id, key_id project_report: project_id, key_id, level, created_at I would like to compare the column level when created_at is now() and created_at is now()::...
3
votes
1answer
900 views

Combine a generated series with each row from a select

The below select is returning an array of all the projects owned by all my users. WITH user_projects AS (SELECT u.id as user_id ,COALESCE(array_agg(DISTINCT pa.project_id), '{0}'::integer[]) as ...
0
votes
1answer
34 views

How to change the mount point for a column in postgresql table?

This is my scenario I have a table t with following columns id int link varchar(500) name varchar(20) sal text In the link column we will store the location for the pdf files(mount point) ...
3
votes
3answers
258 views

Change table scan in query plan to seek

I have an ORM generated query (Django) running against a local Postgres 9.1.11 instance that looks up ~5 records from two specific match fields. Due to the nature of the EDI load program, I need to ...
0
votes
0answers
57 views

Postgres - Combine similar queries with extra where clause

I am trying to combine a number of fairly time consuming queries into one (if possible) The database contains race-track data that records the times for laps completed by drivers (or users here), the ...
1
vote
2answers
93 views

How to understand “object-relational”?

I can't understand why the document say "PostgreSQL is an object-relational database management system"?Can somebody tell me the reason, or give me some article about this question. Thanks a lot. ...
5
votes
2answers
445 views

manual execution of specific remote query with postgresql_fdw

In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g. > explain verbose select max(col1) from remote_tables.table1; ...
3
votes
1answer
375 views

How can I identify queries that are producing cache misses in Postgres?

I have a Postgres database with a total data size of 115GB. The server has ~60GB of memory. The index cache hit rate is holding at 99%+ but the table cache hit rate has fallen to ~97%. I am trying ...
3
votes
2answers
1k views

PostgreSQL 9.3: Index on to_char()

How can I index a to_char() of a column? I have tried: adam_db=> CREATE INDEX updates_hourly_idx ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00')); But got the error: ERROR:...
0
votes
1answer
108 views

Storing not very volatile information in database with cache or mongodb

For a new project I'm working on right now I was doubting on which database/storage system to use for specific information which does not change that often. We have for instance some company-...
1
vote
0answers
65 views

Can we pass a varchar/text datatype in to a variable in pgbench?

--pgbench sql script [pg93@db1 load_test]$ cat get_email.sql \setrandom cnt 1 100000 \set v_username 'user_' || :cnt begin; select get_user_email(:v_username); end; get_user_email() is a function ...
1
vote
1answer
312 views

Postgres create an insert return rule

I am failing to create a rule which will return the insert's data. The table is: CREATE TABLE foo(a int, b text); My attempt for the rule is: CREATE RULE return_data AS ON INSERT TO foo DO ...
0
votes
1answer
19 views

The number of copies for each table stored in physical storage

I am new to Postgres and the database field. My question is that how many copies of each table are stored in physical storage device (memory, disk and so on)? I assume we need more than one copies for ...
0
votes
1answer
52 views

Trying to load a file into a database on virtual machine

I have set up a Postgres db on a linux vm and have been having no issues using a GUI to connect to it. However, I am trying to load a large, 32GB, file onto it and so am skeptical of the old way I was ...
0
votes
1answer
29 views

dm_exec_query_stats in PostgreSQL

I recently started using PostgreSQL after using SQL Server for a while. Is there something like dm_exec_query_stats? I haven't been able to find anything other than logging every long running query. ...
3
votes
1answer
215 views

postgres: Database replica with some schemas writable

I have two computers. Computer A is dedicated to collecting data and storing it in its local database. Computer B is for analysis. A is running flat out just with the data collection, so I want to ...
1
vote
2answers
962 views

no pg_hba.conf entry for host “10.1.1.91”, user “user” database “testing”, SSL off

I've come across different versions of this same issue on stackoverflow and on this forum. I've tried the recommendations posted on these forums but I am still unable to connect to a database. I've ...
1
vote
0answers
81 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
2answers
455 views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT 2+...
10
votes
2answers
19k views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then on the application some times, we only ...
2
votes
1answer
743 views

postgresql 9.3: two instances on same port and two different ip adresses don't work

i'm setting up multiple instances of postgres 9.3 on the same machine (centos 7). I have 2 virtual ip addresses, one instance is bound to first address and second is bound to second one. Same ...
2
votes
1answer
459 views

Delay Trigger Execution

Let me explain the scenario first before asking the question. I have a database with a set of tables. One table, say Table A, contains username details, and other table, say Table B, contains user ...
1
vote
0answers
269 views

pg_basebackup could not get WAL end position from server

I'm trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's ...
0
votes
1answer
65 views

using string concatenation to reference a table

For a database systems project which tracks entities histories, I stumbled acros the following problem. Assume we have the table "entity" CREATE TABLE entity ( eID SERIAL PRIMARY KEY, eName ...
1
vote
1answer
365 views

How to trigger newlines in log messages?

Is it possible for log messages to span multiple lines? I am expecting: RAISE WARNING 'Line 1\nLine 2'; to result in the following log: Line 1 Line 2 But instead I get: Line 1\nLine 2 Any ideas?...
4
votes
2answers
4k views

Trying to set up new data_directory on Postgres 9.3

Ubuntu 14.04, recently installed Postgres 9.3. Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that. However, I get ...