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

Procedure to read from crontab like table and populate another one periodically

Postgres 9.3 Debian 7 I'm working on a mobile game project (poker like) where an admin user can register a recurrent tournament. As it's recurrent, I modeled it crontab like as follows. ...
2
votes
0answers
28 views

pg_dump with ssl certificates

I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
1
vote
2answers
56 views

Is it recommended to install extensions into pg_catalog schema?

Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?
0
votes
1answer
20 views

Postgres or MapReduce to Match Reciprocal Needle Pairs from a Stack of Needles?

I have a Postgres (latest version) table with about a million lines (a_lat, a_lon, b_lat, b_lon) that each have about a dozen criteria. These lines go together in reciprocal pairs (A - B, B - A), ...
0
votes
0answers
10 views

Updating the details of an ACCESS-2003 database on completion of a PostgreSQL UPDATE trigger

I am currently stuck and looking for some further guidance. I have a database in access, which on UPDATE (trigger function) will take the data inputted into the PostgreSQL table in QGIS, and update ...
1
vote
2answers
37 views

Proper usage of Group by in Inner Join

EDIT I updated my question with a SQL Fiddle Sample http://sqlfiddle.com/#!15/8d88b/1 I'm currently making a report from a database records but I don't know how my query should look like, first of ...
2
votes
0answers
24 views

PostgreSQL/PostGIS: Query seems inordinately time-consuming

I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other). Both tables have GIST indices on their spatial ...
2
votes
1answer
83 views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
-1
votes
0answers
30 views

How would I make this query run faster in postgres [closed]

Query: with customer_total_return as (select wr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, sum(wr_return_amt) as ctr_total_return from web_returns ...
3
votes
1answer
81 views

How to store time series data

I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values. An example would be modeling a car and tracking its various attributes during a ...
0
votes
2answers
33 views

PostgreSQL, merge join results

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results. Table "job" : parent_id, target_id, start_date, end_date, jobposition_id ...
3
votes
2answers
73 views

How to log custom messages from inside a PostgreSQL transaction?

I have a software that generates transactions and executes them. I would like to asynchronously inspect about the transaction progress in term of the simple proportion of queries executed over total ...
4
votes
2answers
73 views

Grouping by sequential relationships with Postgres

This is quite similar to this question about continuous ranges, and this question about grouping by sequential numbers, but differs in that the sequences are not numeric. Given the following ...
3
votes
2answers
130 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
1
vote
0answers
57 views

Having problems trying to back up a Postgres database from AWS RDS

I am trying to backup a Postgres database from RDS and I am getting this error message. pg_dump: [archiver (db)] query failed: SSL error: unexpected message pg_dump: [archiver (db)] query ...
3
votes
1answer
55 views

Finding optimal indices, different query competing indices

My schema: create TABLE "logs" ( "id" serial not null default nextval('logs_id_seq'::regclass), "request" varchar(1024), "token" varchar(512) default NULL, "was_batch_request" bool ...
2
votes
2answers
114 views

How to cast to int array in Postgresql?

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc ...
0
votes
0answers
31 views

Merging data from two slightly different datasets

A bit of backstory: we used to have a single Postgresql db that we had to duplicate when we opened a new office. So at that moment there were two indipendent dbs with perfectly identical data. Over ...
2
votes
1answer
28 views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
3
votes
3answers
205 views

Do I want an index if all I care to query is field not null

In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null? Is there a special configuration of the index ...
0
votes
2answers
28 views

Get min and max row from table

From a table with the columns: id(pk), type_id, book_id, title_id, page_id, lines(int4), created_at(date) How can I select the row with min(lines) and max(lines) for (type_id, book_id, title_id, ...
1
vote
1answer
36 views

Challenges with Key with Gaps?

I had a mistake with a trigger which resulted in errors in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit. Each time during the error, the primary key ...
0
votes
1answer
45 views

PostgreSQL window functions very slow on big table

I have a PostgreSQL table constructed as device_id | point | dt_edit ----------+-----------+---------- 100 | geometry | timestamp 101 | geometry | timestamp 100 | geometry | ...
2
votes
1answer
19 views

Update postgres row where condition is from another table

I want to update a password field from a given value that exists from another table. user_info: -------------------------------------------- | user_login | password | ...
5
votes
0answers
123 views

Why is my PostgreSQL ORDER BY case-insensitive?

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior: veure_test=# show LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) veure_test=# SELECT ...
1
vote
0answers
33 views

PostgreSQL suddenly using all my disk space

Today I fired up my linux mint via my virtual computer (host is windows 8), left for 30 minutes, and when I was back, I got an error saying I was out of disk space. Yesterday I had about 20 free GB ...
0
votes
1answer
41 views

Does PostgreSQL benefit from more cores or faster cores for READ heavy applications? [closed]

I have been trying to figure out how to go about configuration of a PostgreSQL instance that will have 64GB ram (which is far larger than the database) and if I should opt for more cores or faster ...
2
votes
1answer
28 views

How to allow users to run a function, but not access objects the function is accessing

I have a PL/PgSQL stored procedure that performs various operations on tables (SELECT, UPDATE, INSERT, TRUNCATE). I want users to be able to run the stored procedure, but not themselves perform those ...
0
votes
1answer
36 views

What is the proper setup for PG user for web application

We have a web app that detects if the DB does not exist and creates it. Users like it, but my thought is that such user has too much privileges. I would optimally propose to change the DB creation ...
1
vote
1answer
118 views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
2
votes
0answers
42 views

Recovering data from postgresql xlog

I lost a lot of data in my postgresql database and do not have a dump file to restore backup from, but I heard that in /var/lib/postgresql/9.4/main there are pg_xlog and pg_clog and that those ...
0
votes
0answers
24 views

Multiple table relationship for different scenarios!

We have the following heirarchy in our platform; Package is comprised of Products, Products is comprised of Services and Services are comprised of SKUs. The relationship between them is many-to-many ...
3
votes
1answer
48 views

Automatic aging-out (deletion) of old records in Postgres

Does Postgres have any features to support aging-out old records? I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.
3
votes
2answers
64 views

how do you prevent dead rows from hanging around in postgresql?

I have production and staging RDS instances on amazon, and staging's data is a direct copy of production so both instances have duplicate data. Doing a EXPLAIN ANALYZE SELECT * from my_table WHERE ...
2
votes
0answers
57 views

Flexible and robust way to cull older data?

I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes ...
1
vote
3answers
166 views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ...
1
vote
0answers
35 views

How to use variables in a PL/pgSQL function with dynamic queries?

...and when must I use EXECUTE ... quote_ident(variable)? Taking an input to the function as text and storing part as a variable: wtype := split_part(message, ' ', 4); realval := ...
1
vote
0answers
33 views

Efficient structure for querying membership in many-to-many relationship where tableA has ONLY the indicated members from tableB?

I'm working on an application with a PostgreSQL back end. There is a many-to-many relationship between items in one table and tags in another table. The specs require that the application be able to ...
1
vote
0answers
24 views

How to prevent multiple queries in this case? [duplicate]

I am using Postgres. I have 2 tables. PS : week starts from monday. Time (id (bigInt), startOfWeek (Date), startOfMonth (Date), startOfQuarter(Date), day (Date) ...) and User (id (bigInt), timeId ...
1
vote
2answers
50 views

Select rows from T such that sum(T.quantity) is greater than an argument

TLDR: I need a way to limit my select such that only rows needed to fill my new order are returned. Hey, I'm looking for a better way to do a query on the simplified toy table below. Right now I do a ...
3
votes
1answer
89 views

psql breaks if I specify host localhost

I am aware that this or similar has been asked in other places. None of the other answers have worked for me. Machine is a Macbook Pro 2014 / OS X Yosemite 10.10 with Postgres 9.3.5 installed via ...
2
votes
1answer
30 views

How to calculate shift hours in postgres?

I have table like below where (I=In) and (O=Out): id | date | time | status | employee_id ----+------------+----------+--------+------------- 1 | 2015-07-01 | 11:00:00 | O | ...
1
vote
1answer
39 views

Using a GIN index on an integer array to model sets of tags

In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table? I am trying to design a simple data model with a ...
4
votes
1answer
37 views

Implementing a function written for Postgres 9.4 in Postgres 8.4

I have a simple function (tested on Postgres 9.4) that I need to implement on Postgres 8.4. The error I get is that function format does not exist. I've been trying to find resources but 8.4 is 6 ...
2
votes
0answers
52 views

operator class “gin_trgm_ops” does not accept data type tsvector

ALTER TABLE users ADD COLUMN full_text_vector TSVECTOR; CREATE INDEX full_text_index ON users USING gin(full_text_vector gin_trgm_ops); The last line is giving me the following error: ...
2
votes
1answer
62 views

Where is the default postgres user path environment set?

This is probably simple but I can't seem to find the answer. We have a server running postgresql 9.4 on CentOS 6.6. Recently we had somebody run a yum install for postgres which installed postgresql ...
5
votes
2answers
32 views

Combine two event tables for multiple timelines into a single result set

This question is an extension to a question I've previously asked that was overly simplified. The more accurate example is demonstrated in this SQLFiddle, where I demonstrate a working (but slow) ...
3
votes
1answer
69 views

Slow window function query with big table

I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions. Here is my table setup: CREATE TABLE player_stat ( ...
3
votes
1answer
53 views

Why does it take so long for Postgres to return a sequence number?

I have an application that does bulk loads into a large table (100 million rows). I am using Postgres' COPY FROM functionality to load data from a flat file. The target table has a primary key of id. ...
1
vote
1answer
39 views

Get 10th and 90th percentile by customer

I have a table which contains customers and scores (based on different factors, irrelevant in this case; a customer can have multiple scores), which looks like this: customer_id | score | ...