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)

0
votes
0answers
10 views

Imported a new month of data, now getting Numeric data overflow error when running query

We have an ETL job that brings in monthly revenue, and I've never experienced any issues with the data in terms of not being able to return results. The ETL job succeeded, but when I run a query to ...
-1
votes
1answer
22 views

Best way to query for a list and then run a query for each result in that list

I am new to databases in general and particularly new to PostgreSQL (v 9.3). I often find myself in the situation that I first run a select statement which will return a list of users stored in an ...
0
votes
1answer
29 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
5 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 ...
0
votes
1answer
21 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
17 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
38 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). ...
1
vote
0answers
20 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
22 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
5 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
12 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 ...
0
votes
0answers
25 views

MSSQL - Creating backup of 1 day worth of data and then merge them to make a full database [on hold]

I am new to this forum let me know if there is any update to be made. Use-case: I would like to create a snapshot daily which would have 1 day worth of data for 7 days and then combine it with ...
1
vote
1answer
19 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
25 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
12 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
19 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
69 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
22 views

EXECUTE format not working [on hold]

DO \$$ DECLARE customer_list varchar[]; customer_schema varchar(100); portal_row a.portal%ROWTYPE; var1 varchar(100); temp varchar(100); ...
0
votes
0answers
14 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
29 views

How to use variable value inside copy function [on hold]

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
21 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
54 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
12 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
15 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
33 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
18 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
31 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
32 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
16 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
29 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
24 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
12 views

Ubuntu: how to properly upgrade Postgres 9.3.x OpenSCG

I have a Ubuntu Server 14.04 with Postgres 9.3.4 installed. The Postgres instalation was made using OpenSCG packages (not using apt-get). Now I want to upgrade Postgres to the most recent version ...
0
votes
0answers
14 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
21 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
30 views

selecting rows from table under public schema where each row acts as a schema

in My DB,I have customer table in public schema.And then I have schema named after each customer.Each customer schema contains different tables like portals,plugin.... .When I want to select portals ...
1
vote
2answers
38 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
24 views

Index Advisor in postgres advisor [on hold]

I use Postgres as my Database.As part of my project I am trying to Index adviser for Postgres which suggests indexes automatically to the queries running slow . As far as I know only "EDB" has done ...
0
votes
0answers
15 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
14 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
13 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
49 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
22 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
54 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
33 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
20 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
48 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
15 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 ...