The main problem is the versioning of the database structure. The standard mysqldump and pg_dump utilities do not produce very well suited files for versioning. The dump commands produces the dump files with autoincrement values, TOC entries and so on. Since these objects is subject to continuous changes it always produces the huge di...
Recently reinstalled postgre 8.3 on my Ubuntu 8.04 after update. Used EnterpriseDB package. I can connect to the database locally, I see system DB postgres but I can't configure it because I can't find config files. Searched through entire hard drive and found only samples like pg_hba.conf.sample Where they are? ...
I need to bulk load a large file into PostgreSQL. I would normally use the COPY command, but this file needs to be loaded from a remote client machine. With MSSQL, I can install the local tools and use bcp.exe on the client to connect to the server. Is there an equivalent way for PostgreSQL? If not, what is the recommended way of loadin...
I have a customers table as following: customername, ordername, amount ============================= bob, book, 20 bob, computer, 40 steve,hat, 15 bill, book, 12 bill, computer, 3 steve, pencil, 10 bill, pen, 2 I want to run a query to get the following result: customername, ordername, amount =========================...
I've previously used sql-oracle just fine in Xemacs on XP and vista. However, now I'm using Postgresql psql on the command line but I kinda find it lame (as compared to using sql-* from within emacs) So, I'm trying to get it(psql) working within xemacs(21.4.22) on windows. When I type M-x sql-postgres ENTER, I get the following 3 pro...
Is there any way to connect to Postgres database using MySQL Workbench? I was hoping it could be easy enough, but didn't find any plugin available. ...
I've a server with postgresql installed on the postgres user. When I do: su - postgres [entering the password] psql I can then execute queries on the database, that works fine. However, from my php script (running on my own account): $dbconnection = pg_connect("host=localhost port=5432 user=postgres password=XXXXXXX "); (password ...
I'm using Postgres via Hibernate (annotations), but it seems to be falling over dealing with a User object: 12:09:16,442 ERROR [SchemaExport] Unsuccessful: create table User (id bigserial not null, password varchar(255), username varchar(255), primary key (id)) 12:09:16,442 ERROR [SchemaExport] ERROR: syntax error at or near "User" I...
Hello I need your help please. We have a PHP application running on MySQL, and we need to use PostgreSQL for a new customer. the problem is that when we insert empty strings in a field of type numeric, we get an error. I think we should actually use NULL instead of empty string. However we would like to avoid having to recode all th...
hey all together, i have problems with encoding when using json and ajax. chrome and ie encode umlauts as unicode when jsonifying, firefox and safari returning those utf-8 escaped umlauts like ¼Ã. where is the best place to give all the same encoding? js / php-get or by writing them to the database. and i think the next trouble is, wh...
I have a table with 300K rows and b-tree index on "operator" field. While I'm runnig this query it isn't using an index. "operator" has the same data-type as dict.vw_dict_operator.id. EXPLAIN SELECT id, name FROM dict.vw_dict_operator self WHERE EXISTS ( SELECT 42 FROM ti.ti_flight_availabilit...
After a db schema change, what was a column is now computed in stored procedure. Is it possible to make this change seamless for application programs? So that when a program sends a query like SELECT id, value FROM table ...it instead gets a result of SELECT id, compute_value() AS value FROM table I thought...
Consider the following table and rows: Listing A. ID, name, event, type 1, 'John Doe', '2010-09-01 15:00:00.000', 'input' 1, 'John Doe', '2010-09-03 11:00:00.000', 'input' 1, 'John Doe', '2010-09-04 17:00:00.000', 'input' 1, 'John Doe', '2010-09-02 15:00:00.000', 'output' 1, 'John Doe', '2010-09-03 16:00:00.000', 'output' 1, 'John Doe'...
Hi! I wonder how the mapping thing works in the OpenStreetMap. I'm building an app that uses my own database(which I will build using OSM dumps using Osmosis; same as in the OpenStreetMap website). I have really no idea how it works. Thanks in advance! :D ...
Am using postgreSQL 8 to create a database. I have noticed that it migt suffer a problem with concurrency control. 1. please explain to me how does postgreSQL 8 handle concurrency control? 2. when comparing MY SQL with postgreSQL which of these is best to handle concurrency control. ...
I have a simple logs table with about 500,000 rows, table structure is TABLE logs ( id serial NOT NULL, username character varying(32), user_id integer, description text NOT NULL, "time" timestamp with time zone DEFAULT now(), referrer character varying(128), "type" character varying(25) ) The most common operation in t...
hi there, I upgraded my Ubuntu 8.04 To Ubuntu 10.04. Unfortunately, the upgrade process also updated the Postgres 8.3 install to an Postgres 8.4 install. My feeling is that although database engine binaries have been updated, the database itself was not migrated, hence it can not be loaded by the 8.4. Since the 8.3 data format is not co...
Is there any danger running /etc/init.d/postgres restart?? We just had an incident where some relations "disappeared" and I ran the said command. Just got bollocked by the sysadmin, however he did not justify why this was a bad thing to do. I did put the webapp in maintenance mode so there wasn't any transactions/ queries going on at the...
I have a postgresql database that lost some records. I want to take a pg_dump SQL dump of the database from a few days ago and merge it with the current production database. A lot of the records are going to be duplicates so it can skip the duplicates. What is the best way to do this? ...
I have a procedure, where I have to check a certain view for some specified entries and delete them accordingly. I have used the following approach for this purpose - SELECT id_1, id_2, id_3, id_4 INTO v_id_1, v_id_2, v_id_3, v_id_4 FROM v_doc...