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 ...
48
votes
5answers
3k views
Do stored procedures prevent SQL injection?
Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection ...
45
votes
7answers
79k views
How do I list all databases and tables using psql?
I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool.
When I log in with psql --username=postgres, how do I list all databases and tables?
I ...
20
votes
1answer
1k views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
18
votes
5answers
6k views
SQL: SELECT All columns except some
Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.
Something like:
SELECT * ...
17
votes
1answer
35k views
How to insert values into a table from a select query in PostgreSQL?
I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int).
Now I want to insert a row into items_ver ...
17
votes
1answer
2k views
Is there a good way to run a trigger for each record in a postgres table?
I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the ...
16
votes
3answers
2k views
Is it a good idea/approach to index a VARCHAR column?
We're using PostgreSQL v8.2.3.
There are tables involved: EMPLOYEE and EMAILLIST.
Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6)
Table 2: EMAILLIST (email)
2 tables are ...
16
votes
3answers
6k views
What are the drawbacks with using UUID or GUID as a primary key?
I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
15
votes
5answers
12k views
How do I find PostgreSQL's data directory?
I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
15
votes
1answer
12k views
How do I get the current unix timestamp from PostgreSQL?
Unix timestamp is the number of seconds since midnight UTC January 1, 1970.
How do I get the correct unix timestamp from PostgreSQL?
When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
14
votes
2answers
5k views
How can I time SQL-queries using psql?
I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
14
votes
2answers
5k views
Find highest level of a hierarchical field: with vs without CTEs
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.
I ...
13
votes
5answers
3k views
Working of indexes in PostgreSQL
I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:
Friends ( user_id1 ,user_id2)
user_id1 and user_id2 are foreign keys to ...
12
votes
3answers
1k views
Is it possible to quickly create/restore database snapshots with PostgreSQL?
First of all, I'm a developer, not a DBA or sysadmin; please be gentle :)
I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
12
votes
1answer
2k views
Best practices for schema changes and data migrations to a live database without downtime?
How do you make schema changes to a live database without downtime?
For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
12
votes
2answers
3k views
Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
11
votes
3answers
2k views
Is the CPU performance relevant for a database server?
This is a purely theoretical question. Let's say I have an application deployed on multiple servers.
A load balancer,
Multiple/scalable applications servers
A (single) database server (for the ...
11
votes
1answer
396 views
Why is there a PostgreSQL Turtle?
I was just checking out the propaganda page for PostgreSQL and I found this little piece of art:
It is the "Turtle Style" PostgreSQL logo. How old is this, and did PostgreSQL ever officially use a ...
10
votes
3answers
2k views
PostgreSQL Stored Procedure Performance
Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.
One of the things I ...
10
votes
5answers
3k views
PostgreSQL: Force data into memory
Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?
10
votes
1answer
584 views
Generate an exception with a Context
When PostgreSQL throws an exception, there is a line "CONTEXT" like:
ERROR: INSERT has more target COLUMNS than expressions
LINE 3: ...
...
10
votes
2answers
3k views
Most idiomatic way to implement UPSERT in Postgresql nowadays
I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example).
And I'm just not a psql expert ...
10
votes
1answer
773 views
How does PostgreSQL handles Checkpoints in the middle of a WAL-enabled backup?
On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes).
Now I add a binary packup of the PG_DATA ...
9
votes
3answers
3k views
Should I use SQL JOIN or IN Clause?
I have a question about best approach. I am not sure which approach is best when data is considered variable in size.
Consider the following 3 TABLES:
EMPLOYEE
EMPLOYEE_ID, EMP_NAME
PROJECT
...
9
votes
3answers
4k views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint ...
9
votes
4answers
15k views
How to insert (file) data into a PostgreSQL bytea column
This question is not about bytea v. oid v. blobs v. large objects, etc.
I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
9
votes
1answer
412 views
Why does NOT IN with a set containing NULL always return FALSE/NULL?
I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.
...
9
votes
5answers
928 views
Efficiently select beginning and end of multiple contiguous ranges in Postgresql query
I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
9
votes
2answers
9k views
How do I install pgcrypto for PostgreSQL in Ubuntu Server?
I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto ...
9
votes
3answers
306 views
How-to implement an entity with an unknown maximum number of attributes?
I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
9
votes
4answers
2k views
Tools to migrate from SQLite to PostgreSQL
I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
9
votes
1answer
278 views
Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state
Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
8
votes
6answers
12k views
PostgreSQL is running locally but I cannot connect. Why?
Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
8
votes
3answers
288 views
What is retrieved from disk during a query?
Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...
Do the number of columns in a particular table affect the performance of a query, ...
8
votes
3answers
2k views
Are regular VACUUM ANALYZE stil recommended under 9.1?
I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?
If the answer is "it depends", then:
I have a largish database ...
8
votes
3answers
747 views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
8
votes
2answers
172 views
Postgres query plan of a UDF invocation written in pgpsql
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
8
votes
2answers
318 views
How can I generate a random bytea
I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data.
What is the best way of doing this?
8
votes
2answers
2k views
Configuring PostgreSQL for write performance
One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed ...
8
votes
2answers
12k views
How do I use currval() in PostgreSQL to get the last inserted id?
I have a table:
CREATE TABLE names (id serial, name varchar(20))
I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
8
votes
1answer
784 views
What is the difference between PostgreSQL 9.0 Replication and Slony-I?
Is there still any use for Slony-I, and if so, what is it? For clarification, from 9.0 Postgresql supports built in streaming replication.
8
votes
2answers
699 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?
...
8
votes
2answers
258 views
Modelling constraints on subset aggregates?
This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
8
votes
2answers
3k views
PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component
We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
8
votes
1answer
333 views
Choice of authentication approach for financial app on PostgreSQL
First some background.
The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL. We implement a very large amount of business logic in user defined ...
8
votes
2answers
290 views
Biological Sequences of UniProt in PostgreSQL
What is the best way to store UniProt biological sequences in PostreSQL?
Data Details
We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month.
The length of ...
8
votes
3answers
1k views
Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?
Context:
Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the ...
8
votes
1answer
165 views
Best cloud platform for PostgreSQL
Currently, in our project, we use dedicated servers for our PostgreSQL database.
In theory, we can run anything on some cloud platforms. However, PostgreSQL configuration is strictly related to ...
8
votes
1answer
644 views
How do I stop/disable PITR and safely clean WAL segments?
Our production server is running PostgreSQL v8.2.3 on CentOS release 5.2 (Final).
We've setup PITR in our production server. For some reasons, after setting up PITR, we're not able to manage and ...
8
votes
1answer
280 views
What is the modern way to partition PostgreSQL across machines, when the data is “naturally partitionable”
After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature.
Today I see data stores with quite different eyes than before. Things like Riak ...
