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)

2
votes
1answer
10 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
1answer
11 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
9 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 ...
0
votes
0answers
15 views

Implementing a functionality for processing heavy insertion

I use PostgreSQL 9.1 The scenario is: I receive heavy insertion into Table1 (about 100 rows a sec). For each new entry, I have to check it with the previous and next ones (check if those items are ...
2
votes
0answers
17 views

In Postgres, how to get a list of current defined savepoint?

I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection. To be more precise: I'd ...
0
votes
0answers
26 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
1answer
66 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 ...
0
votes
0answers
12 views

PostgreSQL replication with periodic log shipping

I'm using Windows Server 2008 R2 with 64-bit PostgreSQL 9.2 on both test machines. What I wish to achieve is that one server will be a master and other will be a slave. This is just for tests, finally ...
5
votes
1answer
173 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 + ...
0
votes
0answers
15 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 ...
2
votes
1answer
25 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 ...
1
vote
1answer
25 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 ...
0
votes
0answers
20 views

What does GID stand for in PostgreSQL [on hold]

Regarding the to the GID column in a PostgreSQL table, what does the abbreviation (assuming it is an abbreviation) GID stand for? EDIT: This might be specific to the PostGIS extention. After ...
1
vote
0answers
19 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 ...
1
vote
0answers
12 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
2answers
39 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 ...
0
votes
1answer
29 views

How can I automatically add a foreign key to a table on select which is stored in an associated other? [on hold]

I have a database with some tables among wich these 3 : organizations organization_id PK teams team_id PK team_organization_id FK users user_id PK teams_users tu_id PK ...
0
votes
1answer
35 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
32 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 ...
1
vote
0answers
25 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 = '*' ...
0
votes
1answer
16 views

psql: could not connect to server: No such file or directory

The error in its entirety reads: psql: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket ...
0
votes
1answer
22 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
0answers
22 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 ...
1
vote
0answers
12 views

create trigger hangs

I have a trigger function that just copies inserted values to another table. Then, each night I need to drop and recreate this trigger for some export operation. --trigger function CREATE OR ...
0
votes
1answer
23 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 ...
0
votes
3answers
45 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
11 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
3answers
28 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
2answers
57 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: ...
0
votes
0answers
31 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 ...
0
votes
0answers
29 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
31 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
1answer
74 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 ...
1
vote
1answer
31 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 ...
1
vote
0answers
13 views

How to restore everything (including “postgres” role) from pg_dumpall backup

I have a postgres 9.1 server running and I do backups with pg_dumpall -c -f /nfs/backup/fulldump.sql I'm testing restoring the backup to another server with psql -f /nfs/backup/fulldump.sql ...
1
vote
3answers
34 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 ...
0
votes
2answers
30 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
0
votes
1answer
23 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
29 views

DB design, when to split out into schemas

I am building a storage solution to store important themes/concepts referenced in books. At a high level the system will contain one or more catalogs and each catalog will contain one or more books. ...
0
votes
1answer
26 views

Useful guide & FAQ for Application+SQL [closed]

Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know ...
2
votes
3answers
39 views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...
1
vote
2answers
34 views

PostgreSQL get first letter of each word (except…)

I don't think I drank enough coffee today, but stuck on this for some reason. Trying to get the first letter of each word, except a few stopwords. Example "The United States of America" -> "USA" ...
2
votes
2answers
25 views

How to tell which indexes are not being used

Postgresql 9.3 Debian 7 I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they ...
0
votes
0answers
13 views

Hstore or multiple columns for this scenario?

I have a table called Template which needs quite a lot of attributes in order to describe the template itself. Most of the attributes are boolean, such as: template_requires_images, ...
0
votes
1answer
35 views

hierarchical retreival of records in postgresql

I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it. PRODUCT MASTER ...
1
vote
0answers
82 views
+100

Multischema view for postgres , querying multiple databases

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 ...
0
votes
0answers
26 views

Server-Hoster for game [closed]

Im currently working on a Multiplayer-Strategy-Game (C++ 11). It's pretty much like a mix of Browsergames like Travian and Total War games. I have a hexagonal grid, on which your armies move. A game ...
2
votes
3answers
88 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 ...
0
votes
0answers
6 views

Using range types (with GiST index)

Say I have 2 tables with a PostgreSQL range column: CREATE TABLE events ( id serial, during tsrange ); and CREATE TABLE storms ( id serial, during ...
0
votes
1answer
20 views

Row-level subquery caching?

I have a query that uses a CTE which contains millions of rows. I plan to call this query many times, and the CTE returned each time will have most of the same rows. Is it possible for me to cache the ...