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)

1
vote
1answer
9 views

Delete array element by index

Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.) I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but ...
1
vote
2answers
25 views

Combine multiple similar queries into one

I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5. ...
1
vote
0answers
10 views

Can bdr only replicate one database per server?

I have installed the new BDR-Solution with PostgreSQL 9.4 and it works like a charm. My Problem: I want to add more databases to the replication but every time if I added the other database and ...
1
vote
0answers
17 views

Postgres configuration, incoming connections

Hello I'm trying to figure out the best way to allow connection from certain ips/hosts to my db instance. I've got an api(s) pointing to the database and right now we do something like this ...
0
votes
2answers
19 views

Group by transformed column

I have a database with creations and orders, and a bunch of tables linking them. I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a ...
1
vote
1answer
17 views

Create/copy new superuser from an existing one

I have a superuser for a PostgreSQL database, let's call him userX. How can I make a new superuser (let's call him userY) with all the same permissions, access, etc...? Basically, I want a clone of ...
0
votes
0answers
20 views

Integrating Postgres/GIS data in a SQL Server environment without generating 'data islands'

Currently I'm facing an scenario in which a GIS expert is planning on starting to integrate mapping services for several departments within the organization I work for. I handle around 40 mid-sized ...
0
votes
1answer
16 views

Get table name by foreign key

For example I have a two tables in the database. First table has a foreign key to second table. Can i get second table name by the foreign key from first table? Thank you.
0
votes
1answer
16 views

Dropped column still has value when recreated with Postgres table of 150M rows

I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working: ALTER TABLE ...
0
votes
1answer
25 views

Pgpool II : unable to read message length between two network interfaces

Start log file: 2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for 0.0.0.0:9999 2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for :::9999 2015-03-06 01:57:56: pid 2760: LOG: ...
0
votes
2answers
33 views

What is more efficient: text(765) or varchar(765) to store a freeform searchable description?

EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back? A little background on the question. Let's say we have sales_orders table and we want to be able to keep a ...
0
votes
0answers
28 views

Table with both primary key and unique key

My system requirement defines a unique business/domain/natural key (i.e. <yyyymmdd><branch_id><running_number>, in bigint). Should I use this unique key as primary key or I should ...
0
votes
1answer
39 views

How to avoid duplications using a function

I am using this function in a bulk insert to avoid duplication of url paths. CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4) RETURNS "int4" AS $BODY$ DECLARE ...
0
votes
1answer
24 views

Storing historical data in DB vs on disk

I've been pondering on alternative solutions to speed up data retrieval from my DB. I currently have a relatively large database, about 1 billion rows and growing. We expect the DB to grow linearly ...
0
votes
1answer
28 views

Help converting a MySQL query to postgres

Is it possible to do this query in postgresql? SET @p:= 0; UPDATE t SET x=CEIL((@p:= @p + 1) / $n) ORDER BY id DESC; I'm struggling with the variable syntax. I tried: \set p 0 update t set ...
-1
votes
0answers
20 views

Optimize a query sorted by a join value [duplicate]

This query fetches the likes on a post from people you follow, sorted by the number of followers they have: EXPLAIN (ANALYZE, buffers) SELECT * FROM "Likes" AS "Like" INNER JOIN "Users" AS "user" ...
0
votes
1answer
24 views

Index for a Postgres query with a sort and an equality

This query is very slow: EXPLAIN (ANALYZE, buffers) SELECT * FROM "Follows" AS "Follow" INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id" WHERE "Follow"."followerId" ...
1
vote
0answers
25 views

How can I tell which client process owns a particular Unix domain socket connection?

I recently tried to call ALTER TABLE, which got blocked because one of the transactions holds a lock on the related table. With the help of #postgresql IRC channel, I managed to track down the problem ...
0
votes
1answer
14 views

How to use language PL/PythonU on a PostgreSQL database via phpPgAdmin?

I am used to PgAdmin III where I can simply create python functions like the following on a PostgreSQL database: CREATE OR REPLACE FUNCTION pyHello (x integer) RETURNS void AS $$ if x == 1: ...
0
votes
0answers
15 views

PostgreSQL slow update -> does autovacuum have anything to do with it?

I am quite new to PostgreSQL as I only need it to create a map (using PostGIS). So I don't really know on hot how to optimise my DB... I have a table with a lot of polygons that I'd like to save in a ...
1
vote
0answers
9 views

Storage snapshots for consistent backup of postgresql - different data and log volumes

We are running many Linux VM's in a vmware/shared storage environment, each running its own instance of postgreSQL (a mix of 9.0 and 9.3). Currently, the entire VM sits on a single root ...
1
vote
2answers
38 views

Postgres ON DELETE Rule Not Working With WHERE Clause

I'm trying to implement "Soft Deletes" given the following schema: CREATE TABLE categories( id serial not null primary key, num integer, "name" text, deleted_at timestamp default null ); ...
0
votes
1answer
12 views

What postgres sync solution should I use to duplicate production, but read it from another application?

I'm a little out of my league with my current problem, but I'm looking for a solution where the following conditions are met: My production database reads and writes as it currently does My ...
1
vote
1answer
14 views

Using psql with string variable that has two periods

psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3" into the script begin; create table db_log ( db_owner varchar(255) not null, db_user varchar(255) ...
0
votes
0answers
9 views

How to transform Streaming Replication Master-Slave to Cascaded Master-Slave1-Slave2 setup?

I've setup streaming replication as part of HA solution on my production enviroment. I've two ubuntu servers PROD1 and PROD2. PROD1 is master and PROD2 is slave. In case of failover the slave database ...
0
votes
0answers
9 views

Data base design for handling website subscriptions

I have a website where at least 20 posts are posted in a day and each post contains more than 5 categories and each category has further tags. I wanted to build a subscription module that users can ...
0
votes
1answer
38 views

From PostgreSQL to SQL Server (No User/Pass)

Some of you MAY be able to crack a really tough nut... I have to move all the data from a PostgreSQL (9.1 - Windows) database to SQL Server because the company I am contracting with is upgrading the ...
0
votes
0answers
32 views

FATAL: terminating walreceiver due to timeout

1/ DESCRIPTION: Machine 1 (slave): Centos 6.6 , x64 , installed PostgreSQL 9.3 (on Local) Machine 2 (master): Centos 6.6 , x64 , installed PostgreSQL 9.3 (on Cloud) Machine 1 (slave) and machine ...
2
votes
2answers
39 views

UNIQUE constraint on large VARCHARs - PostgreSQL

I have a column defined like: data_url character varying(32768) NOT NULL and UNIQUE constraint on that column: CONSTRAINT unique_data_url UNIQUE (data_url) When a large object is being inserted ...
0
votes
1answer
56 views

Random unguessable primary key which preserves correct order

I want primary keys in my table to be random and unguessable while still preserving correct order (order in which documents were inserted to database). Firebase uses this algorithm: ...
2
votes
2answers
46 views

PostgreSQL CHECK constraint is evaluated wrongly

Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced. drop database if exists example; create database example; create table example_table ...
0
votes
0answers
27 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
0
votes
1answer
56 views

Pros and cons of using many schemas in PostgreSQL as opposed to just one?

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the ...
0
votes
0answers
29 views

11.2GB text file import MySQL or PostgreSQL

I am out of my depth a bit perhaps but submit this query hoping someone has some experience importing large (11.2GB) tab delimited text file (2.2GB tar.gz file) into either MySQL 5.5.41 or PostgreSQL ...
0
votes
2answers
45 views

How to rollback transaction if row changed?

I am using Postgres 9.3. When I update row in a table my flow looks like this: Fetch old row (ex. SELECT * FROM tbl WHERE id = 1) Validate new data in scope of old data (old row fetched in point 1) ...
0
votes
1answer
9 views

extract state changes from snapshot-based archive table

I have a table snapshot, storing snapshots (of the new state) every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to ...
0
votes
0answers
15 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 ...
-2
votes
1answer
26 views

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

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 ...
2
votes
1answer
68 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
8 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
28 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
21 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
42 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). ...
2
votes
0answers
31 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
28 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
10 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
19 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
27 views

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

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 ...