postgresql

Strange: Planner takes decision with lower cost, but (very) query long runtime

Facts: PGSQL 8.4.2, Linux I make use of table inheritance Each Table contains 3 million rows Indexes on joining columns are set Table statistics (analyze, vacuum analyze) are up-to-date Only used table is "node" with varios partitioned sub-tables Recursive query (pg >= 8.4) Now here is the explained query: WITH RECURSIVE ...

How to use serial / autonumber in a PostgreSQL / Access combination

I'm working up a database in PostgreSQL (8.3) that users will interact with (update and query) via Microsoft Access 2003. Several tables have primary keys defined as 'serial' in PostgreSQL. These columns, when linked into Access, show up as 'Number' and don't auto-increment when inserting new data into the tables via Access. Is there ...

How can I execute a SQL query in emacs lisp?

I want to execute an SQL query and get its result in elisp: (let ((results (do-sql-query "SELECT * FROM a_table"))) (do-something-with results)) I'm using Postgres, and I already know all of my connection information (host, username, password, db et al) I just want to execute the query and get the result back, synchronously. ...

Why is Postgres doing a Hash in this query?

I have two tables: A and P. I want to get information out of all rows in A whose id is in a temporary table I created, tmp_ids. However, there is additional information about A in the P table, foo, and I want to get this info as well. I have the following query: SELECT A.H_id AS hid, A.id AS aid, P.foo, A.pos, A.size FROM ...

LOCK TABLE as a prepared statement?

Does it make sense to prepare a LOCK TABLE statement? I'm not really concerned about efficiency here, because it would only execute once a transaction and at most a few times a minute. I'm more wondering if the concept is meaningful. I'm using the PostgreSQL JDBC driver, if it matters. ...

get n records at a time from a temporary table

I have a temporary table with about 1 million entries. The temporary table stores the result of a larger query. I want to process these records 1000 at a time, for example. What's the best way to set up queries such that I get the first 1000 rows, then the next 1000, etc.? They are not inherently ordered, but the temporary table just has...

create temporary table from cursor

Is there any way, in PostgreSQL accessed from Python using SQLObject, to create a temporary table from the results of a cursor? Previously, I had a query, and I created the temporary table directly from the query. I then had many other queries interacting w/ that temporary table. Now I have much more data, so I want to only process 10...

Inserting Newline from XML to Database

I am trying to parse this xml document in which a newline is required for certain fields and must be inserted into the database with the newline. But I've been running into problems. 1)First Problem: \n Character The first problem I had was using the \n like below. <javascript>jquery_ui.js\nshadowbox_modal.js\nuser_profile.js\ntableso...

SQL for selecting only the last item from a versioned table

I have a table with the following structure: CREATE TABLE items ( id serial not null, title character varying(255), version_id integer DEFAULT 1, parent_id integer, CONSTRAINT items_pkey PRIMARY KEY (id) ) So the table contains rows that looks as so: id: 1, title: "Version 1", version_id: 1, parent_id: 1 id: 2, ti...

SQL to join to the best matching row

I have a wiki system where there is a central table, Article, that has many Revisions in their own table. The Revisions each contain a created_at time and date column. I want to update the Articles to contain a denormalized field sort_name from the most recent Revision's name field. What SQL command can I issue to fill in each Article...

how does one _model_ data from relational databases in clojure ?

I have asked this question on twitter as well the #clojure IRC channel, yet got no responses. There have been several articles about Clojure-for-Ruby-programmers, Clojure-for-lisp-programmers.. but what is the missing part is Clojure for ActiveRecord programmers . There have been articles about interacting with MongoDB, Redis, etc. - ...

Convert PostgreSQL array to PHP array

I have trouble reading Postgresql arrays in PHP. I have tried explode(), but this breaks arrays containing commas in strings, and str_getcsv() but it's also no good as PostgreSQL doesn't quote the Japanese strings. Not working: explode(',', trim($pgArray['key'], '{}')); str_getcsv( trim($pgArray['key'], '{}') ); Example: // print_r(...

Which are the criteria for defining " as mandatory or not in a Postgresql query?

I am getting started with Postgresql, I converted a MS SQL DB but I cannot understand why SELECT * FROM MYTABLE doesn't work while SELECT * FROM "MYTABLE" does Moreover from another machine, connecting to the same DB I can run SELECT * FROM MYTABLE --(without ") All tests were done by using PGAdmin III Windows client. Postgresq...

Search sort by parameter match count in the query? PostgreSQL

I am working on a search query in PostgreSQL, and one of the things I do is sort my query results by the number of parameters matched. I have no clue how this can be done. Does anyone have a suggestion or solution? Table brand color type engine Ford Blue 4-door V8 Maserati Blue 2-door V12 Saturn G...

Hibernate+PostgreSQL throws JDBCConnectionException: Cannot open connection

I write an Test Java APP and it works right BUt this Web app throws an exception like that with to same cfg.xml file <property name="hibernate.bytecode.use_reflection_optimizer">false</property> <property name="hibernate.connection.autocommit">true</property> <property name="hibernate.connection.release_mode">auto</property> <property n...

Creating tables in pgadmin sql editor ?

I am failing in creating tables via the PGadmin III SQL editor - even if the syntax is generated by the frontend: CREATE TABLE testtable ( id integer, "name" character varying(100) ) WITH ( OIDS = FALSE ) ; Error message is in german, but basically says that there's supposed to be a syntax error.. FEHLER: Syntaxfehler bei ...

Read-only on *all* tables in postgresql (even INHERITED ones)

Building on how-do-you-create-a-read-only-user-in-postgresql, my situation is more complex. Each hour, new INHERITED tables are created. Any (easy?) programmatic way to all our read-only user privileges on these as well? Trigger? Cronjob that looks for new ones? ...

When to use inherited tables in PostgreSQL?

In which situations you should use inherited tables? I tried to use them very briefly and inheritance didn't seem like in OOP world. I thinked they worked like this: Table USERS which has all fields required for all user levels. Tables like MODERATORS, ADMINS, BLOGGERS, etc but fields are not checked from parent. For example USERS has ...

How do I INSERT and SELECT data with partitioned tables?

I set up a set of partitioned tables per the docs at http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html CREATE TABLE t (year, a); CREATE TABLE t_1980 ( CHECK (year = 1980) ) INHERITS (t); CREATE TABLE t_1981 ( CHECK (year = 1981) ) INHERITS (t); CREATE RULE t_ins_1980 AS ON INSERT TO t WHERE (year = 1980) DO INSTEA...

How do i change column type in Heroku?

I am trying to rake the db:migrations into my heorku instance and I get an error. The FAQ described my error as below: Cannot change column type Example: PGError: ERROR: column “verified_at” cannot be cast to type “date” Cause: PostgreSQL doesn’t know how to cast all the rows in that table to the specified type. Mo...