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 ...
5
votes
1answer
95 views
Determining which isolation level is appropriate
This is a homework question.
For the following transactions state the isolation level that will
maximize throughput without lowering the integrity of the database.
Explain the answer.
...
4
votes
2answers
452 views
Postgresql 9 speeding up indexed inserts (JPA)
I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
4
votes
3answers
89 views
Filtering data that could have more than one category
I have a table like the following:
Annotation (
document,
term,
category
)
Where document and term are some ID, while category is an integer.
The couple document - term is not unique, ...
4
votes
5answers
6k views
How can I use a default value in a Select query in PostgreSQL?
I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?
E.g. ...
4
votes
2answers
111 views
Custom unique column constraint, only enforced if one column has a specific value
Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is "true", then ...
4
votes
5answers
1k views
Improve sort performance in PostgreSQL?
I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
4
votes
2answers
2k views
Can't rename columns in PostgreSQL views with CREATE OR REPLACE
In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names.
This works
CREATE OR REPLACE VIEW gfam.nice_builds AS
SELECT ...
4
votes
2answers
344 views
Pros and Cons of virtualisation technology for database-server and datastorage
Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm ...
4
votes
1answer
251 views
Is PostgreSQL replication production ready?
How does PostgreSQL native replication compare to MySQL?
I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?
...
4
votes
3answers
538 views
How can I write a proper query to JOIN all of these tables without duplicates?
Let's say I have four tables:
TABLE: players
COLUMNS: id, first_name, last_name
TABLE: passing_stats
COLUMNS: id, year, passing_yards (several other passing columns)
TABLE: rushing_stats
...
4
votes
3answers
349 views
Lookup table for a one-to-many relationship
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff
lookup
- ...
4
votes
2answers
239 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 ...
4
votes
1answer
758 views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
4
votes
1answer
88 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
4
votes
2answers
180 views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about 1MM ...
4
votes
3answers
2k views
Set a column's default value to the concatenation of two other columns' values?
I have a Postgresql 8.1 database. In one table, there are three columns: first_name, last_name, display_name.
Is it possible to set the default value of display_name to be first_name + " " + ...
4
votes
3answers
3k views
Created user can access all databases in PostgreSQL without any grants
I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access ...
4
votes
3answers
3k views
How can I insert if key not exist with PostgreSQL?
I have a table:
CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY)
Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the ...
4
votes
2answers
441 views
Index Generation in Postgres
We have a database with a single table made up of around 700 million entries.
We update the database by adding new entries on a server then transfer the server to the production server using pg_dump:
...
4
votes
3answers
1k views
Optimizing query using view on EAV structure
An application is writing into a database that follows an EAV structure, similar to this:
CREATE TABLE item (
id INTEGER PRIMARY KEY,
description TEXT
);
CREATE TABLE item_attr (
item ...
4
votes
1answer
878 views
PostgreSQL: Unable to run repmgr cloned database
I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave.
I am using the instructions on this guide: ...
4
votes
4answers
1k views
Trouble setting up Postgres for the first time
I have some, albeit limited, experience with MySQL and MongoDB (both running fine on server) but having trouble with Postgres and find the documentation lacking for newbies, like myself.
Postgres is ...
4
votes
2answers
1k views
How to promote an existing index to primary key in PostgreSQL
I know how to make a primary key within a table, but how do I make an existing index a primary key? I'm trying to copy an existing table from one database to another. When I show the table, the index ...
4
votes
2answers
2k views
How do I detach all other users from a postgres database?
I need exclusive access to a database. Is it possible using an SQL command to "detach" all other users from a postgres database. Or maybe closing all other connections and then gaining exclusive ...
4
votes
2answers
6k views
List the database privileges using psql
I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL ...
4
votes
1answer
264 views
Source several files at once with psql
I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran ...
4
votes
2answers
1k views
PostgreSQL: Creation date of a table
I've recently finished a project, during which many DB tables were created.
Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.
Is there a ...
4
votes
3answers
652 views
How much could sequential scan gain by GPU?
I am considering hiring someone to implement GPU processing in Postgres to speed up sequential scans on a database located on a ramdisk. How much do you believe I could gain using this approach? I ...
4
votes
2answers
56 views
Seeting up a PostgreSQL database on another hard drive than my system one on OS X?
I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data.
Is there a way to create new ...
4
votes
1answer
2k views
Optimal way to ignore duplicate inserts?
Background
This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:
-- Ignores duplicates.
INSERT INTO
db_table ...
4
votes
2answers
837 views
Recover PostgreSQL database from WAL errors on startup?
I'm trying to set up an OpenStreetMap server on an Ubuntu 12.04 machine using the Ubuntu packages listed at switch2osm.org. I initially installed and set up everything using a Northeast US-only map ...
4
votes
2answers
3k views
Easy Postgresql master/slave replication with automatic failover on Linux?
I need a reliable SQL DB for our application and have been looking at Postgresql's streaming replication support. It seems that it's not hard to setup master/slave replication and you can use the ...
4
votes
1answer
1k views
How to set Postgresql database to see date as “MDY” permanently
Ho can I set my database to see the 'date' as "MDY" without running:
SET datestyle = "ISO, MDY";
every-time I'm trying to access it?
I'm using Postgresql version 9.1, Ubuntu 12.04.
My system ...
4
votes
2answers
208 views
What is the consensus on using NOT NULL constraints on all text-based fields in a table?
Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying.
I'm curious what the feelings are with using something like NOT NULL ...
4
votes
2answers
571 views
What can I do with the new json field?
PostgreSQL 9.2 introduced the json field type. Why and when should I use it? What benefits does it have over a text field?
I thought there were new query options available, however I haven't seen ...
4
votes
1answer
105 views
I was interrupted while transferring a very large table. How can I pick it up from where it stopped?
I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the ...
4
votes
1answer
568 views
Dropping unique constraint on PostgreSQL table taking 10+ hours
We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about ...
4
votes
2answers
233 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
4
votes
3answers
3k views
Automating failover in PostgreSQL 9.1
How does one setup two identical servers for automatic failover in PostgreSQL 9.1.
OS
Centos 5
PostgreSQL 9.1 compiled from source
The postgres user account exists on both machines and has a ...
4
votes
1answer
258 views
Gaining visibility into long running INSERT INTO … SELECT
I have a large SQL insert statement that updates a summary table. The process normally runs hourly, and takes about 5 to 10 minutes to calculate uniques over that period. I "foolishly" chose to run it ...
4
votes
3answers
2k views
PostgreSQL designing tool
I am about to design my first database which is going to run with PostgreSQL. I am used to the magnificant tool called MySQL Workbench. It is useful and it looks good which I kind of expect from a ...
4
votes
2answers
617 views
PostgreSQL: Last access time to table
I am in charge of a large PostgreSQL database, with few dozen tables. I suspect that many of these tables are never accessed.
What's the best way to check when was the last time a certain table was ...
4
votes
2answers
860 views
Estimate average and median efficiently in Postgres?
I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which ...
4
votes
1answer
737 views
Indexing to improve performance of range queries
I've got a relation in a Postgres 8.2 database similar to this:
CREATE TABLE foo (
foo_id varchar(160) NOT NULL,
bar_id varchar(160) NOT NULL,
created bigint NOT NULL,
PRIMARY KEY ...
4
votes
1answer
64 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 ...
4
votes
1answer
88 views
Materialized View PostgreSQL
Is there any possibility of having built-in materialized views in PostgreSQL ?
I don't want Gardner version or somehthing like physical table populated by triggers.
I've read that was/is something ...
4
votes
2answers
116 views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field.
Consider the following table:
lap_id (Serial), lap_no (int), car_type ...
4
votes
1answer
120 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
4
votes
2answers
491 views
PostgreSQL: reuse of complex intermediate result in same query
Using PostgreSQL (8.4), I'm creating a view that summarizes various results from a few tables (e.g creating columns a, b, c in the view), and then I need to combine some of these results together in ...
4
votes
1answer
182 views
Can PostgreSQL tell me what configuration files it's using?
I am having trouble with some changes I'm making to a configuration file. I think PostgreSQL is not using the ones I'm modifying. Is there any way to get postgres (maybe some environment variable) to ...
