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)

5
votes
1answer
163 views

Improve SQL query

I'm looking for ideas how to speed-up my query which is based on two tables only. I've added indexes, run vacuum. Tables: CREATE TABLE vintage_unit ( id integer NOT NULL, vintage_unit_date date, ...
2
votes
1answer
467 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
2
votes
0answers
485 views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
4
votes
1answer
230 views

How do I monitor PostgreSQL WAL shipping if I'm not using streaming replication?

We have a fairly simple setup to replicate from our on-premise master PostgreSQL database to our presentation tier in AWS. We're using WAL shipping using the archive_command setting. Basically the ...
1
vote
1answer
102 views

Why are query results shortened in pgAdmin 1.18.1?

I've recently installed pgAdmin 1.18.1 and noticed a strange thing: Long json query results are shortened to 256 symbols and then ' (...)' is added. Could someone help me disable this shortening?
3
votes
1answer
542 views

How to change temporary directory for pg_dump?

I'm running out of space from my harddrive which has a 18GB PostgreSQl databse. When I try pg_dump to a different drive, PostgreSQL creates temporary files on the disk it's dumping from, so I run out ...
2
votes
1answer
673 views

Return ResultSet with column names from postgresql Stored procedures

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
0
votes
2answers
155 views

Database design for holding read/unread content state

In forum application I have member table and post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns: ...
0
votes
1answer
454 views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
0
votes
0answers
69 views

set up proper role/group acces on databases and schemes

On my postgresql server I'm struggeling / finding the best practice for a proper role/group setup. I have a number of clients on one machine; each client has its own role: client1; client2;client3 ...
0
votes
1answer
1k views

Howto disable Postgres listening on TCP?

Simply to avoid many problems in the first place I do not want my postgres server program to accept/listen to anything from any network (i.e. TCP/IP 4/6) connections. My setup is a Postgres 9.1 on ...
0
votes
0answers
179 views

Unable to push table from pgadmin to heroku

I have tried https://devcenter.heroku.com/articles/heroku-postgresql#local-setup but heroku pg:psql keeps returning "not found" heroku pg:psql app::databaseName returns The local psql command could ...
0
votes
1answer
81 views

Nullable One To Many w/Join Table - Indexing

I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables users user_id (pk) user_name user_dob ... ...
3
votes
1answer
894 views

Can column name be “Group” in Postgresql or in any databases

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax ...
1
vote
1answer
26 views

Change propagation among connections

If I update a table using one connection and issue a query in another connection immediately after that, is there a chance that I can't see the changes even if it's extremely unlikely?
2
votes
3answers
585 views

A way to reference the ID in a multi insert transaction? (postgres)

Assuming table "entity.eid" is auto incrementing, I want to be able to reference the autoincrement value assigned later in the same transaction. The way I have been doing this is by doing multiple ...
2
votes
0answers
78 views

How can I use an environment variable in a Postgres function?

Let's say I have a windows user environment variable SOME_STRING. Is it possible to use its value in a PL/pgSQL function? If not, do I need to use an untrusted language (like PL/pythonu) or can I ...
0
votes
1answer
895 views

Tool to synchronize PostgreSQL DDL and data databases [closed]

We have several developers who each have a local database for testing. We often make DDL and/or data changes to the master database that we need to replicate to each development machine. The framework ...
1
vote
0answers
55 views

using separate schemas or databases

For my web application with a postgresql backend I'm now looking into schema's. As I switched from MySQL to Postgresql(because of (amongst others) the proper geospatial extension) I was not aware of ...
0
votes
1answer
69 views

Query Pane Docking in pgAdmin3

I just started using pgAdmin3. I do not like having to have a new window when I want to run ad hoc SQL queries. Is there a way to do dock it within the top level app and actually show in the main ...
1
vote
0answers
199 views

postgresql multi column unique or check constraint

First a word of pleading. To say I am a newbie to postgresql would be an insult to the newbies! I have been working on this for only a day or so. I have found several questions on this site, and ...
1
vote
1answer
639 views

Restoring PostgreSQL 9.1.9 backup with 9.3.0 results in error [closed]

How can I resolve the following errors in 9.3.0: psql mydb < ~/mydb_2013-10-25.backup.tar results in ... ALTER TABLE ALTER TABLE ALTER TABLE ERROR: could not open file "$$PATH$$/2309.dat" for ...
2
votes
0answers
192 views

PostgreSQL Change owner of all tables under a specific schema

I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner Is there a way to accomplish that?
0
votes
1answer
141 views

Best read performance when every query is known, and the data doesn't change

I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or ...
1
vote
1answer
197 views

PostgreSQL 9.2 number of days in a tstzrange?

How can the number of days contained within a range be found? For example, with these timestamp ranges, get these (integer) number of days: tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
3
votes
1answer
113 views

PL/pgSQL functions and optimizations fences?

I am newbie on DB stuff. And my DB knowledge is very old (around MSSQL2000). All what I remember is, the stored procedures are faster than ordinary queries because it avoid query compilation and ...
1
vote
2answers
159 views

to use full text search or not?

In my web app, I have a web page for admins that displays the following fields: user id (numeric) device id(numeric) device description (text) cell phone number (text) user name (string ... but ...
2
votes
0answers
49 views

Evenly sized partitions in Postgresql

All, My database has several years worth of temporal data that I am currently partitioning my the month. The problem is that some months there are hundreds of millions of records while other months ...
0
votes
0answers
153 views

Windows Multicorn Installation [closed]

Has anyone had any luck installing multicorn for postgresql on windows. I'm not to familiar with python. I've installed pgxnclient but I get the following error when I run pgxn install multicorn ...
7
votes
2answers
3k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
0
votes
0answers
130 views

Data migration of specific data from one DB to another

I am new to this site, I have one major question of data migration, I apologize before hand if the question is too silly. I have two data base on one server, I have one table with the same schema on ...
2
votes
1answer
487 views

pg_dump custom format between postgres versions

I was wondering how stable the custom pg_dump format is between Postgres versions. Can I for example restore a custom format dump created with Postgres 8.x in postgres 9.x? Background: I am building ...
1
vote
1answer
131 views

Get maximum from aggregated values in a query

I have a table details it has a column kills and a column player_slot and a column match_id. I want to find the maximum difference between the SUM of kills for player_slot 0-4 and 128-132. Can someone ...
2
votes
1answer
378 views

Why Django session table grows on PostgreSQL

I use PostgreSQL 9.1.6 via PgBouncer 1.4.2 in my Django 1.4.8 project. django_session table grows all the time. VACUUM does not help. VACUUM FULL runs for hours. Only TRUNCATE helps usually. The ...
0
votes
1answer
271 views

How can you have a combined primary key for one table?

I have exactly the same question as posted here. But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so ...
1
vote
2answers
85 views

Postgres: What happens if your slave DB went down temporarily?

I have a master DB that is replicated to a slave DB through binary replication. What happens if this slave DB were to go offline for a period of time? Would it eventually catch up with the master as ...
0
votes
1answer
429 views

Bulk loading to partitioned table

I am designing a database application which will be using PostgreSQL 9.3. My question is regarding bulk loading a CSV to a partitioned table. I have done a lot of research, but I'm still unsure of ...
0
votes
1answer
67 views

Can Postgres raise warnings if joins are inconsistant with foreign keys?

Like most relational databases, PostgreSQL provides foreign keys as a means of providing referential integrity CREATE TABLE job ( id serial NOT NULL PRIMARY KEY ) CREATE TABLE jobtuning ( id ...
3
votes
1answer
447 views

How to take pg_dump of a very large postgres database?

I have a production database server running Postgres 8.4 on Cent OS 6.5.The volume of data has increased recently and within few one month database now has 18500 tables amounting to approx 350GB ...
0
votes
1answer
336 views

Simple PostgreSQL lookup table is inexplicably slow

I'm trying PostgreSQL 8.4.14 for storing triples, pieces of data of the form (String, String, String). For speed, I'm not repeatedly storing strings but rather using two tables: main table triples ...
0
votes
3answers
530 views

Performance of primary key IDs with gaps (but in sequential order)

I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e: 154300000 283700000 351300000 464200000 ...will ...
0
votes
0answers
153 views

PostgreSQL 9.2 (PostGIS) performance problem

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table: CREATE TABLE sometable ( gid serial NOT NULL, geom ...
2
votes
1answer
3k views

PostgreSQL isn't listen on 5432 in Windows 7

I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented: listen_addresses="*" port=5432 And this is the ...
2
votes
1answer
164 views

PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'. Various revisions of this SQL isn't constraining '-/+infinity' input: CREATE TABLE bill ( id ...
2
votes
1answer
280 views

How to solve 'tuple concurrently updated' error?

The following query is performed concurrently by two threads logged in with two different users: WITH raw_stat AS ( SELECT host(client_addr) as client_addr, pid , usename ...
1
vote
1answer
541 views

When will PostgreSQL execute archive_command to archive wal files?

I use the following archive_command archive wal files ,suppose I have touched the file archive_active already.and I want to konw when will PostgreSQL execute archive_command to archive wal files? I ...
0
votes
1answer
308 views

What db permissions are needed for pgAgent user?

I have successfully set up pgAgent daemon (running on the same server as Postgres 9.3). I would like to restrict permissions on pgAgent. Created 'pgagent' login role, and granted it (via group role) ...
0
votes
0answers
164 views

Postgres binary replication: Standby takes a long time to start

I am using the Postgres binary replication tutorial, and have rsynced all the data files from my master to standby rsync -av --exclude pg_xlog --exclude postgresql.conf data/* ...
1
vote
1answer
246 views

Design DB for users with different information fields?

Let's say I want to register all faculty of a university and they are in different fields and have different resume information. Here we have some common fields and some field specific fields. for ...
0
votes
1answer
182 views

use function arguments or variable in postgres function

I want to set the default value for the column building and floor in my table points in the below function called test(b,f) Create or replace function test(b text, f text) returns void as $$ Begin ...