I have a quite large object tree structure that I persist with Hibernate. This should have been persisted with the root-node first and then the children and so on. However this was not the way that Hibernate did it, it persisted the child first and than updated the reference column in the child when the parent had been persisted. This is...
I have one table that stores values with a point in time: CREATE TABLE values ( value DECIMAL, datetime DATETIME ) There may be many values on each day, there may also be only one value for a given day. Now I want to get the value for each day in a given timespan (e.g. one month) which is nearest to a given time of day. I onl...
I try to compare strings in PostgreSQL. Here are queries that I execute: select 'UK (z'>'Ukraine'; This one returns true; Then I try following one: select 'UK ('>'Ukraine'; This one returns false; I think, that both of these should return false , and on another PostgreSQL server it behaves correctly. But main server is producing...
I want to limit access to username+password table to one query pattern like: SELECT count(id) AS auth_result FROM user WHERE username = '%s' AND password = SHA1('%s') (this query doesn't pretend to be working from the point of injection vulnerability, just an example) Is that possible? or am I missing some different approach? ...
Hi, I'm trying to speed up my django unit test, so I wrote custom database backend using for test purposes another tablespace located in memory. But I found no real advantages, the time my test running in memory remains the same. Then I created two similar tables using different tablespaces and run 10000 inserts for the each one. Time ...
I'm using Postgres, and I have a large number of rows that need to be inserted into the database, that differ only in terms of an integer that is incremented. Forgive what may be a silly question, but I'm not much of a database guru. Is it possible to directly enter a SQL query that will use a loop to programatically insert the rows? ...
My problem is that I want to retrieve both a list of measurements along with a moving average of those measurements. I can do that with this SQL statement (postgresql interval syntax): SELECT time, value, ( SELECT AVG(t2.value) FROM measurements t2 WHERE t2.time BETWEEN t1.time - interval '5 days...
I have a table with some foreign keys, I need to get a report of when these keys change. from | to | timestamp 1 | 2 | 0000 1 | 2 | 0001 1 | 2 | 0002 1 | 3 | 0003 1 | 3 | 0004 1 | 2 | 0005 SELECT from,to,FIRST(timestamp) FROM table GROUP BY from,to; from | to | timestamp 1 | 2 | 0000 1 | 3 | 0003 I...
I am getting the following error: ReportsController#return_search (ActiveRecord::StatementInvalid) "PGError: ERROR: syntax error at or near \"FROM\"\nLINE 5: ...OUNT(contact_postalcards.id) AS postalcard_count, FROM \"cont...\n It works fine locally, but when I push to heroku I get the error: What do I need to change so it will work...
I have one table that has 2 fields one(container_id) for numeric type and another for byte type(coDearntainer_objects) . I would like to read the byte field(container_objects) as a string for corresponding (container_id) field. How could I do this? I am using Postgresql Db Table Structure: CREATE TABLE container ( ct_id numeric, co...
I am currently in the process of setting up a database structure to manage events. Events have properties which are stored in separate tables like 'location', 'timeslots', 'files' etc. This in itself is not so difficult to set up. However, the tool needs to be able to host multiple events at the same time. So, for example a user can m...
Looking to find a good online or on-site training program for PostgreSQL especially dealing with the just released 9.0 . Basically looking at training that focuses on DBA functions, provides optimization techniques and offers insight into new functions of PostGreSQL 9.0 such as Hot-spare... ...
CREATE TABLE dtvs_risk_analizine_girmeme_tanimi_t ( id bigint NOT NULL, aktif boolean NOT NULL, price1 double precision, price2 double precision, priceoperator integer, ) WITH ( OIDS=FALSE ); ALTER TABLE dtvs_risk_analizine_girmeme_tanimi_t OWNER TO postgres; hello oll my table like on the up side. I have a price and I h...
We're using a "1 audit table for each monitored Table"; However, in our case emp(PARENT) table has a child table emp_address which also needs to be monitored, so we have emp_audit and emp_address_audit tables. postgres audit SQL : how to join PARENT and CHILD tables for reporting purposes. /* Employee table */ create table emp ( ...
I am trying to do an inexact join (I am not sure what the proper term is) where I could perform pattern matching. Basically, instead of doing a JOIN like this: .... JOIN .... ON (t1.col = t2.col) I would like to do do something like: .... JOIN .... ON (t1.col ILIKE %(t2.col)% ) The second example is obviously not proper syntax. Is ...
Query SELECT ppc.personid, ppc.category, ppc.phonenumber, ppc.createts FROM person_phone_contacts ppc WHERE CURRENT_TIMESTAMP BETWEEN ppc.createts AND ppc.endts AND CURRENT_DATE BETWEEN ppc.effectivedate AND ppc.enddate ORDER BY ppc.personid, ppc.category, ppc.createts DESC Resulting Data 3742 | Home | xxx-xxx...
Hello, I'm trying to install a shopping cart plugin for django but having a problem when I run manage.py syncdb. When run, it installs 4 tables, then I'm getting the following error message: File "(mypath)/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute return self.cursor.execute(query, args) django....
I am using php and postgresql. I need a function that: connects to a db if not already connected run the query safely put the results into an object For example: I do a query that is 'select * from test'. I get back 2 rows...There are three columns in 'test' (id,fname,lname). I have an object named $ep. I want to be able to put the ...
I try to install Rails 3 on Windows 7, and rails new someapp -d mysql cd someapp bundle install will fail at mysql2 so, there is no way to use MySQL but to stick with SQLite3. But I tried rails new app_postgres -d postgresql cd app_postgres bundle install and it all worked. Does that mean perhaps Rails team favor Postgresql slig...
I am using postgres. I want to delete Duplicate rows. The condition is that , 1 copy from the set of duplicate rows would not be deleted. i.e : if there are 5 duplicate records then 4 of them will be deleted. ...