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)

5
votes
1answer
1k views

Schema for analytics table in Postgres

We use Postgres for analytics (star schema). Every few seconds we get reports on ~500 metrics types. The simplest schema would be: timestamp metric_type value 78930890 FOO ...
4
votes
2answers
2k views

Postgres log files not rotating

I have a postgres system with a hot standby server. They use the same config file. I've setup the log rotation to use log_rotation_size and log_rotation_age so that the master server is rotated every ...
2
votes
0answers
403 views

Postgres: PANIC: WAL contains references to invalid pages

I have a slave database that is being replicated from a Master DB. All of a sudden, my slave DB just exited and wasn't able to restart. Here are the error messages when it exits. Similar ones appear ...
4
votes
1answer
6k views

Why aren't unsigned integer types available in the top database platforms?

Databases are usually very customizable with varying data types and custom lengths. It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither ...
2
votes
1answer
3k views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
4
votes
1answer
385 views

Stored procedure deadlocking itself

I have a strange situation, seen from the log: Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. Process 53807 waits for ExclusiveLock on ...
1
vote
2answers
2k views

How can I keep a result set without keeping the transaction open?

The following documentation describes how to see the refcursor returned from a function, here, like this: CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT ...
0
votes
3answers
4k views

How do I install postgresql in Ubuntu?

I'm following these instructions, however I can only get to step 17.2. Despite installing postgresql successfully via the sudo apt-get install postgresql command, upon running initdb -D ...
2
votes
1answer
2k views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for ...
-1
votes
1answer
233 views

Example creating a PostgreSQL XML data type

I am looking for an SQL example where a table is created in PostgreSQL which has a field with data type XML.
2
votes
2answers
5k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
1
vote
0answers
756 views

PgPool is pushing Postgres to 100% cpu usage

I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, ...
2
votes
1answer
1k views

Postgres: “Pivot” unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, ...
1
vote
1answer
912 views

How to do a minor upgrade postgresql 9.3.0 windows to 9.3.1?

What is the recommended way to perform an upgrade from PostgreSQL 9.3.0 to 9.3.1 (minor upgrade) using the Enterprise DB built windows installer? Should I uninstall first or just install over the ...
0
votes
1answer
117 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
3
votes
0answers
340 views

PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in [closed]

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on. The insert trigger works fine and new rows are inserted into the ...
0
votes
1answer
88 views

How can I run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc?

I need to be able to run PostgreSQL databases outside the standard installation directories, ie run the existing postgres or pg_ctl process pointing to the alternative ports, configuration and data ...
1
vote
0answers
207 views

PostgreSQL how to create a scalable location-based query

I need suggestions because I am not good enough. I have a DB in PostgreSQL that runs on AWS (Amazon Web Services). I have a table "user_location" where there are stored the locations of each user ...
0
votes
2answers
492 views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...
2
votes
1answer
2k views

Order by alphabet and then by numbers

I have records like A5 A4 Z1 B2 C7 C1A C11A B1 B4 I want them to be sorted in this manner A4 A5 B1 B2 B4 C1 C11A C7 Z1 using the ORDER BY clause. I want them to be sorted by alphabets ...
13
votes
2answers
7k views

Best way to populate a new column in a large table?

We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
5
votes
1answer
224 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
663 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
996 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 ...
5
votes
1answer
372 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 ...
3
votes
1answer
417 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
1k 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
2k 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
332 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: ...
1
vote
1answer
1k 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
78 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
3k 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
1answer
120 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 ... ...
4
votes
1answer
2k 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
27 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
2k 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
117 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
2k 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
66 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
237 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
343 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
1k 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
1answer
2k 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
158 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
462 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
178 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
273 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
54 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 ...
9
votes
2answers
11k 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
298 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 ...