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
86 views

PostgreSQL 9.x: a clustered index on column A is the same as creating a table ordered by A?

Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables: The CLUSTER command reorders the original table by ...
0
votes
0answers
11 views

Postgres Join that allows for an empty table [migrated]

I have the following schema, and I'm having a lot of trouble joining these tables properly. I'm trying to get all folders that don't have a file updated in the last 10 mins in them. But I want to ...
0
votes
0answers
9 views

Schemas with the same name in a Redshift cluster?

Is it harmful in some way to have multiple schemas with the same name in a database cluster, specifically Redshift? I want to create multiple databases, and then create a schema "tables" or "data" or ...
1
vote
1answer
30 views

How compiled are prepared statements?

I found this explanation of how prepared statements are compiled, but I'm still unsure. What exactly is "compiled" in the context of a prepared statement? Is a C compiler executed to create a custom ...
0
votes
0answers
15 views

SQL social connections query

Given the following 2 tables: Profiles --------- id, name Connections ----------- id1, id2, status where status is (for the sake of the example) either 'pending', or 'active'. What I'm trying to ...
1
vote
0answers
29 views

Pass a URI as a pgAdmin parameter

I can connect to Heroku with psql passing a URI: $ psql postgres://user:password@ec2-54-243-xxx-xxx.compute-1.amazonaws.com:5432/d7xkxididkdi I want to do the same with pgAdmin: $ pgadmin3 -qc ...
1
vote
0answers
53 views

Simple avg query on large table much slower in PostgreSQL than SQL Server

I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012). ...
3
votes
0answers
48 views

Get inserted row count when master table isn't modified

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables. Now, I need to create ...
0
votes
2answers
43 views

Write output from dynamic queries in PL/pgSQL to CSV file

PG_CMD="psql -d portal -U portal -c " PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c " abc() { $PG_CMD " DO \$$ DECLARE srowdata record; ...
0
votes
0answers
26 views

Failover_command in pgpool is not working?

I have setup the pgpool with load balancing and failover mode. Following is my failover_command='/usr/local/etc/failover.sh %d %P %H %R' The following is the script: !/bin/bash -x FALLING_NODE=$1 ...
0
votes
1answer
29 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...
1
vote
1answer
23 views

How do I debug an Idle Query?

I have a batch query that I'm running daily on my database. However, it seems to get stuck in idle state, and I'm having a lot of difficulty debugging what's going on. The query is an aggregation on ...
1
vote
2answers
29 views

Speed of WAL send/receive when replicating

1/ Description: I have a cluster include two machines Master: postgresql 9.3 run on Centos Slave : postgresql 9.3 run on Centos Master ----> Slave : relpica from Master to Slave by asynchronous ...
0
votes
1answer
25 views

Avoid PG::TRDeadlockDetected

I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR: ...
1
vote
1answer
21 views

Linking Products to Users Via Categories

I'm trying to match products to users where the users has the same set of categories as the product, here is the schema: User ——— user_id ... Users Categories ——— user_id category_id Products ...
1
vote
1answer
89 views

How to put the results of a query into a variable?

I have written a Postgres anonymous block. In which I have written join query on multiple tables like: select a.portal.*, a.p_fb_config.*, a.p_gplus_config from a.portal left outer join ...
0
votes
0answers
24 views

PostgreSQL 9.3.5 database restore error : plpgsql already exists

I have a .net program to restore a PostgreSQL database backup. What it is doing is 1) Delete the existing database. 2) Create it again. 3) Initialize some variables/properties. 4) Restore the ...
-2
votes
1answer
44 views

How to use variable value inside copy function [closed]

I want to send data to .csv file using COPY function in Postgres. But I am using anonymous block so my table name should be value of variable. COPY (SELECT cname.portal from user) To '/tmp/out.csv' ...
0
votes
1answer
30 views

dynamic postgres inside shell script not working

PG_CMD="psql -d portal -U portal -c " PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c " abc() { $PG_CMD " DO \$$ BEGIN select * from customer; ...
1
vote
0answers
64 views

How to speed up query which is using <@> operator?

I am using earthdistance extension (along with cube extension). I have following query: SELECT "User"."id" ,"User"."displayName" ,"User"."firstName" ,"User"."lastName" ...
0
votes
0answers
24 views

Insert into a specific partition, create if not exists

So I have my master table: p_reports: id(pk), web_id, other(data) At the moment I create my partitions which inherits from p_reports by using: CREATE TABLE IF NOT EXISTS ...
0
votes
0answers
29 views

Umlauts in DBeaver

I have a postgres Database set to UTF-8. I am working with DBeaver 3.1.4, and it seems to use a different encoding than UTF-8 (I suppose Latin-1 or something), which leads to 1) wrong display in the ...
1
vote
2answers
45 views

Index for numeric field is not used

I have PG server (9.0.4) installed on Debian x86. In one of my tables I use numeric(6,1): CREATE TABLE mytable(name text, numfield numeric(6,1)) Also index created for this field: CREATE INDEX ...
0
votes
0answers
21 views

Sampling and matching samples with spatial conditions

The Problem I have two tables with start points of a route and possible end points of a route which need to be matched against each other. I also have a given distribution for the distance between ...
0
votes
1answer
41 views

Setting PostgreSQL data_directory in postgresql.conf not working

I'm attempting to configure PostgreSQL 9.3 on a Centos 6.6 box to keep the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) in a different directory than the data directory. According ...
1
vote
0answers
39 views

How to modelling a union type in PostgreSQL

We have the generic idea of a rule with specific implementation details stored in their own table. We currently have the following schema: Rule * id * name * ruleAId * ruleBId * .... * ...
0
votes
1answer
23 views

Is it possible to backup my ubuntu postgresql database by copying the /var/lib/postgresql/9.1/main file and pasting it back in?

A developer working on my server has fiddled with some settings and knocked my postgresql database offline. I've been messing with it for an hour and am considering just reinstalling postgresql fresh ...
0
votes
0answers
40 views

Postgres Won't Start - incomplete startup packet

I have an issue that is preventing a Postgres server from starting. The server fell over after it ran out of disk space, how ever this has now been resolved although now Postgres will not start. It ...
2
votes
2answers
38 views

query to find all dblink()

Besides manually searching for all the dblink() used in triggers/functions/stored procedures is there a way to query for this information? Example: There is a dblink inside of a trigger, can I select ...
0
votes
0answers
27 views

Install specific older version of postgresql centos

I need to rebuild a production system with same version of installed packages. It's a Centos 5.11 and it was using this repo http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm to ...
0
votes
1answer
42 views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
0
votes
1answer
60 views

Select rows from table where each row refers to a different schema

I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by ...
1
vote
2answers
73 views

Return counts for multiple ranges in a single SELECT statement

I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and ...
0
votes
0answers
49 views

Is Automatic failover is possible in pgpool?

I have setup the 2 PostgreSQL 9.4 severs with master-slave(Hot standby streaming replication).Is this possible in pgpool-II 3.4 to configure,where it works for automatic failover(network or hardware ...
0
votes
1answer
15 views

postgres finding child tables for particular table

I want to find all child tables corresponding to particular parent table. SELECT * FROM information_schema.table_constraints tc right JOIN information_schema.constraint_column_usage ccu ...
0
votes
0answers
14 views

Are my queries to Postgresql hot spare actually being handled there?

I have master and minion database servers each running Arch Linux, with the latter acting as a hot spare database with replication. I wrote a service that is supposed to check whether the replication ...
1
vote
2answers
59 views

Postgres Index a query with MAX and groupBy

Is there any way to index the following query? SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id; I've tried creating sorted (non-composite) indexes on frame and ...
0
votes
0answers
9 views

Problem with Postgres installation on MacOSX - psql does not work [duplicate]

I'm currently trying to install Postgres (using the installation package from http://postgresapp.com) on OSX Mavericks (10.9), i.e. through downloading, unpacking, and dragging the Postgres app into ...
0
votes
1answer
25 views

Postgres installation on MacOSX - problem with psql

I'm currently trying to install Postgres (using the installation package from http://postgresapp.com), i.e. downloading, unpacking, dragging the app into the Applications folder, launching the app. So ...
-3
votes
1answer
63 views

How match a numeric column with a varchar parameter? [closed]

external_id in my source table is of varchar(255) data type I need to convert it to a numeric(20) to do a lookup on target table where card_id (contains external_id's) is of numeric(20) data type. I ...
0
votes
1answer
39 views

Delete index while query is running, is it possible?

I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still ...
0
votes
1answer
23 views

Is there any way to get the optimization time in PostgreSQL? [duplicate]

I'd like to see how long it takes to optimize a query. Does PostgreSQL support this feature?
3
votes
1answer
67 views

How can I implement a sequence for each foreign key value?

In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus ...
0
votes
1answer
53 views

How to convert time without time zone to timestamp without time zone in PostgreSQL?

I received this error when trying to alter a column of type time to type timestamp: PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone It's not ...
0
votes
1answer
26 views

How to update records of one table with the values of another one (PostgreSQL)

I have a table t1 with columns account_id and user_id, and another table users with columns id and account_id. I want to populate t1.account_id with the values of users.account_id. For instance ...
0
votes
1answer
19 views

How to set weights on a Postgres TSVECTOR column?

Currently, I'm using Postgres 9.3.5 for full-text search but I don't want to set weight or convert to tsvector on the fly (a lot of articles suggest that, and that just seems like a terrible idea to ...
0
votes
0answers
21 views

Efficient Trigger updating in Postgresql

So I have a big table that often needs specific columns updated for a large number of rows. So right now I have it set up so that there is a 'staging' table that has the update-able columns. Then upon ...
0
votes
1answer
51 views

Best query to exclude existing friends from query

I have a User table / id_user / username / .. friends table (pair AB and BA): / friend_of / friend_to and a friend_request table (used for pending friend requests) / id_req/ sent_from / sent_to ...
1
vote
1answer
28 views

How to measure or estimate the streaming replication lag in Postgres 9.2

I've been asked to reverse engineer an app that uses postgresql in the back end. I can see that there is some database replica tion going on... based on my reading, I *think it's called streaming ...
0
votes
0answers
24 views

Tuning PostgreSQL for minimum reliability and bulk updates

I'm using PostgreSQL to host some data that comes from external source, the normal operation of the database is read-only with periodic updates. Updates though are kinda huge e.g. in a 50 million row ...