PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

1
vote
1answer
54 views

PostgreSQL: Another seq scan where an index scan is expected

Here's the query: SELECT DISTINCT ON(points.track_id) participants.contest_number, points.track_id, points.lat, points.lon, points.alt, points.timestamp, points.h_speed, ...
8
votes
2answers
4k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
1
vote
1answer
102 views

Returning rows in PostgreSQL with a table called “user” [duplicate]

I have a table called user I have some SQL that is select User.* from User This fails because User is a reserved word however, I was under the impression that Postgresql didn't care about the case ...
3
votes
2answers
399 views

Is Data Warehousing possible in MySQL and PostgresSQL [closed]

I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I ...
0
votes
1answer
26 views

Change permissions of postgres copy to exported file

I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here. The original question is: ...
2
votes
1answer
35 views

Can't list users - “select * from user” returns only one row with one column

I have a heroku postgres add-on database with a user table. However, when I attempt to select * from user, all I get is: current_user ---------------- rtsjlhdfptlaqd (1 row) The table name is ...
2
votes
1answer
34 views

Diagnosing the error in a Postgresql to Redshift migration through psycopg2

I am running a job class that contains the following: A Postgresql connection that can issue SQL statements A Redshift connection that can do the same an S3 connection to function as an intermediary ...
0
votes
0answers
24 views

Postgresql Full Text Search sequence of strings

What I'm trying to do is that i have a bunch of html documents in which I need to search for certain groups of strings. I did build a ts_vector for all htmls but I seem not be able to find out if it ...
0
votes
0answers
22 views

Best way to setup postgresql load balancing and failover?

Ok so I know that my db is going to be postgres and I want to ensure that it is able to handle read/write queries in the future (instant messaging application so lots of small writes and a lot more ...
1
vote
1answer
74 views

Best way to add Foreign Key to massive table?

Say I have 4 tables: arrival (~100 million rows), departure (~100 million rows), airport (~30 million rows), airplane (small). arrival and departure each have 2 ForeignKeys: one to airport, one to ...
2
votes
3answers
138 views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
6
votes
4answers
166 views

Store millions of rows of denomalized data or some SQL magic?

My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know! I have a problem whereby I need to lookup or calculate ...
0
votes
0answers
45 views

Reset password for 'postgres' on v9.3 (Win Srv)

I'm trying to reset the password of the user 'postgres' on a postgres 9.3 installation, but have been unable to do so... I've tried every combination in pg_hba.conf I could find online (ex. 'host all ...
1
vote
2answers
79 views

How do I maintain high INSERT-performance on PostgreSQL

I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database. At the core is a table (partitioned by month) which contains a row for each measurement point: ...
4
votes
0answers
159 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
5
votes
1answer
187 views

Rounding issues?

Try this: create table test (f float); insert into test values (330.0); commit; select (8 + 330.0/60)::int; --14 select (8 + f/60)::int from test; --14 select (9 + 330.0/60)::int; --15 select (9 + ...
1
vote
1answer
34 views

What's the best solution for offline db syncing

I'm building a system which requires a master db (BIG master) that contains things like accounts, balances, actions, etc. This db then needs to be accessible at a physical place, like a fairground ...
2
votes
1answer
32 views

Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection?

I'm doing some work with PostgreSQL 9.3 using the psycopg2 database API. I have the DB API set in the minimum isolation level ("autocommit" mode), and am managing my own transactions directly via ...
0
votes
0answers
22 views

What to do if pg_cancel_backend and pg_terminate_backend return False?

I cannot end/terminate the queries shown in the screenshot. The functions are not running anymore (usually they cause a high load on the server and the server was fine). I have tried both ...
0
votes
3answers
34 views

postgresql cannot drop a table when autovacuum holds lock on it

Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE ...
1
vote
0answers
22 views

Postgres_Failover Not Working

I was able to configure and Replicate between master and Slave ,so when we insert anything on master db it will automatically replicate to slave, Now,requirement is that if we shutdown master can we ...
3
votes
1answer
675 views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...
1
vote
0answers
27 views

Getting vacuumdb :invalid page header in block error while vacuuming

I am newbie to PostgreSQL and using version 9.2.Disk space for data folder is full causing DB to stop. When i am trying to perform a vacuum to reclaim space, i am getting below error. vacuumdb: ...
2
votes
6answers
58 views

Need separate columns that calculate percentage

I have the following table: NAME Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage Alex 2 33% Bob 2 33% Roger 1 16.6% Tim 1 ...
1
vote
1answer
129 views

How will the order of operations in this query affect my results?

I've written a query, and I'm kinda sure that it does what it want. I'm thinking, "huh, I wonder if I know how Postgres works?". I'm going to list a specific assumption about this query below, and ...
1
vote
0answers
36 views

listen_addresses does not respect postgresql.conf

I have PostgreSQL 9.3, and I'm trying to set it up on CentOS. One part that has been driving me nuts is the listen_addresses setting. I set it to * inside postgresql.conf: listen_addresses = '*' ...
4
votes
2answers
3k views

PostgreSQL Sequential Scan instead of Index Scan Why?

Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
0
votes
3answers
55 views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
0
votes
1answer
41 views

Translating MySQL query to PostgreSQL

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error. MySQL: SELECT ticket.queue_id, queue.name, ...
1
vote
1answer
41 views

Upsert statement never completes, infinite loop?

Edit: The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a ...
6
votes
3answers
531 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
0
votes
1answer
7 views

Determining which database instance makes biggest IO

Assuming that I have a dedicated server on which I am running multiple instances of mysql and postresql servers. How without iotop determine which instance in particular time (proc/pid/io shows data ...
0
votes
1answer
26 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
0
votes
1answer
96 views

What is the best approach to process huge amount of data insertion efficiently

I'm dealing with a HUGE amount of data entry. I need to know the best practice to insert/update table records after new entry inserted in another table. By huge i mean we get more than 2 million ...
0
votes
0answers
26 views

API on top of postgresql

I'm looking for one or several API's that can be put on top of postgres to make data input more dynamic. What I'm looking for is something that can handle a wider range of protocols and differently ...
0
votes
2answers
103 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
25 views

Separation of users columns in another table

I've got a quite a lot of columns on the users table and I have been cleaning it up recently. Now since I'm searching over the users I figured it would improve performance if I separated verification ...
1
vote
1answer
43 views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
0
votes
0answers
33 views

Speed up sorting in this query

I'm trying to optimize the following plan and noticed that PostgreSQL spends a lot of time (relatively speaking) on sorting before the merge joins. Is there a way (using indexes, cluster the table, ...
0
votes
1answer
27 views

How to predict the growth of a PostgreSQL database

I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the ...
0
votes
0answers
37 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
1
vote
3answers
55 views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
1
vote
2answers
1k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
0
votes
1answer
18 views

Type of an id field that references another id field

I'm on postgresql. I have a table user (id serial not null) and another table client(id) i need to create a : foreign key client(id) references user(id). What the client(id) type should be? Int or ...
0
votes
1answer
230 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
4
votes
2answers
2k views

Install PostGIS v2 for PostgreSQL v9.2

I have installed the latest stable version of PostgreSQL v9.2 and I want to install the PostGIS v2 extension. I've been searching for it for 3 days now, however can't find it. I did however find ...
5
votes
5answers
5k views

SQL Query Formatter

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux ...
2
votes
0answers
7 views

Active record relation AND filtering [duplicate]

(Edit: since reposted to dba.stackexchange.com and answered there). Suppose I have 3 models: Post, PostCategory and Category. I'm implementing some kind of page with filters where I can filter posts ...
0
votes
1answer
42 views

What's the difference between INSERT … SELECT and SELECT INTO

Let's say I have a table item and an other one versionned_item. From time to time, I want to copy data from item to versionned_item. What I did is WITH subset AS (SELECT ...) INSERT INTO ...
0
votes
0answers
10 views

Multischema view for postgres , querying multiple databases. [duplicate]

Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...