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)

4
votes
2answers
69 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
121 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
43 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
76 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
24 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
22 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
198 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
0answers
14 views

Postgres where clause compare datetime [migrated]

Hello guys I am new to postgres, I have a table where column is of datatype datetime Which contains records multiple records for a day I want to select all rows corresponding to day How do I ...
0
votes
2answers
27 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
29 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
39 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
17 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 | ...
3
votes
0answers
89 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
29 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
32 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
25 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
23 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
92 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
40 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
42 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
48 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
53 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
148 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
28 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
31 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 ...
0
votes
0answers
43 views

pgbouncer cannot connect to multi-homed server [closed]

I use pgbouncer 1.5.5 on one host, in order to connect to a remote PG 9.4.4 server. The DB name used in pgbouncer.ini resolves to 2 IP addresses: one that does not accept connections, and the other ...
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
46 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
83 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
28 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
36 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
36 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
53 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
26 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
65 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
51 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
38 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 | ...
0
votes
1answer
42 views

How to find similar (but different) names with the same soundex value?

I got the output with the same name and same soundex value, but I want the result which has similar name and same soundex value. Can anyone help me to do this?
0
votes
1answer
49 views

pg_restore warning column X has type unknown

I'm doing a dump from PostgreSQL 9.1 and restoring on PostgreSQL 9.4 and getting this warning pg_restore: WARNING: column "foobar" has type "unknown" DETAIL: Proceeding with relation creation ...
1
vote
0answers
26 views

ANY in postgresql gives weird error

when I do: SELECT ARRAY[4] I get array integer[] ------ {4} and if I do: select * from b where b.ID = ANY (array [4]) That works. Now, something else: select array_agg(4) I get: ...
2
votes
1answer
31 views

Error while restoring Postgres DB Dump

Using below command to restore the database Restoration command: cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will ...
-2
votes
1answer
51 views

No operator matches the given name and argument type(s). You might need to add explicit type casts [closed]

postgres=# select * from json_test; id | data ----+----------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b":["C", "d"]} 4 | { "a":1, ...
1
vote
1answer
45 views

How can I improve this query in PostgreSQL?

I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have counts in them, such as: SELECT COUNT(*) AS count_all, listings.id AS listings_id FROM "listings" ...
5
votes
2answers
110 views

“Recheck Cond:” line in query plans with a bitmap index scan

This is a spin-off from comments to the previous question: Postgres 9.4.4 query takes forever Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
1
vote
1answer
29 views

Postgres - window function rank and count

I have some tables that track athletes time around a track: competitions, competition_users and competition_user_sessions I am constructing a query that extracts the current rank and total no of ...
1
vote
1answer
27 views

How to grant access to IP range in Postgres?

I would like to configure my Postgres DB to be accessed by any IP 10.8.101.*. I know I have to create a rule in pg_hba.conf but I'm not sure what it should look like. My LAN IP is currently ...
2
votes
1answer
51 views

“ERROR:malformed array literal” when using json_to_record with a JSON array element in Postgres 9.4

This illustrates the issue nicely: When column b is of type text, and not an array, the following works: select * from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') as x(a int, b ...