postgresql

Options to Replicate Microsoft SQL Server Database to MySQL/PostgreSQL on Linux

I need to replicate data from Microsoft SQL Server to MySQL or PostgreSQL. The data includes images stored in BLOB columns. Could you please comment on your experiences with the following strategies and suggest others I may have missing? custom script written in Java using JDBC linux odbc driver with perl script Setup my own windows ...

PostgreSQL not showing up on phpconfig() function(on windows)

I just installed Apache 2.2.11 and downloaded the zip for PHP5.2.9-2 I uncommented extension=php_pdo_pgsql.dll and extension=php_pgsql.dll But when I call the phpinfo() function, postgresql is not showing up. What am I forgetting? Also, on the PHP site, I found this: In order to enable PostgreSQL support, --with-pgs...

Creating a copy of a database in Postgres

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin? ...

PostgreSQL: best way to create new/duplicate existing tables every year

Hi guys, referring to this question, I've decided to duplicate the tables every year, creating tables with the data of the year, something like, for example: orders_2008 orders_2009 orders_2010 etc... Well, I know that probably the speed problem could be solved with just 2 tables for each element, like orders_history and order_actual,...

How to insert a text file into a field in PostgreSQL?

How to insert a text file into a field in PostgreSQL? I'd like to insert a row with fields from a local or remote text file. I'd expect a function like gettext() or geturl() in order to do the following: % INSERT INTO collection(id, path, content) VALUES(1, '/etc/motd', gettext('/etc/motd')); -S. ...

Cant the prepared statement be used throught the transaction, from php?

Hi again, guys. I work on a LAPP environment (linux apache postgresql php), and i'm just triyn to find out how to use the prepared statement within the transaction (if is possible). I hope code will explain better then words: Example 1, simple transaction: BEGIN; INSERT INTO requests (user_id, description, date) VALUES ('4', 'This do...

postgresql libpq inserting empty row for no reason

I'm using the libpq library in C for accessing my Postgresql database. The application inserts a piece of data fed from a queue. When there is a lot of data and it's inserting very quickly it randomly inserts and empty row into the table. Before I even perform the insert I check to make sure that the length of the text being inserted is ...

Seeing Postgres errors (plpgsql already exists/function array_accum already exists) in Rails app log

I am constantly seeing the following postgres errors/warnings in my Rails development log. It's not causing my app to fail (I hope). Any idea what could be causing them? What can I do to debug this? Installed in my PC: - Ruby 1.8.7 (2008-08-11 patchlevel 72) [i486-linux] - Rails 2.2.2 - PostgreSQL 8.3.7 [4;36;1mSQL (0.1ms)[0m [0...

any formal benchmarking of Open source Database software ?

Hi, Is there any formal performance and stress test reports of open source database, specially sqlite,MySQL an PgSQL? I want to use sqlite in server for its simple structure and easy embeddable capability. But I can not find any pros and cons (by Googling and Yahoo!ing) regarding performance of these database software. Please suggest....

SQL to select the middle third of all values in a column in PostgreSQL

Suppose I have a column of heights -- how can I select all and only those height values that are neither in the top 30% of values nor the bottom 30% of values. UPDATE: I'd like the answer for PostgreSQL (or, failing that, MySQL -- I'm using Rails). ...

Postgresql and PHP: is the currval a efficent way to retrieve the last row inserted id, in a multiuser application?

Hi guys! Im wondering if the way i use to retrieve the id of the last row inserted in a postgresql table is efficent.. It works, obviously, but referencing on the serial sequence currval value could be problematic when i have many users adding rows in the same table at the same time. My actual way is: $pgConnection = pg_connect('host...

Connection attempt failed in postgreSQL server

hi all, In our project we are using postgresSql(8.3) as the database server, but we are rarely facing problem (once a while) . in the log file i am seeing error Like ****Connection attempt failed**** it means for me that , application is attempting to get connection object from the DB server but it was unsuccessfull. Strange t...

Regex in Postgres - not doing what I'm trying to do (newbie question)

I have this regex in a query in postgres and I cannot figure out why it is not matching anything after the text specified in the regex; The idea is removing the last part, including the separator characters between. I have records like these to match: Villa hermosa, Pilar, PCIA. BS. AS. Esmeralda - Pilar - BUENOS AIRES. San Ma...

Select multiple ids from a PostgreSQL sequence

Is there a concise way to select the nextval for a PostgreSQL sequence multiple times in 1 query? This would be the only value being returned. For example, I would like to do something really short and sweet like: SELECT NEXTVAL('mytable_seq', 3) AS id; And get: id ----- 118 119 120 (3 rows) ...

Postgresql: how to make full text search ignore certain tokens?

is there a magic function or operator to ignore some tokens? select to_tsvector('the quick. brown fox') @@ 'brown' -- returns true select to_tsvector('the quick,brown fox') @@ 'brown' -- returns true select to_tsvector('the quick.brown fox') @@ 'brown' -- returns false, should return true select to_tsvector('the quick/brown fox') @@ ...

Are there any good PostgreSQL clients for linux?

I am frustrated of not having a good Linux GUI administration and development tool for PostgreSQL. pgAdmin III is buggy and unusable piece of... hmm, software, compared to Windows-only PostgreSQL Maestro and EMS PostgreSQL manager. phpPgaAmin does not looks promising. EMS PostgreSQL manager can work under Wine, but such setup have a n...

How do I find the last time that a PostgreSQL database has been updated?

I am working with a postgreSQL database that gets updated in batches. I need to know when the last time that the database (or a table in the database)has been updated or modified, either will do. I saw that someone on the postgeSQL forum had suggested that to use logging and query your logs for the time. This will not work for me as t...

delete old PostgreSQL data directory in win7

i'm not able to delete my old data directory in windows 7. i already uninstalled PostgreSQL, but there's still the system service user account "postgres", who's the only one with access-rights to the folder. possible solution would be to develop a service, running as "postgres" user, which deletes the folder, but there must be an easier...

What is the best way to update (or replace) an entire database table on a live machine?

I'm being given a data source weekly that I'm going to parse and put into a database. The data will not change much from week to week, but I should be updating the database on a regular basis. Besides this weekly update, the data is static. For now rebuilding the entire database isn't a problem, but eventually this database will be li...

Converting mySQL's KEY to PgSQL?

I'm converting some mySQL code to pgSQL and I've run into this problem mysql code : "KEY ug_main_grp_id (ug_main_grp_id)" What would be the equivalent in PgSQL. First I thought I could just use CREATE INDEX in pgSQL, but that's really not appropriate because KEY is not an index :P ...