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)

2
votes
2answers
1k views

Read Committed Isolation Level

Quote from docs: Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data ...
2
votes
1answer
12k views

Performance difference between MySQL and PostgreSQL for the same schema/queries [closed]

I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS. I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
1
vote
1answer
419 views

Table for optional parent/child relationship

Assuming we have the following table: Item, Parent, Child and Parent is the parent of child. The item can either belong to a parent or child and not both. I have other tables that are similar to ...
0
votes
0answers
364 views

PostgreSQL: Slave doesn't replicate the Master's data

I'm using PostgreSQL 9.2.3 on both computers, both of which run Win2008R2. I've setup streaming replication by following this guide: ...
4
votes
2answers
505 views

Is it ever a good idea to denormalize for integrity?

I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers. These problems can take a variety of forms - they might be multiple ...
0
votes
1answer
44 views

Storing no of players the last seven days in an online game

I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how ...
4
votes
1answer
1k views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
1
vote
2answers
1k views

Dropping a group of schemas with similar name patterns

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object. In my case, I need to drop some schemas, all of the ...
3
votes
3answers
20k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
-1
votes
1answer
254 views

Should I keep pg_xlog on the same disk as data if using disk snapshotting?

We're running on EBS volumes on EC2. We're interested in leveraging EBS snapshotting for backups. However, does this mean we'd need to ensure our pg_xlog is on the same EBS volume as our data? (I ...
12
votes
2answers
8k views

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
6
votes
1answer
10k views

Usage of COPY FROM STDIN in postgres

I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org. I understand the commands, since I previously ...
0
votes
1answer
724 views

Cannot create PostgreSQL user

I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04. Using the following StackOverflow question, I tried to create a user/role in postgressql: ...
3
votes
2answers
4k views

Creating a PostgreSQL SERIAL column using pgAdmin3

When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)? Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
2answers
219 views

“Rewinding” a Postgresql database

I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
4
votes
1answer
281 views

Is there better way to 'touch' a row than performing a dummy update?

I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update: ...
1
vote
1answer
152 views

PostgreSQL socket not listening on the right one

I'm trying to get my PostgreSQL to work. It is clearly running, according to: ps aux | grep postgres | grep -v grep I am getting - username 5781 0.0 0.0 2441364 524 ?? Ss ...
2
votes
1answer
1k views

PosgreSQL: setting high work_mem does not avoid disk merge

This is not quite my day with postgres. On my server machine with PosgreSQL 9.2.3 I have set work_mem to 4MB to avoid Sort Method: external merge Disk: 2072kB but it did not help: cwu=# vacuum ...
1
vote
2answers
2k views

Why PostreSQL 9.2 does not use index scan if it can?

My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan. Here is my table. The index I am talking about is name_left_prefix cwu=# \d web_city; ...
3
votes
1answer
4k views

Error “column does not exist” in a SELECT with JOIN and GROUP BY query

I'm using PostgreSQL 9.1 with a Ruby on Rails application. I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
1
vote
2answers
289 views

Error installing pgcrypto extension [closed]

I want to install the pgcrypto extension on my postgresql installation on Windows. I found that I just have to call CREATE EXTENSION pgcrypto; but I've got an error : Syntax error on « CREATE » LINE ...
0
votes
2answers
249 views

Storing data in PostgreSQL: One table or two?

I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month. Question: Because every product can have different ...
1
vote
1answer
104 views

Does PostgreSQL work well as a workstation-based DBMS?

I've been considering PostgreSQL for an application I am writing in .NET WinForms, and the app is intended to be standalone with occasional sync-ing with a web service. I plan to use Sql Server for ...
2
votes
1answer
166 views

Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql?

I have a rather naive question regarding the size of shared_buffers computed from pg_buffercache and the shared_buffers size mentioned in postgresql.conf. Based on my understanding pg_buffercache ...
1
vote
1answer
519 views

Postgres: Given some parent id's how can I SELECT X child records for each?

In Postgres, if I have : Parent Table id, name 1, Parent1 2, Parent2 ..... 5, Parent5 Child Table id, parent_id, name 1, 1, Child1 2, 1, Child2 ..... 6, 2, Child6 ..... 25, 5, Child25 so 5 ...
2
votes
2answers
681 views

Creating sequence for longer numeric values

I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API. The value must be 20 digit long, numeric and unique. Best ...
2
votes
2answers
388 views

PostgreSQL and default Schemas

Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas: Now from my understanding Schemas are like folders for organization, etc. So the ...
1
vote
2answers
2k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
2
votes
1answer
624 views

How to get user-specific settings in PostgreSQL?

I have to collect some information of PostgreSQL roles. One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters ...
2
votes
2answers
154 views

Different queries result in big difference in performance

I have 2 similar functions, different way result in big diff in performance. The PostgreSQL version: 9.2.1. Function 1 create or replace function func_1() returns text as $$ declare v_i ...
1
vote
1answer
197 views

Equivalent of MRG_MYISAM in databases other than MySQL? [closed]

Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)? I know about fragmenting, but this is not quite the same AFAIK. We're using ...
4
votes
1answer
4k views

PostgreSQL: Cannot change directory to /root

I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump. Problem: However I'm getting the error could not change directory to ...
3
votes
0answers
290 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
6
votes
1answer
2k views

Recovering a dropped table in PostgreSQL

I have : CREATE TABLE ketoan_vn.Customers ( CustomerId character varying(10) NOT NULL, CustomerName character varying(150), CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId) ); I ...
0
votes
1answer
106 views

PostgreSQL, How to keep only one schema?

I don't want some users be available to create schemas, and only use the public schema. I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 ...
0
votes
1answer
44 views

look for missing datetime records in database

I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database. As I browse through the database, I've noticed there are minutes here and there ...
1
vote
2answers
4k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
0
votes
1answer
1k views

How to insert binary data into a PostgreSQL BYTEA column using libpqxx?

I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx. What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is ...
0
votes
1answer
6k views

Using postgres with the command line without pgAdmin

I started working with PostgreSQL and till now I only had issues with it ( pgAdmin3 issues the most ) now I work a lot with MySQL and sometimes on the command line by just doing mysql -u ... -h ...
0
votes
1answer
1k views

postgres deadlock without explicit locking

I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock ...
1
vote
2answers
752 views

In Postgresql, is it possible to change the maximum number of columns a table can have?

I'm tasked with keeping a postgres table in sync with a non-postgres table. The non-postgres table has more than 1600 columns. When I try to create the table in postgres, I get: ERROR: tables can ...
1
vote
1answer
2k views

Pass a table array as a parameter to an upsert function in postgresql

i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
1
vote
2answers
658 views

PostgreSQL stored procedure which can return one of two custom rowtypes

I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this: CREATE OR REPLACE FUNCTION ...
1
vote
1answer
242 views

Drop database from remote that has connected users

I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent ...
0
votes
1answer
346 views

How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?

I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could ...
4
votes
1answer
2k views

PostgreSQL concurrent increment of counter

I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
1
vote
1answer
196 views

How to allow a user to create databases only with a certain prefix?

We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must ...
1
vote
2answers
3k views

PostgreSQL cannot start after replacing contents inside its Data folder

When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
0
votes
0answers
122 views

Identical Postgres index scan taking 5x longer on server

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
38
votes
3answers
62k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;