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)

0
votes
2answers
76 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
1
vote
1answer
15 views

Calculate value differences of rows before and after bounded by multiple referenced rows

Supposed I have a user travelling table like this: id | start_time | end_time | location | ----+--------------+------------+------------+ 1 | 1 | 2 | Loc_A | 1 | ...
0
votes
2answers
18 views

Is there a way I can use currval() without selecting it more than once?

I want to be able to use the current value of an auto ID by giving it some kind of name and just referring to it (instead of calling SELECT for each record): Requests table has a serial column named ...
1
vote
1answer
74 views

Recursive calls in stored procedure and temporary tables

I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to ...
1
vote
1answer
31 views

Wildcard subnet mask for IPv6

To allow any host to connect to PostgreSQL we can use the IPv4 wildcard subnet mask: 0.0.0.0/0 I need this to setup my PostgreSQL's pg_hba.conf file correctly. What is the IPv6 equivalent?
1
vote
1answer
23 views

Organizing Database Objects in a Database that are not part of the systems original Database Objects?

I've been working at a place for about 2 years now with a series of heterogeneous legacy database applications, which are tied together using 3rd party Microsoft Access databases (*.mdb). Soon we ...
0
votes
1answer
13 views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...
6
votes
2answers
754 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 NOTNULL to ...
1
vote
0answers
13 views

Meaning of query in pgbouncer.log

I could not find any documentation about pgbouncer log, I'm assuming it has the same meaning as the SHOW STATS ? req/s - number of requests since last stat period in b/s - Bytes per sec received ? ...
1
vote
1answer
25 views

How does MS SQL Server authenticate MSDTC connections when using SSPI?

I'm trying to get SSPI authentication to play well with two-phase transactions via MSDTC, using PostgreSQL with psqlODBC. I can't figure out how it can work securely - so I'm hoping someone can ...
0
votes
1answer
18 views

Does PostgreSQL write first to WAL and then to data files?

This is regarding the synchronous_commit in Postgres. The synchronous_commit documentation says: Specifies whether transaction commit will wait for WAL records to be written to disk before the ...
0
votes
1answer
15 views

Creating unique constraint to be validated from input

I have this table name tbl with this table definition: create table tbl ( id serial primary key not null, counter integer not null default 1, ref_id integer not null, date_booked date ...
0
votes
1answer
312 views

Authentification failure for PostgreSQL server (9.3)

I can currently not connect to my Postgresql server (9.3) from pgadmin (Running on Ubuntu 14.04). The server according to pg_lsclusters is on port 5433. Ver Cluster Port Status Owner Data ...
1
vote
2answers
25 views

Building a Trending query

Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every ...
4
votes
2answers
2k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
2
votes
1answer
48 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
0
votes
0answers
6 views

difference between pg_dump command's --column-insert and --attribute-insert

Background: I'm trying to create a dump file that only contains the data in my database. I would like it to create the data with the INSERT command. In reading the manual, ...
1
vote
0answers
17 views

Postgresql service frozen and can restart properly

I have windows server 2008 R2 and postgresql 9.3 Server is still for testing so I'm alone and only have a .net application doing consult with npgsql to the db, mostly doing some store procedures and ...
1
vote
0answers
11 views

Pgpooladmin pcp commands does not work

After first installation of pgpoolAdmin I got an error e1002 with message "pcp_node_count command error occurred" Does anybody know why is this happen and how to fix this? Little update: just ...
0
votes
1answer
29 views

How do I go about easy table input without creating an application?

I am converting all my applications from MS Access to PostgreSQL and I have a few personal-use databases. These MS databases have text fields as the primary keys, so I can type the name of something ...
1
vote
1answer
69 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
0
votes
0answers
16 views

Does amazon rds PostgreSQL require vacuum?

It isn't clear if auto vacuum is running in rds. Thanks!
1
vote
1answer
28 views

How to design a versioned database with PostgreSQL?

I am planning to create a back-end aka an API. The database will be PostgreSQL. I would like to discuss the following scenario: How can I have multiple versions of the same database? I would like to ...
3
votes
2answers
32 views

How can I fake inet_client_addr() for unit tests in PostgreSQL?

I have a simple stored procedure whose return value depends on the value of inet_client_addr(). How can I override inet_client_addr() for the purpose of unit tests when testing my stored procedure? ...
4
votes
1answer
2k views

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?
2
votes
1answer
46 views

MS SQL - export tables to format compatible with PostgreSQL

We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing. MS SQL Server has an export function but the only one that looks like it would work would be to export ...
0
votes
0answers
12 views

Postgresql startup: runuser: cannot set groups: operation not permitted

I'm having trouble starting a postgres database. I'm running 9.2 on Centos 5.4 (for compatibility reasons). Here is what I have done so far: Ran postgres initdb for the directory /opt/database ...
0
votes
0answers
16 views

Starting up postgres on Ubuntu for the first time

I've installed postgres on ubuntu, created a node and now I'm trying to start up that node as per the instructions: postgres --coordinator -D /usr/local/pgsql/data -p 1281 But I get the following ...
1
vote
1answer
315 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 ...
1
vote
1answer
5k views

How do I restore a plain text postgres .backup file

I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is ...
1
vote
1answer
161 views

How can I get data from broken PostgreSQL server?

I have installed CentOS on a virtual machine and installed the latest version of PostgreSQL server 9.3. I have added PostGIS extension and some data. For some reason, the PostgreSQL service now fails ...
3
votes
1answer
49 views

Query with dynamic ranges in PostgreSQL based on status

We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have ...
4
votes
3answers
5k views

Why won't PostgreSQL 9.3 start on Ubuntu?

I have successfully installed PostgreSQL 9.3 from the APT repository on 2 VM's running Ubuntu 12.04 and 13.04...however, I cannot get it to install properly on my host machine running Ubuntu 12.04. ...
4
votes
2answers
6k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
1
vote
1answer
43 views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
0
votes
1answer
26 views

PostgreSQL: how do I query over a column with a custom range?

I'm trying to write a query over a string column called grades_served that can have values such as k-5 1-5 5-9 The query could be for a specific grade or it could be a range with a start and end ...
0
votes
0answers
29 views

postgres explain plan with giant gaps between operations

I have a postgresql explain plan that seems to have large gaps between the join, sort & filter processes and can't explain (sorry) it. The query is taking an inordinate amount of time and the ...
1
vote
1answer
17 views

Pgpool - semanage config files - which filetype should I choose?

Reffering to problem with pgpool installation : Problem with pgpoolAdmin installation I had a problem with non running pgpooladmin(installation files permission was wrong). I must change file label of ...
3
votes
0answers
30 views

Investigating rollbacks in Postgres

A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have ...
1
vote
1answer
25 views

How to configure read transactions to never cause other write serialization transactions to fail

I am protecting critical transactions with application mutexes and SERIALIZABLE. I presume that this makes these operations as acid as possible, but now I'm concerned that reads might cause failures ...
1
vote
1answer
28 views

Detailed optimal column order with large text and bytea

I read this excellent answer on optimal column ordering and noticed the summation: Generally speaking, if you put the 8-byte-types first then the 4-byte-types and the 2-byte-types last you can't ...
2
votes
2answers
136 views

How many database servers do I need?

This may be a no-brainer to some of you, but I am a newcomer to DBA and this question popped into my head and I couldn't answer it on my own and so I am turning to the experts here. I have a app that ...
0
votes
3answers
70 views

Trigger or Stored Procedure?

Let's say I have two tables, foo and bar: CREATE TABLE foo( foo_id serial NOT NULL, count integer NULL, PRIMARY KEY(foo_id) ); CREATE TABLE bar( bar_id serial NOT ...
2
votes
1answer
835 views

PostgreSQL: How to list all stored functions that access specific table

Introduction: PostgreSQL database with several hundred of stored functions, including obsolete, not used etc. Problem I need to find out all the stored functions that have any relationship to the ...
1
vote
1answer
2k views

Installing and configuring postgresql 9.3 on ubuntu 13.10 using puppet

I'm having some trouble getting postgresql[1] to install with puppet[2] on a blank ubuntu box. I need to install postgresql 9.3 on Ubuntu 13.10. Since postgresql 9.3 is apparently not in the official ...
0
votes
1answer
40 views

Check if data from database is up to date

I'm storing some data from database, and want to check if that data is up-to-date from the database version. What is the best way to do this? My ideas: Getting checksum from the table and comparing ...
0
votes
1answer
48 views

restoring Postgres dump with tablespaces

I'm trying to restore a dump done with pg_dumpall > mybak.sql from a server that has 1 table in a different tablespace that the default one. On the target machine I have recreated the directory ...
5
votes
2answers
5k views

How to view data in pgAdmin III

I can't believe they make it this hard. I am at a loss about how to view the data in my database. Is there an easy way to see what data is in my tables with pgAdmin III? Alternatively, is there a ...
1
vote
0answers
52 views

Does a serialized transaction affect rows written after begin?

I'm trying to determine exactly how a SERIALIZABLE transaction affects rows written after the transaction has begun. I'm using PostgreSQL 9.2. For example, in one of my serialized transactions, it ...
0
votes
1answer
105 views

postgres pgpool2 replicating fails in ubuntu 12.04

I'm trying to configure a pgpool2 loadbalancer (in a Ubuntu 12.04 server) to balance between 2 postgres nodes (2 ubuntu 12.04 servers). The main problem is that, if I create a database using pgAdmin ...