I'm programming a PL/pgSQL function with this: cols_to_select := according to a set of rules, we get the name of the columns FOR I IN EXECUTE 'SELECT '||cols_to_select||' FROM tabl' LOOP -- how to access to fields of record I without knowing their names? -- END LOOP; ...
Duplicate: MySQL vs PostgreSQL for Web Applications Do you recommend PostgreSQL over MySQL? How different is PostgreSQL to MySQL? I've been toying with PostgreSQL for about a week, and, while I like it, I can't figure out what the specific advantages of PostrgreSQL over MySQL (using InnoDB) are. There seem to be a lot of features,...
consider the following pgSQL statement: SELECT DISTINCT some_field FROM some_table WHERE some_field LIKE 'text%' LIMIT 10; Consider also, that some_table consists of several million records, and that some_field has a b-tree index. Why does the query take so long to execute (several minutes)? What I mean is, why doesnt it lo...
I'm trying to insert binary data (a whirlpool hash) into a PG table and am getting an error: TypeError: not all arguments converted during string formatting code: cur.execute(""" INSERT INTO sessions (identity_hash, posted_on) VALUES (%s, NOW()) """, identity_hash) I tried adding conn.Binary("identit...
I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age. The problem I'm having is that both datetime.datetime.now() and datetime.datetime.utcnow() seem to return timezone unaware timestamps, which results in me getting this error: TypeEr...
Why opensource database like Postgresql and Mysql don't have encrypted stored proc? Is it because of their innate open source philosophy? What are the compelling reasons to encrypt the stored procs? ...
Hi guys, I'm new with postgresql, and already have my first problem.. Well, I wrote some code to understand how transactions work, following step by step the manual. To make it short, I've created 2 tables, user and movements: in the first one there are the name, email and credit columns, in the second the columns from, to, import. So...
Is there a simple way to create a copy of a database or schema in PostgreSQL 8.1? I'm testing some software which does a lot of updates to a particular schema within a database, and I'd like to make a copy of it so I can run some comparisons against the original. ...
Is it possible to prevent deletion of the first row in table on PostgreSQL side? I have a category table and I want to prevent deletion of default category as it could break the application. Of course I could easily do it in application code, but it would be a lot better to do it in database. I think it has something to do with rules o...
I want to add some constraint to my username VARCHAR in the sql table so that if a username exists, a duplicate username in a different case cannot be created. How can I do this? thanks edit: I am using postgres sql, a little syntax help will be greatly appreicated ...
Are there any open source projects in the works to create a column-oriented storage engine for PostgreSQL? I know Yahoo created one in-house, and that there are commercial products built on PostgreSQL that are column-oriented. ...
I'm preforming an aggregate function on multiple records, which are grouped by a common ID. The problem is, I also want to export some other fields, which might be different within the grouped records, but I want to get those certain fields from one of the records (the first one, according to the query's ORDER BY). Starting point exampl...
With tables basically like this: Elements id INT PRIMARY KEY ... Observations id INT PRIMARY KEY ... Data id INT PRIMARY KEY observation_id FOREIGN KEY element_id FOREIGN KEY value FLOAT ... I want to find all observation_ids where there are duplicate element_ids in a single observation_id. For example, if I have D...
I'm running a query against a table in a postgresql database. The database is on a remote machine. The table has around 30 sub-tables using postgresql partitioning capability. The query will return a large result set, something around 1.8 million rows. In my code I use spring jdbc support, method JdbcTemplate.query, but my RowCallbackH...
I have a rather small (ca. 4.5k pageviews a day) website running on Django, with PostgreSQL 8.3 as the db. I am using the database as both the cache and the sesssion backend. I've heard a lot of good things about using Memcached for this purpose, and I would definitely like to give it a try. However, I would like to know exactly what w...
It would seem these days that everyone just goes with MySQL because that's just what everyone goes with. I'm working on a web application that will be handling a large quantity of incoming data and am wondering if I should "just go with MySQL" or if I should take a look at other open-source databases or even commercial databases? Thanks...
I want update a column by adding days to current time. In pseudosyntax it would be: UPDATE foo SET time = current_timestamp + days::integer days is a column in the same table. ...
I find it cumbersome to create a trigger just to get the current total rows of the table without doing COUNT(*) FROM table. I'm thinking if their planned index-organized tables for Postgres 8.5 could make it possible? ...
Is it possible? How? Thanks! ...
I'm using WAMP on windows, which installs PHP, Apache and MySQL. I'm now working on something new that requires PostgreSQL. The current install won't do it for me, as I keep getting these errors: Call to undefined function pg_query() Always undefined function I've installed PostgreSQL 8.3.7-1 for windows, added php_pgsql.dl...