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)

3
votes
1answer
40 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
146 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
27 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
30 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
42 views

pgbouncer cannot connect to multi-homed server [on hold]

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
45 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
80 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
27 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
27 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
33 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
64 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
50 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
47 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
30 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
46 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
104 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
26 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
45 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 ...
0
votes
1answer
36 views

How to print to console in SQL?

I'm using PostgreSQL And I want to print a message to the console. If I use plpythonu I use plpy.notice If I use plpgsql I use raise notice but how do I print when the function is pure SQL? using ...
3
votes
2answers
104 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
3
votes
1answer
43 views

Locking in Postgres for UPDATE / INSERT combination

I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once. The user needs to be able to redeem a coupon, which will insert a row into the log table ...
1
vote
1answer
30 views

Should database directory and file access be restricted to only the db user?

I don't know exactly how to word this question and I haven't been able to find any specifics on it so apologies if it's a duplicate. I know that it's standard practice to have database server ...
2
votes
1answer
110 views

ERROR: no schema has been selected to create in

I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not ...
0
votes
1answer
27 views

postgres: WAL files not being deleted during PITR

I have got an issue with setting up PITR on my postgres server: WAL archiving and restoring works quite well, as well as restoring to a specific point in time. The issue however is, that if i do a ...
4
votes
1answer
80 views

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
2
votes
0answers
18 views

How do I keep a postgres table up to date using instagrams api?

I want to update a table in postgres with the response I get from the instagram api. I saved the response as a json file and imported it using navicat but when I saved the import job in navicat it ...
1
vote
1answer
28 views

PostgreSQL - pgAdmin - path to database

I am new to PostgreSQL and I can not found info about this. I am using pgAdmin III and my databse files are kept in instalation directory of PostgreSQL. So far so good. But now, I need to create huge ...
2
votes
1answer
34 views

PostgreSQL vagrant VM cannot create extension

I had some problems during installation of PostgreSQL and I eventually decided to go for a VM solution managed with vagrant, as explained in postgresql wiki. (At the beginning my motivation was just ...
2
votes
0answers
32 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
3
votes
1answer
29 views

Single table or multiple table design when records have a clear distinction but are queried together?

Let's say I have the following types of recurring data (all types periodically come in). Post (Title, Summary, Text, Timestamp) Image (URL, Caption, Timestamp) Event (StartDate, EndDate, Title, ...
5
votes
1answer
72 views

Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many ...
3
votes
1answer
39 views

RETURN TABLE is converted to RETURN SETOF int in Postgres function

In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE: CREATE OR REPLACE FUNCTION test( IN parm1 date, IN parm2 interval) ...
4
votes
1answer
40 views

Select a count with some conditions

Using Postgres 9.4, I have a table page_views which has a page column. I'd like to select all the distinct pages and have a count before them and sort them descending. I'd also like to have a minimum ...
3
votes
0answers
46 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently?

This answer motivated think again my following tables but I am still uncertain how to store statistic data for quick selection. I think it is well done with partial indexes because only a subset of ...
1
vote
2answers
39 views

Postgresql: Insert trigger function fails on partial insert statement

We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test ...
2
votes
1answer
106 views

Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table. Does PostgreSQL support this to any level? That is, can I add/drop columns from a table ...
-2
votes
1answer
29 views

Like operator in postgresql [closed]

how to check the reverse of the original word using like operator? For Eg: victoria state------state victoria This is the query i tried: SELECT DISTINCT ON (t1.name) ...
8
votes
1answer
70 views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
3
votes
1answer
49 views

How to get the difference in days between 2 timestamps in PostgreSQL

I have a table named accounts. I am interested in retrieving those accounts whose status has not been updated since more than 10 days. I thought about this query and it seemed to work but I am not ...