I'm trying to write a function in PL/PgSQL that have to work with a table it receives as a parameter. I use EXECUTE..INTO..USING statements within the function definition to build dynamic queries (it's the only way I know to do this) but ... I encountered a problem with RECORD data types. Let's consider the follow (extremely simplifie...
In MySQL you enter dates (datetime field) in the format 2009-10-16 21:30:45. Which makes it simple to insert times into the database. $date = date('Y-m-d H:i:s', $timestamp); Will this format work with SQLite and PostgreSQL? If not, what format do they use? ...
Does psycopg2 have a function for escaping the value of a LIKE operand for Postgres? For example I may want to match strings that start with the string "20% of all", so I want to write something like this: sql = '... WHERE ... LIKE %(myvalue)s' cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' } Is there an existi...
Recently we are working on migrate our software from general PC server to a kind of embedded system which use Disk on module (DOM) instead of hard disk drive. My colleague insist that as DOM could only support about 1 million times of write operation, we should running our database entirely in a RAM disk and backup the database to DOM....
Is there a way to circumbent google app engine sdk to allow the usage of classes that are not present in the GAE JRE white list? I know the app that I would be building will not run in appspot, but at least in my development server, I need to access a postgresql database(java.net.socket.*) and generate some files(java.io.FileWriter) in m...
We are trying to show a map with a large number of points (ranging from 1000 up to 20000 depending on the users criteria) using OpenLayers and GeoServer. The points are stored in a PostgreSQL database. Whilst the application seems to have little problem displaying the lower range, its practical limit seems to be around 5000 points. Th...
My web app stores all timestamps in UTC without time zones. I have a shell/psql script that returns a list of recent logins, and I want that script to display login times in the server's local time zone (which may vary depending on where the server is and with daylight savings). To get an interval representing the difference between my...
Error: Can't load '/home/oracle/.cpan/build/DBD-Pg-2.16.1/blib/arch/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.5: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230. Did anyone have similar error while instaling DBD::Pg perl module ? what can I do to fi...
I'm looking for a way to quickly "clone" a database from 1 postgresql server to another. Assuming... I have a postgresql server running on HostA, serving 2 databases I have 2 devices mounted on HostA, each device stores the data for one of the database (i.e. 1 database => 1 tablespace => 1 device ) I am able to obtain a "safe" point i...
Hello, I'm searching for a simple way to delete all data from a database and keep the structure (table, relationship, etc...). I using postgreSQL but I think, if there a command to do that, it's not specific to postgres. Thanks, Damien ...
We are in the process of upgrading from PostgreSQL 8.3 to PostgreSQL 8.4, in a large part so that we can start using certificate-based authentication. We have some Python 2.x code that accesses the database that uses PyGreSQL. Is there a way to get it or any other Python library to use a cert to access PostgreSQL? Looking through the P...
Hi I just tried to create my first plpgsql function. When executing the script, I get ERROR: language "‘plpgsql’" does not exist I then run the command CREATE LANGUAGE plpgsql; which shows the following error: ERROR: language "plpgsql" already exists Commands are being run on the same database. Regards Peter ...
Hi I am working on bug in my Database library which use LibPQ internally.I see following behavior which looks strange to me : For postgreSql Datatype DATE it returns true if its empty in DB For postgreSql Datatype TIME it return true if its empty in DB But for VARCHAR,SMALLINT..it return false even they were empty in DB. PS. I have...
I wanted to reset a database and issued a drop database followed by a create database on a postgresql server accessed through psycopg2 by a django app. When I do ./manage.py syncdb I get the following error: (prod)root@ns204612:/home/someproject/prod/django-mingus/mingus# ./manage.py syncdb Traceback (most recent call last): File "./...
I'm trying to install Postgres 8.4 for a Rails dev-box and I'm having a couple problems. I installed postgres and pg-admin3 through apt-get. Using the lastest Rails 2.3.5 and lastest Ruby 1.9.1 Now the configuration is bothering me. I found some documentation regarding setting up the user for the postgres user(which is the default adm...
Possible Duplicate: How to empty a SQL database? What is the fastest way to delete all records from all tables in the db supposing they do not have too much data(may be a few records in some tables but no more)? I believe that recreate the database from structure dump is much longer variant. ...
Given the following table in PostgreSQL, how do I insert a record which refers to itself? CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); The examples I'm finding on the Web have been allowed the pare...
I need a db to store, i.e. user records. Regular stuff: name, e-mail, address, phone, fax and so on. The problem is, in this case there can be more than one phone number per user. And more than one e-mail. Even more than one address. And a lot more of more-than-one stuff. One approach is to store everything in one table, e.g. serialize...
There's xml data type in Postgres, I never used it before so I'd like to hear opinions. Downsides and upsides vs using regular varchar (or Text) column to store xml. The text I'm going to store is xml, well-formed, UTF-8. No need to search by it (I've read searching by xml is slow). This XML actually is data prepared for PDF generation...
I am attempting to create a profile page that shows the amount of dwarves that are assigned to each corresponding career. I have 4 careers, 2 jobs within each of those careers and of course many dwarves that each have a single job. How can I get a count of the number of dwarves in each of those careers? My solution was to hardcore the...