I can get Python to work with Postgresql but I cannot get it to work with MySQL. The main problem is that on the shared hosting account I have I do not have the ability to install things such as Django or PySQL, I generally fail when installing them on my computer so maybe it's good I can't install on the host. I found bpgsql really goo...
Attempting to insert an escape character into a table results in a warning. For example: create table EscapeTest (text varchar(50)); insert into EscapeTest (text) values ('This is the first part \n And this is the second'); Produces the warning: WARNING: nonstandard use of escape in a string literal (Using PSQL 8.2) Anyone know ...
Postgresql is interesting in that it supports several languages for writing stored procedures. Which one do you use, and why?...
What is the full correct syntax for the SQL Case statement? ...
Can anyone suggest a good guide/tutorial for programming PostgreSQL? By this I mean creating triggers and procedures, domains, new types, using the various languages, and so on. The manual is good, but I found it lacking as a learning aid (it is more of a reference, as manuals should be)? It is very good at describing the SQL syntax and...
phppgadmin comes with instructions for creating a reports database on the system for use with phppgadmin. The instructions describe how to set it up, but do not really give any indication of what its purpose is, and the phppgadmin site was not very helpful either. It seems to allow you to store SQL queries, so is it for storing admin qu...
I've been using PHP+MySQL for ages and am about to start using postgresql instead. What's the preferred method? Is it via the PDO objects or is there something better? ...
We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons. Has anyone else done such a move? Our database is the lifeblood of the application and will eventually be storing TBs of data, so I'm keen to hear about experiences of performance improveme...
From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in advice about when to use one or the other (or neither possibly). The PostgreSQL documentation has some information about this: GIN index lookups are...
What are some strategies that people have had success with for maintaining a change history for data in a fairly complex database. One of the applications that I frequently use and develop for could really benefit from a more comprehensive way of tracking how records have changed over time. For instance, right now records can have a numb...
I have a postgres database with a user table (userid, firstname, lastname) and a usermetadata table (userid, code, content, created datetime). I store various information about each user in the usermetadata table by code and keep a full history. so for example, a user (userid 15) has the following metadata: 15, 'QHS', '20', '2008-08-24 ...
I am working on a web application using Python (Django) and would like to know whether MySQL or PostgreSQL would be better when deploying for production. In one podcast Joel said that he had some problems with MySQL and the data wasn't consistent. I would like to know whether someone had any such problems. Also when it comes to perfo...
In MS SQL Server, create my scripts to use customizable variables: declare @somevariable int select @somevariable = -1 insert into foo values ( @somevariable ) I'll then change the value of @somevariable at runtime, depending on the value that I want the particular situation. Since it's at the top of the script it's easy to see & reme...
I'm setting up an web application with a FreeBSD PostgreSQL back-end. I'm looking for some database performance optimization tool/technique. Does someone know one? ...
First of all, I should point out that I'm not asking this question for any practical reason. I'm just asking out of sheer curiosity. Why is it that SQL distributions are so non-standard? I know there's an ANSI standard for SQL, but it just seems that developers of SQL databases go out of their way to find different ways to do things. ...
I currently have an MS Access application that connects to a PostgreSQL database via ODBC. This successfully runs on a LAN with 20 users (each running their own version of Access). Now I am thinking through some disaster recovery scenarios, and it seems that a quick and easy method of protecting the data is to use log shipping to create ...
I am considering log-shipping of Write Ahead Logs (WAL) in PostgreSQL to create a warm-standby database. However I have one table in the database that receives a huge amount of INSERT/DELETEs each day, but which I don't care about protecting the data in it. To reduce the amount of WALs produced I was wondering, is there a way to prevent ...
I've been using PostgreSQL a little bit lately, and one of the things that I think is cool is that you can use languages other than SQL for scripting functions and whatnot. But when is this actually useful? For example, the documentation says that the main use for PL/Perl is that it's pretty good at text manipulation. But isn't that m...
I am going to answer my own question because I just found the answer, but thought it still worth posting here. I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 ...
I have a database with a few dozen tables interlinked with foreign keys. Under normal circumstances, I want the default ON DELETE RESTRICT behavior for those constraints. But when trying to share a snapshot of the database with a consultant, I needed to remove some sensitive data. I wish that my memory of a DELETE FROM Table CASCADE comm...