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)

9
votes
1answer
326 views

Avoiding “Row by row” fetch method when dealing with source LOB columns

I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying: 'Row by Row' fetch method is enforced because ...
5
votes
2answers
70 views

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON users.id=user_sites.user JOIN ...
2
votes
2answers
24 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
0
votes
2answers
53 views

PSQL not executing SQL command - Powershell

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the ...
0
votes
1answer
111 views

PostgreSQL Role Configuration

I am using PostgreSQL 9.3. I am working with a friend of mine on a project on which both of us would need access to the same database and all of the permissions on every table. So I created a Role ...
0
votes
1answer
37 views

Postgres 8.4 VACUUM FULL performance

I am trying to run a vacuum full analyze on a postgres 8.4 machine where it appears that autovacuum has not been able to keep up. The database is very large and under heavy load. The problem started ...
0
votes
1answer
854 views

Even after permissions are updated, I still get the error permission denied for relation users

I'm getting the error ERROR: permission denied for relation users and have tried to update privileges using both grant all privileges on all tables in schema ops to my_user GRANT ALL PRIVILEGES ON ...
0
votes
0answers
16 views

Application uses all pgpool children up

I have few applications written in Java EE. They connect to PostgreSQL server through pgpool. PostgreSQL consists from master and slave. Datasources in my application server (Wildfly) establish ...
3
votes
2answers
3k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
1
vote
1answer
27 views

Can't create user in postgres

When I run CREATE USER username WITH PASSWORD 'test' in sudo -u postgres psql and run \du only postgres user appears... I'm not prompted anything when running commands like ALTER USER and I know USER ...
1
vote
0answers
24 views

SymmetricDS scheduled sync

I am trying to configure SymmetricDS to run a scheduled replication from one postgres database to another. I've seen people suggesting doing that by setting these fields in node config: ...
8
votes
2answers
178 views

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

I have created the table donor in the schema reference as per: CREATE TABLE reference.donor ( donor_code smallint PRIMARY KEY, donor_name character varying NOT NULL, donor_type smallint ...
6
votes
2answers
262 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
1
vote
1answer
699 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 ...
2
votes
1answer
39 views

Uniqueness constraint with date range

Consider a prices table with these columns: id (primary key) product_id (foreign key) start_date (date, non-null) end_date (date, non-null) quantity (integer) price (numeric) I'd like the ...
2
votes
3answers
43 views

Cap on number of rows matching a condition in Postgres

Say I have a db schema like this user_id text photo_id text date_created timestamp and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would ...
1
vote
2answers
42 views

Backup a large postgres database for PITR

I have one postgres 9.1 master and 2 slaves setup with streaming replication with WAL archive. Now, I am trying to set up for PITR for at least a window of 1 week. This is to cover for any ...
63
votes
6answers
7k views

Do stored procedures prevent SQL injection?

Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection ...
0
votes
1answer
32 views

PostgreSQL full-text search retrival from id gap

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part). There is a postgres function where I SELECT concat_ws(' ', ...
2
votes
1answer
35 views

Can Postgres index regular expressions stored in a column?

I've got regular expressions stored as varchars in a column, which I need to match against incoming input. For example, the table might contain: | field | value | |-------|---------------| | ...
2
votes
2answers
146 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
1
vote
1answer
30 views

Debugging Postgres error: “could not send data to client: Broken pipe”

I'm running a Django 1.8 site, served by nginx and Gunicorn, with a Postgres 9.4 backend. When I make lots of data-intensive calls to the site's API in succession, I've noticed about 1 in 100 ...
1
vote
1answer
162 views

PostgreSQL: How to get a sum of values and the total rows that went into each value? [closed]

I have a table, MyData in the following format: |id|theme_id|priority_id|value| I am trying to get the following result for all the rows: theme_id | priority_id | total | sum ...
0
votes
2answers
42 views

Trying to get count of childs rows

I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs. select c.id, ...
1
vote
1answer
776 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
1
vote
1answer
34 views

Is C collation suitable for my Postgres database?

I am working with 500m rows (120GB) in Postgres 9.4, and I am trying to make SELECT statements as fast as possible. I am less concerned about the speed of INSERT and UPDATE, because my data only ...
2
votes
2answers
242 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
0
votes
1answer
568 views

Connect to PostgreSQL via a ssh tunnel using Cygwin

I would like to connect to a Postgres server installed on a remote server using an ssh tunnel. I have Windows so I use cygwin for SSH. I do the following on cygwin: # Create the tunnel ssh -f ...
6
votes
1answer
77 views

SELECT DISTINCT ON subquery uses inefficient plan

I've got a table progresses (contains on the order of hundreds of thousands of records currently): Column | Type | Modifiers ...
0
votes
2answers
23 views

is it “bad” to bulk update a boolean column semi-regularly on potentially a large set of records in Postgres?

pertaining to a previous question I asked: how do you prevent dead rows from hanging around in postgresql? If I have a table with 300k rows, and I were to update all rows in the table, setting a ...
1
vote
1answer
773 views

Trying to start a newly created cluster in PostgreSQL on Windows

I just created a new cluster using PostgreSQL 8.3 and looks like everything worked. What I am having issues with is trying to start the server of this new cluster. My two clusters are on different ...
0
votes
1answer
29 views

combining 2 sql queries based on different conditions

I have a table : Person : (integer) (varchar) (Date) (date) id state d1 d2 1 'ALIVE' '2015-08-07' '2015-08-08' 2 'DEAD' '2015-08-08' ...
0
votes
1answer
36 views

PostgreSQL bug(?) - logging with a minimum log level

I am using the log_min_error_statement - Setting in the PostgreSQL configuration file, but the logger does not react on the setting, either if I turn it on, or off, or set it to another level, the ...
0
votes
0answers
32 views

How to call Oracle Function from POSTGRESQL

I am sorry if this topic already exists. I have: PostgreSQL 9.3 , Oracle 11gR2 on Linux . I used oracle_fdw to query Oracle table from Postgres but oracle_fdw can not call function. Questions: How ...
0
votes
0answers
43 views

Postgres query timing divergence

I have been using EXPLAIN ANALYSE and \timing option to clock the server side execution time and the client side time (using psql in Ubuntu). On running a same query, say an update query repeatedly, I ...
2
votes
1answer
124 views

Multi-column uniqueness constraint, checking a field has one of two values

I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example create table builders ( id text serial ); create table ...
1
vote
2answers
21 views

PostgreSQL Extension Multiple SQL Files

I'm creating a custom extension for PostgreSQL. I can get the extension to create successfully, but my foo--0.1.sql is getting rather large and complex. I'd like to break it out into multiple files ...
0
votes
1answer
26 views

PostgreSQL to MS Access

Is it possible to create a database in PostgreSQL move it into other computer and use this database with MS Access? How to do that?
1
vote
1answer
32 views

Continuously arriving data with fast filtering on date in PostgreSQL

I have a huge amount of data, which arrives continously. (About 10 million rows/day, ~3000 rows/10 seconds in peak intervals of the day.) I want to store them in a PostgreSQL database (I use PostGis ...
0
votes
1answer
59 views

Columns names are case sensitive on postgres

I have successfully migrated database from MySQL to PostgreSQL. While I am trying to connect with ofbiz, I am getting an error saying that "user_id" not found. When I query against postgres by ...
0
votes
1answer
20 views

Safe to Restore Database from Untrusted Sources

I am asking someone to aggregate around 100GB of data for me. I would prefer this to be in a Postgres database instead of something like CSV. I was thinking pg_dump and pg_restore custom format. Is it ...
2
votes
3answers
864 views

How do I use an Ispell dictionary with Postgres text search?

Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.
0
votes
2answers
194 views

PostgreSQL: how do I list all triggers that use a specific field?

for example i have a student table with (studentid) i want to get a list of triggers that use this field... for example if in table X there is a trigger that uses studentid i want it to be shown in ...
3
votes
0answers
41 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
7
votes
1answer
60 views

How to reclaim space taken by an index that partially built and was terminated by a power outage

I'm running postgres (postgis) 9.4.2 on a mac (10.10.4). I've got a couple big tables (several TBs). During an index build on one of them that takes about a week, I watched the available HD space ...
0
votes
1answer
30 views

Create postgres schema in db with multi user in AUTHORIZATION

Is it possible to create a schema that is owned or accessible by more than one user in the same database? I was reading postgres create schema documentation and postgres alter schema documentation ...
3
votes
1answer
35 views

Why does Postgres run SORT on many rows, before GROUP BY?

I'm using Postgres 9.4, with a recently analyzed database. These are my tables: Materialized view "public.vw_chemical_summary_by_ccg" Column | Type | Modifiers ...
9
votes
2answers
113 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
1
vote
1answer
46 views

Count childs of parent with sql or save in column?

In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this: WITH RECURSIVE ...
2
votes
1answer
280 views

How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE?

As was asked on stackoverflow but then for MySQL, I was wondering how this works in PostgreSQL. How many rows get locked when I do a "FOR UPDATE" in combination with a "LIMIT", a "ORDER BY" and some ...