All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
1answer
29 views

Does PostgreSQL consider how evenly data is distributed on disk when choosing a seq scan?

I have a table items with an indexed column type_id. The table contains about 80 million rows. At the moment the first 5 million (in order of insertion) and the last 3 million have type_id = 8, and ...
0
votes
0answers
36 views

Postgres query taking 1minute to fetch one row

I am using postgres version 9.4 in my application. I have a partitioned table named sql_sent_sms which is partitioned on the basis of primary keys. I have a query which uses join on same table with ...
0
votes
1answer
20 views

Different behaviour between oracle and postgres regarding alter table primary key command

In Oracle 11g, when you write alter table abc add constraint abc_pk primary key (one, two, three); Automatically an index for one, two, three is added. In PostgreSQL 9.5, when you do the same, ...
0
votes
0answers
42 views

Connecting Oracle 11g to Postgres via dblink

I have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow. I've seen ...
4
votes
3answers
48 views

What is the easiest way to backup PostgreSQL and send backups to Google Drive?

I am new with PostgreSQL, So my question is: what is the easiest way to make regular backups and send them to Google Drive. Perhaps, there is a tool that can make it or something else.
1
vote
1answer
33 views

PostgreSQL won't allow remote connections

My Postgres installation won't allow me to connect remotely. I set the listen_addresses field in the postgresql.conf to '*' and added the line host all all 0.0.0.0/0 ...
3
votes
1answer
32 views

Postgres: Preventing objects with same name

I just spent one hour debugging a query which had previously worked but suddenly created strange results. The cause of this bug was that I have a table in one schema which I reference in the query. ...
1
vote
0answers
15 views

How can I upgrade PostgreSQL while using PgQ queue system?

I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then: sudo ...
2
votes
2answers
51 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
1
vote
1answer
25 views

Storing last N samples in a table coming from multiple clients

I want to store some last N (say fixed 50) samples in a table indexed by uuid. They're coming from multiple clients. The requirements of which last N are weak (the use case is to show some recent ...
2
votes
1answer
11 views

In Postgres, how can I calculate cume_dist (relative rank) over the entire table, but only for a single row?

I have a table with the following schema: CREATE TABLE foo ( data TEXT NOT NULL, count BIGINT NOT NULL DEFAULT 1, PRIMARY KEY (data) ); I'd like to get the relative rank of a single entry, ...
0
votes
0answers
24 views

improve performance with schema design, a lot join

I'm making a custom cms system for my client, with postgresql, nodejs ... have problem with slow performance issue, when doing select data . (and it is still on localhost/mac nginx and just few data, ...
0
votes
0answers
4 views

Save relation into postgres function variable to use in CORR() call?

I have two tables, queries and signalsand I want to create a Postgres function to calculate the CORR() for all signals, given a query. I'm trying to simply call CORR() with two variables in the ...
1
vote
0answers
12 views

Efficiently get records with latest timestamp per group [duplicate]

I have a table which roughly looks like this uuid uuid, id id, data text, last_updated timestamp Each row has an associated unique uuid. However, the key of interest is the id (indexed). ...
0
votes
0answers
27 views

Is there any way to reduce pg_largeobject size?

The Backup team are concerned about DB backup since it's huge and taking a long time to back up (physical backup - 300 GB - approx 5hrs). 90% of data is in pg_largeobject. I could remove loid which ...
3
votes
1answer
32 views

Postgres database deadlock issue

I am using Cloudera Manager to monitor and configure Hadoop. I choose PostgreSQL as metastore of HUE - a web portal to submit your SQL to Hadoop. When I submit a SQL, the HUE says the database is ...
1
vote
0answers
15 views

How do I configure Postgresql stop words to reduce to -ism and -ist?

I want to reduce communism and communist to the same stop words. Currently, that's not the way it's working. # select to_tsvector('communism communist'); to_tsvector ...
2
votes
2answers
28 views

Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

It seems to be disallowed to add a Foreign Key constraint to a foreign table. Is there any other way to do this? My two tables have these constrains on the remote server. More specific details: ...
0
votes
0answers
13 views

Diagram conventions and standards for Database modeling pgmodeler [duplicate]

I am using pgmodeler for data modeling in postgres.. Apart from naming conventions are there any other standards for drawing the diagram... Like UML modeling
1
vote
1answer
23 views

array_length if array is empty behaviour

Please explain to me this behavior. This SQL finds elements where ids (type is array::bigint) is empty. SELECT * FROM rises WHERE ids = '{}' -- finds, e.g., 9 rows This SQL finds no rows: SELECT ...
4
votes
2answers
72 views

Select tables on column entry in schema (with many tables)

I have a schema in a PostgreSQL-DB that includes approx. 3000 tables with each having one row/ entry (size, id, geom). I'd like to select all tables which have a size value lower than 20. How can I ...
3
votes
1answer
42 views

Converting SQL SERVER Procedure to POSTGRESQL

I have procedure in SQL Server to print all players of given country. I try to convert it into plpgsql, but I get errors. SQL Server Procedure CREATE PROCEDURE kraj_pilkarze @kraj varchar(30) AS ...
1
vote
0answers
23 views

optimize a window aggregate function

First, the query SELECT tc.numreg, com.numdep, tc.numcom, tc.numiris, sum(tc.conso_climat_reel)/sum(sum(tc.conso_climat_reel)) OVER (PARTITION BY tc.numreg) AS ratio, ...
1
vote
0answers
27 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
0
votes
0answers
11 views

Different insert operations with the same data and cache

I want to perform several different insert operations into a Postgres database. PostgreSQL Version 9.5. Operating system: SLES 12. I have 200000 rows of data. It is the same for every different ...
3
votes
0answers
45 views

Postgresql Filter Slows Query

I've got a query like this: SELECT count(*) FROM acs2014_5yr.geoheader as g JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract) JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id) ...
2
votes
1answer
13 views

Backup a hot standby database (streaming replication) whilst it is in recovery

Synchronous Streaming Replicating is set up between a master and a hot standby database. It seems like a good idea to perform daily backups on the standby database to avoid any performance issues ...
0
votes
0answers
6 views

Export data from Hive to Postgres

I would like to save data that I fetch via Hive on a remote Hadoop cluster to my local machine in a Postgres database. I am currently downloading the data as files but I have no idea on how to ...
1
vote
1answer
35 views

Duplicate rows - how to remove one? [duplicate]

I have a pretty large table (~114 million rows) containing OS MasterMap data. This is freshly loaded data in a new table. When trying to set the primary key, I get this error: ERROR: could not ...
3
votes
2answers
315 views

What function to use with this timestamp?

I have timestamp (stored as BIGINT) format I cannot turn into a human-readable format: 1462975819250 It points to a date in 2016-may-11th. However function to_timestamp() turns this into: ...
1
vote
2answers
58 views

Trigram index for for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
3
votes
2answers
44 views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE ...
1
vote
1answer
39 views

Store table names to reference other tables

I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used ...
2
votes
1answer
24 views

Limited utility in RECURSIVE VIEWS?

In the docs for recursive queries with WITH, you'll find this Recursive queries are typically used to deal with hierarchical or tree-structured data. This seems to be true, but how does this ...
1
vote
0answers
22 views

What are the pros and cons of storing two values in an array instead of two columns?

I have a project that plans to store an away team and a home team. I'm wondering if it is better to store the teams in a single array column like this: {Harvard,Colorado} Or create away_team and ...
0
votes
1answer
16 views

Finding time ranges in multiple rows

I have a table with (suppose) just 2 columns: - fecha (a date) - ignicion (0/1 representing boolean state) The goal is to retrieve which periods of time was ignicion held at '1', for such task I ...
2
votes
1answer
22 views

Table design for ticket sales

I'm designing a database for a ticket sales system in PostgreSQL, where the admin user can create a batch of tickets for a specific event. All tickets for a given event have the same attributes (like ...
1
vote
1answer
31 views

Auto start pgAgent

When my windows server 2008 start, both services postgres and pgAgentstart because are in automatic. But if my db is frozen and I need to restart the postgres service, the pgAgent is also stoped, ...
0
votes
0answers
11 views

SQL query for setting value

I found this example for configuration databse tables: http://stackoverflow.com/questions/10204902/database-design-for-user-settings Can you show me some example for SQL query for getting ...
1
vote
2answers
37 views

Aggregate function to return any (first) encountered value

How do I tell PostgreSQL to return first encountered value instead of an aggregate column? Table "public.cache" Column | Type | Modifiers | Storage | Stats target | ...
2
votes
0answers
33 views

Postgres update query complaining about text value when only NULL values are passed

I am using Postgres 9.4.6. Is there any reason why the below update query would execute successfully here:- UPDATE "Predictions" SET "userid" = c."userid", "Fixture_No" = ...
0
votes
1answer
10 views

postgresql fulltext slow

I am trying PostgreSQL for the first time, but am finding the fulltext search very slow. My database has a table with 5 columns and 5 million rows CREATE TABLE table_test ( id integer NOT NULL, ...
0
votes
0answers
41 views

Postgres wont install on windows 7 64 bit (Failed to load SQL modules into table)

I have been trying to install standard postgres 9.3 onto a windows 7 Machine. every single time it gives the same error: Failed to load SQL modules into table I have tried (from this question ...
0
votes
0answers
20 views

Backup options for database with large objects

I have a database that includes a table with references (as oids) to large objects that have been stored in the database via the JDBC Large Object manager. I need to make regular backups of the ...
4
votes
2answers
206 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
3
votes
1answer
32 views

What permissions are required to return rows from information_schema.schemata?

In postgres (I'm on 9.3.4) what permissions are required to return rows for select * from information_schema.schemata The 9.3 documentation says "are owned by a currently enabled role". Does that ...
0
votes
0answers
20 views

Postgresql: find out all columns that have particular string and count the number

I want to create a function like this: enter table name and a particular text, then return a table that have 3 columns the : table name , column name, the number of that column have the particular ...
2
votes
1answer
53 views

Select where value in row is different and update

I have a large table that includes rows like the following: Query: select * from cartographic_text_vm where fid = 'osgb5000005104723440' order by ckpt; Results: -[ RECORD 1 ...
0
votes
0answers
18 views

PostgreSQL 9.3 overlapping pg_dump commands

I'm wanting to do a pg_dump or pg_dumpall of a database every 10 minutes. Unfortunately the database takes longer then 10 minutes to write out. This post confirms that database dumps produce ...
0
votes
1answer
22 views

Postgresql maintainenace queries fail with foreignkey violation

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...