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 ...
7
votes
4answers
4k views
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
I had to write a simple query where I go looking for people's name that start with a B or a D :
SELECT s.name from
spelers s
WHERE s.name LIKE 'B%' or s.name like 'D%'
order by 1
I was wondering ...
7
votes
3answers
480 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
7
votes
5answers
3k views
What are the differences between “Stored Procedures” and “Stored Functions”?
So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL.
The comment links to a wikipedia article but some of this don't ...
7
votes
5answers
1k views
Are there any professional fulltime PostgreSQL DBA's?
For my job I work on a JavaEE application with PostgreSQL as the database. Although we have a sysadmin for our productions servers, who also manages our database servers, we have no full-time DBA ...
7
votes
3answers
2k views
How should I represent a latitude and longitude in Postgres without using PostGIS?
How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.
7
votes
2answers
2k views
Trigger: move deleted rows to archive table
I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis.
I would like to have a table called restrictions_deleted, where ...
7
votes
5answers
1k views
Are there any good and free tools for managing a PostgreSQL database?
Before, I have used phpmyadmin to manage an MySQL database, but now I would like to manage a PostgreSQL database. The PostgreSQL database is on a server, but not a webserver, so I don't use PHP.
Are ...
7
votes
2answers
345 views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
7
votes
4answers
795 views
Locking issue with concurrent DELETE / INSERT in PostgreSQL
This is pretty simple, but I'm baffled by what PG does (v9.0).
We start with a simple table:
CREATE TABLE test (id INT PRIMARY KEY);
and a few rows:
INSERT INTO TEST VALUES (1);
INSERT INTO TEST ...
7
votes
2answers
694 views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
7
votes
2answers
2k views
Is REINDEX dangerous?
I've been trying to COUNT(*) a table with 150,000 rows that has a Primary key. It tool about 5 minutes, so I figured out this is an indexing problem.
Citing the PostgreSQL manual:
REINDEX is ...
7
votes
1answer
873 views
PostgreSQL Index optimization with dates
I have a large table of objects (15M rows +), for which I want to query for outdated field. PostgreSQL (9.0.8)
I want to divide the query by millions, for scalability & concurrency purposes, and ...
7
votes
1answer
2k views
How to migrate large blob table from mysql to postgresql?
I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
7
votes
1answer
879 views
PostgreSQL: Disk space not released after TRUNCATE
I haveTRUNCATEd a huge (~120Gb) table called files:
TRUNCATE files;
VACUUM FULL files;
The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space?
UPDATE:
The ...
7
votes
3answers
258 views
Efficiently comparing prices in different currencies
I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the ...
7
votes
2answers
1k views
Can PostgreSQL create a case sensitive database?
I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to ...
7
votes
2answers
2k views
PostgreSQL for high volume transactions and for Data warehousing
Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using ...
6
votes
6answers
488 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
6
votes
2answers
1k views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
6
votes
2answers
885 views
Is DB2 more reliable and stable compared to PostgreSQL or MySQL?
Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps.
I had data loss few times and thanks to old backup i was always up and running.
But for a recent project for Airport, ...
6
votes
4answers
542 views
How to check if a subquery has exactly one distinct result and a specified value concisely?
I found myself writing the following:
select 'yes'
where exists(select * from foo where val=1) and not exists(select * from foo where val<>1);
and wondering if there is a more concise way ...
6
votes
4answers
437 views
Should I invest the time to change the column type from CHAR(36) to UUID?
I have a few million rows in my database already. I didn't know about the PostgreSQL UUID data type when I designed my schema.
One of the tables has 16M rows (about 3.5M to 4 M records per shard), ...
6
votes
3answers
305 views
Is there a need to keep refreshing the indexes from time-to-time?
This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL.
I have created a fresh database and the tables, at present, have no records. I have created ...
6
votes
2answers
625 views
Database for building a realtime analytics system
I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on
Real time counting of unique URI visit/PV by different ...
6
votes
3answers
2k views
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
6
votes
1answer
367 views
Concept of Schema in PostgreSQL
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?
Can it affect me in future if I decide to not think ...
6
votes
1answer
1k views
Starting PostgreSQL server after a HDD crash results in FAILED STATE
I am using Fedora 15 with PostgreSQL 9.1.4. Fedora crashed recently after which:
An attempt to start the PostgreSQL server :
service postgresql-9.1 start
gives
Starting postgresql-9.1 (via ...
6
votes
3answers
663 views
Where does the magic column “name” come from?
I got this by accident:
db=> select name from site;
ERROR: column "name" does not exist
LINE 1: select name from site;
^
db=> select site.name from site;
name
...
6
votes
3answers
2k views
How to version control PostgreSQL schema with comments?
I version control most of my work with Git: code, documentation, system configuration.
I am able to do that because all my valuable work is stored as text files.
I have also been writing and dealing ...
6
votes
1answer
295 views
PostgreSQL Complete Database Backup
I've been trying to figure out how I can completely backup my PostgreSQL database.
I have not seen any resource that explains how to do a complete backup.
I am aware of the pg_dump and pg_dumpall ...
6
votes
1answer
371 views
Equality query on NVARCHAR column yields multiple results in SQL Server 2012
I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard).
I've noticed an interesting phenomenon when querying unicode words. Given the definition:
CREATE ...
6
votes
1answer
325 views
How do I preserve the order of the original elements in an unnested array?
Given the string
'I think that postgresql is nifty'
, I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
6
votes
4answers
8k views
PostgreSQL: How to create full copy of database schema in same database?
How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc.
My version of Postgres is 8.4
P.S. I need to copy schema NOT database
6
votes
3answers
4k views
PostgreSQL: The database cluster initialization failed
Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights.
I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL
Every way I ...
6
votes
3answers
2k views
How to convert mysql to postgresql?
I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be:
Free
Works under Linux
Simple to use and not buggy
You actually tried and ...
6
votes
3answers
330 views
PostgreSQL Initial Database Size
There are 2 parts to my question.
Is there a way of specifying the initial size of a database in PostgreSQL?
If there isn't, how do you deal with fragmentation when the database grows over time?
...
6
votes
1answer
7k views
How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?
I´m trying to setup an PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this?
I have installed the PostgreSQL ...
6
votes
1answer
5k views
How to get a working and complete PostgreSQL DB backup and test
I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users.
From what I've read, and it ...
6
votes
2answers
307 views
Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting
While writing a query other day a thought came to me and have stuck in my mind.
What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise ...
6
votes
2answers
6k views
PgAdmin III - How to connect to database when password is empty?
I have installed PostgreSQL 9.1 on my PC (Win 7). I have a small Java application connecting successfully to it with login=sa and password="". The connection works.
However, it is refused from ...
6
votes
1answer
728 views
BLOBs or references in PostgreSQL
I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files ...
6
votes
3answers
212 views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
6
votes
4answers
887 views
Determine when a PostgreSQL database was last changed
I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed?
Instead of using pg_dumpall, I'd ...
6
votes
1answer
3k views
PostgreSQL: COUNT(*) uses a sequential scan, not index
Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?
6
votes
2answers
980 views
View Postgresql Memory Usage
I'm running Postgresql on a Ubuntu server and need to be able to monitor its memory usage. Currently I have scripts running in a one-minute cron job that monitor/log various statistics and need to ...
6
votes
2answers
1k views
Load balancer versus connection pooling — is there a difference?
I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is ...
6
votes
3answers
873 views
Postgres, MVCC, and Locking
I have a series of SQL statements that look like the following:
BEGIN;
SELECT counter FROM table WHERE id=X FOR UPDATE;
REALLY COMPLEX QUERY;
UPDATE table SET counter=Y WHERE id=X;
END;
I'd like to ...
6
votes
2answers
300 views
PostgreSQL PITR restore
I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving. On this server, there are three databases: databaseA, databaseB and databaseC. Someone deletes a ...
6
votes
1answer
177 views
in postgres, can one optimize a table across partitions?
in a database with many companies, segregating them using schema and search_path
almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting ...
6
votes
1answer
61 views
How can I see a query after rules have been applied?
From docs - 37.3.1.1. "A First Rule Step by Step"
CREATE TABLE shoelace_log (
sl_name text, -- shoelace changed
sl_avail integer, -- new available value
log_who ...
