Postgres + Hibernate + Java UUID
I want to use PostgreSQL's native UUID type with a Java UUID. I am using Hibernate as my JPA provider and ORM. If I try to save it directly, it is just saved as a bytea in Postgres. How can I do this? ...
postgresql
What resources exist for Database performance-tuning?
What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area? The idea of this question is to collect the shed load of resources that invariably exist, so that people can have a "one stop" knowledge shop of the good, peer approved resources. General SQL Book: SQL Perform...
Hidden Features of PostgreSQL
I'm surprised this hasn't been posted yet. Any interesting tricks that you know about in Postgres? Obscure config options and scaling/perf tricks are particularly welcome. I'm sure we can beat the 9 comments on the corresponding MySQL thread :) ...
Can PostgreSQL select from an array returned by a function?
This query fails: SELECT xpath('/my/xpath/expr', my_xml)[1] FROM my_table ERROR: syntax error at or near "[" But this one works: SELECT x[1] FROM (SELECT xpath('/my/xpath/expr', my_xml) as x FROM my_table) as ss My xpath expression always returns only a single value, but the Postgres xpath function returns an array. I want t...
Django / mod_wsgi / postgresql_psycopg2: can't connect authentication fails (but settings work fine under django runserver or dbshell)
Hi Stackers, I'm deploying my django site on a Ubuntu 8.10 server that I've set up from scratch. I'm totally new to this so there may be plenty of stuff that I've missed, but happily have found my way through to the point of deploying a website. My last hurdle: I'm getting an authentication error with postgresql [Sun Apr 19 18:44:05...
What's the difference between B-Tree and GiST index methods (in PostgreSQL)?
I have been working on optimizing my Postgres databases recently, and traditionally, I've only ever use B-Tree indexes. However, I saw that GiST indexes suport non-unique, multicolumn indexes, in the Postgres 8.3 documentation. I couldn't, however, see what the actual difference between them is. I was hoping that my fellow coders migh...
How do you use variables in a simple PostgreSQL script?
For example, in MS-SQL, you can open up a query window and run the following: DECLARE @List AS VARCHAR(8) SELECT @List = 'foobar' SELECT * FROM dbo.PubLists WHERE Name = @List How is this done in PostgreSQL? Can it be done? ...
Postgresql: is "public." an standard way to fully qualify table name in Postgresql?
If i don't qualify a "public." on account_category table, *out account_category* will have a conflict with account_category table name. Does "public." also works on other rdbms? CREATE OR REPLACE FUNCTION X_RAIN(x VARCHAR, OUT user_id VARCHAR, out account_category varchar, out depth int) returns setof record AS $$ BEGIN return qu...
How to gain performance when maintaining historical and current data?
I want to maintain last ten years of stock market data in a single table. Certain analysis need only data of the last one month data. When I do this short term analysis it takes a long time to complete the operation. To overcome this I created another table to hold current year data alone. When I perform the analysis from this table it...
Postgres XML datatype
What are the benefits of using the "xml" datatype versus storing the xml content inside a "text" datatype? Am I able to query by some specific xml attribute or element? What about indexing and query performance? Besides the postgresql manual what other online sources can you point me to? ...
show tables in postgresql
How do you say "show tables" (mysql) in Postgresql? ...
How do I get at the sql statement that caused an SQLException using the Postgres JDBC driver in Java?
Background In my current project - a server product with no GUI front-end, I'm trying to write in better error handling support. Errors currently are outputted to the logs and are typically not read by users. We use PostgreSQL as our database backend and we access it using direct JDBC calls and DAOs via a database pooler. Most database...
Switching from MySQL to PostgreSQL - tips, tricks and gotchas?
I am contemplating a switch from MySQL to PostgreSQL. What are your tips, tricks and gotchas for working with PostgreSQL? What should a MySQLer look out for? See also: How different is PostgreSQL to MySQL? See also: Migrate from MySQL to PostgreSQL Note - I don't think this is a duplicate. In particular the type of answers are ...
Oracle XE or Postgres ?
Should I use for a web application Postgres or Oracle XE and why? They are both available for free and for use commercially. Why should I use one database over the other? ...
Using a stored procedure as a "dynamic" view?
I want to create a "view" to eliminate the same three-line sub-query from about 90 queries in an app I'm working on. The problem is the sub-query contains a condition based on a variable. SELECT * FROM items WHERE id NOT IN ( SELECT item_id FROM excluded_items WHERE user_id = 123 ); If it wasn't variable, I could simply make a view...
How to efficiently delete rows from a Postgresql 8.1 table?
I'm working on a PostgreSQL 8.1 SQL script which needs to delete a large number of rows from a table. Let's say the table I need to delete from is Employees (~260K rows). It has primary key named id. The rows I need to delete from this table are stored in a separate temporary table called EmployeesToDelete (~10K records) with a foreig...
sql select thru multiple postgres databases
is there a way to create a select statement that would retrieve data from multiple database in postgre? i was thinking it would be something like this: select * from dbname1.table1, dbname2.table2 where dbname1.table1.column1 = dbname2.table2.column1 ...
Performance Tuning PostgreSQL
Keep in mind that I am a rookie in the world of sql/databases. I am inserting/updating thousands of objects every second. Those objects are actively being queried for at multiple second intervals. What are some basic things I should do to performance tune my (postgres) database? ...
postgresql/postgis backup strategy to restore after geos/postgis recompile?
Current situation: Ubuntu 8.04 server edition (live server) Postgresql 8.3.7 (from standard repositories) Postgis 1.3.3 (from standard repositories) GEOS 2.3.4 (from standard repositories) Problem: GEOS contains bugs which are fixed in the 3.0 release. I have encountered these and need to upgrade GEOS/Postgis to include the GEOS...
PostgreSQL Autoincrement
I'm switching from MySQL to PostgreSQL and was wondering how I can do autoincrement values. I saw in the PostgreSQL docs a datatype "serial", but I get syntax errors when using it (in v8.0). ...