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
1answer
14 views

Statistical probability of an incomplete transaction committed by Postgres

tldr After spending time with bad databases which I will not mention, I came to question even the point of a database. I knew that they could serve a purpose to keep permanent data organized, but ...
1
vote
0answers
19 views

Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this SELECT * FROM ( SELECT ...
0
votes
1answer
17 views

Update new column with value from old columns for all the rows

I've got an odd db design in place that was here when I got on my new work space. We have a column named params which contains some string data which appears that it belongs in more than one column, ...
4
votes
1answer
38 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 You can refer to this question to see how the query was produced. In the fiddle ...
1
vote
1answer
34 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?
0
votes
3answers
20 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 ...
0
votes
2answers
26 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
20 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 | ...
1
vote
0answers
16 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 ? ...
0
votes
1answer
24 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
16 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
0answers
7 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
2answers
30 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 ...
2
votes
1answer
59 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 ...
1
vote
0answers
16 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
0answers
17 views

Does amazon rds PostgreSQL require vacuum?

It isn't clear if auto vacuum is running in rds. Thanks!
1
vote
1answer
27 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 ...
1
vote
1answer
29 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
34 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? ...
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 ...
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 ...
0
votes
0answers
13 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 ...
0
votes
0answers
31 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 ...
3
votes
0answers
31 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
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 ...
1
vote
1answer
45 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 ...
2
votes
2answers
137 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 ...
1
vote
1answer
30 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 ...
0
votes
1answer
27 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
18 views

How does ActiveRecord handle hard drive block size in PostgreSQL?

I know that hard drives are split into blocks of bytes aka chunks, that if used less then the total amount of the block the remaining space is an empty waste of unused space. What I'm curious about ...
1
vote
1answer
27 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 ...
3
votes
1answer
50 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 ...
0
votes
1answer
28 views

PostgreSQL : inconsistent result when using WHERE clause on VIEW

This is my VIEW declaration CREATE OR REPLACE VIEW inv_groups_vw AS SELECT gg.id, gg.sku, gg.name, gg.description, CASE WHEN count(i.*) = 0 THEN '[]'::json ...
1
vote
1answer
28 views

Problem with pgpoolAdmin installation

I got a problem with permissions during installation of pgpoolAdmin(newest version). The problem is when I get to window with main config that should be all okay to move to next installtion step. The ...
2
votes
1answer
48 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 ...
1
vote
0answers
26 views

Is a commit or abort more costly for a prepared select?

I have found that selects will actually generate WAL records upon commit. Are there other costs to committing a select? Are there costs due to an abort? If so, which has more? What would be the ...
3
votes
0answers
37 views

Will a serializable read only deferrable select block?

I'm unsure from the documentation and wiki if a serializable read only deferrable select, a SELECT preceded by SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE, will block a ...
0
votes
0answers
16 views

Postgres sync database

I have two postgres databases. Master = master_database Slave = database All initial changes are made to master. Upon admin approval, the version is updated, and I want all changes to be copied ...
1
vote
1answer
37 views

Adding a group of rows in postgresql based on column id

I have a table with a sets of rows such as displayed below: Qty Equip_ID 21487.92;"Load Center-6" 99272.59;"Load Center-10" 68088.61;"Load Center-11" 21821.5;;"Load Center-6" 102165.2;"Load ...
0
votes
1answer
23 views

PostgreSQL : select columns inside json_agg

If I have a query like SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
2
votes
1answer
28 views

PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows: CREATE FUNCTION public.assert ( in_assertion boolean, ...
0
votes
1answer
15 views

How do I select the value with SERIAL data type in postgres

I wanna ask how to select coloum that has SERIAL data type. I already try to do that but the output is like this 'Resource id #3', and not just '3' I use SERIAL so I dont have to input ID like when ...
0
votes
1answer
25 views

Selecting 3MB (or more/less) data from database server

I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a ...
0
votes
0answers
21 views

Will Amazon RDS for Postgres be as convenient as using Heroku Postgres?

I'm currently running a Heroku Rails app and our Postgres DB has grown to a data size of 50GB, and our DB cache hit rate is at 93%. We're on the legacy Ronin ($200/month) plan (1.7GB) and probably ...
0
votes
1answer
28 views

Will removing a foreign key constraint affect performance?

I have a units table and a reports table. A unit has many reports. So reports table has a foreign key unit_id which corresponds to the id of the unit. Hence, there is a foreign key constraint in ...
0
votes
1answer
30 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
2
votes
0answers
24 views

Postgres multidisk master replicating to single disk slave

I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, ...
0
votes
1answer
38 views

PostgreSQL: Creating view of multiple tables in function

I want to create view of multiple table in function using PostgreSQL 9.3 version. Example: I have following tables in the table name "AllTables": table1, table2,table3 And I have following ...
1
vote
1answer
29 views

How to insert into view through GUI in pgAdmin?

I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In ...