I'm using symfony framework, and my db is Postgres. I export data from my tables into XML format. The european characters (those with umlaut) are messed up, inspite of using UTF-8 encoding. Any suggestions on how to fix this? ...
My table is: CREATE TABLE A( id serial NOT NULL, date timestamp without time zone, type text, sub_type text, filename text, filepath text, filesize integer, ); I have an update routine: $Query = "UPDATE A SET type=\"" . $strType . "\" where id=" . intval($ID); Problem: When $strType is a string, like "...
This is a follow up to this question. Here is my schema CREATE TABLE A( id serial NOT NULL, date timestamp without time zone, type text, sub_type text, filename text, filepath text, filesize integer, lock_status int ); In this database, a user can update the type,sub-type,filename,filepath,fil...
I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet): ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL; Each time I run this query, I receive the following error message: ERROR: column "mycolumn" contains null values I'm stumped. Where am I go...
We have the following example table (actually taken from another example here on stackoverflow...) CREATE TABLE example ( id integer primary key, name char(200), parentid integer, value integer); And given a specific child we want to get the top Parent. I know of the tablefunc connectby function but that is for getting a pa...
In postgresql a query in the querylog gets something like this: 2009-02-05 00:12:27 CET LOG: duration: 3781.634 ms execute <unnamed>: SELECT QUERY .... Is there a possibility to put something more usable into the "< unnamed >" placed like the url the query was requested from? Are there any other possibilities to track the origin o...
Hello, everyone! I have a problem with simple full-text search dictionary template in Pg. It's simply passes through any non-English words not bothering lower-casing em. Is it by design or I miss some kind of setting here? CREATE TEXT SEARCH DICTIONARY russian_simple ( TEMPLATE = simple, StopWords = russian ); SELECT ts_lexize(...
A) What is the best solution for regularly backing up large PostgreSQL database (version 8.3 running on latest Ubuntu server); please don't say pg_dump with those painfully slow insert statements B) What is the best solution for PostgreSQL database replication that works in real world ...
I am trying to find a way to have a SELECT statement return the set of dates between 2 input dates with a given interval. I would like to be able to easily change the time frame and interval that would be returned, so hard coding something with a series of SELECT ... UNIONs would not be ideal. For example: I want all the dates at 5 sec...
I am getting the error OperationalError: FATAL: sorry, too many clients already when using psycopg2. I am calling the close method on my connection instance after I am done with it. I am not sure what could be causing this, it is my first experience with python and postgresql, but I have a few years experience with php, asp.net, mysql, a...
I'm helping implement a SQL Server 2008 database and I have several columns that I'd like to default to the next number in a sequence, but still be able to insert numbers by hand into it at will. In PostgreSQL this is done with the SERIAL datatype and a sequence. Unfortunately those things do not exist in SQL Server. Here's what I know W...
I've got a problem that keeps coming up with normalized databases and was looking for the best solution. Suppose I've got an album information database. I want to setup the schema in a normalized fashion, so I setup two tables - albums, which has one listing for each album, and songs, which lists all songs contained by albums. albums ...
Hi world, I intend to have a python script do many UPDATEs per second on 2,433,000 rows. I am currently trying to keep the dynamic column in python as a value in a python dict. Yet to keep my python dict synchronized with changes in the other columns is becoming more and more difficult or nonviable. I know I could put the autovacuum on...
Hi, I have very strange problem with my application, upon occasions I get following error when connecting to postgres. This happens without any pattern, often happens when I run some extensive selenium tests. Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL server: server closed the connection unexpectedly ...
Hello, I have a table constructed like this : oid | identifier | value 1 | 10 | 101 2 | 10 | 102 3 | 20 | 201 4 | 20 | 202 5 | 20 | 203 I'd like to query this table to get a result like this : identifier | values[] 10 | {101, 102} 20 | {201, 202, 203} I can't figure...
Sometimes when copying stuff into PostgreSQL I get errors that there's invalid byte sequences. Is there an easy way using either vim or other utilities to detect byte sequences that cause errors such as: invalid invalid byte sequence for encoding "UTF8": 0xde70 and whatnot, and possibly and easy way to do a conversion? Edit: What my w...
I have an existing ASP.NET app which is configured to connect to a MS SQL Server database and I am trying to modify it to use a postgres database instead. I would like to avoid modifying the C# code, if possible. I expected that the following changes would be sufficient: Add a reference to a suitable postgres driver, in this case Nau...
I'm using psql 8.2.3 on FreeBSD. Every time I press Insert, Home, Delete, End, Page Up or Page Down, a tilde (~) character is inserted instead of performing the expected function of the key. Why does this happen and how can I fix it? ...
Very simple question (I hope), does there exist a tool similar to phpMyAdmin when working with a MySQL DB but for PostgreSQL databases? The reason I'm not just googling for a solution is that I am not as familiar with PostgreSQL as I am with MySQL and I was hoping that the SO community would be able to provide a tool that you have exper...
I have been working on an application in Django. To begin with, for simplicity, I had been using sqlite3 for the database. However, once I moved to PostgreSQL, I've run into a bit of a problem: the primary key does not reset once I clear out a table. This app is a game that is played over a long time period (weeks). As such, every t...