I have two tables one called details and the other called c_details. Both tables are exact the same except for different table names No I a row with data in both of these tables Is it possible to update the row in details with the row in c_details Eg. update details SET (Select * from c_details)? ...
Hi all, some time ago I happend to resolve a PG related problem with this SO question of mine. Basically it's about using row_number over a partition in 8.4. Sadly now I have to create the same thing for 8.2 since one of my customers is on 8.2 and needs it desperatly. What I do know (on 8.4) is the following: SELECT custId, custName,...
I have a query in postgres insert into c_d (select * from cd where ak = '22019763'); And I get the following error ERROR: column "region" is of type integer but expression is of type character varying HINT: You will need to rewrite or cast the expression. ...
I would like to replace some of the sequences I use for id's in my postgresql db with my own custom made id generator. The generator would produce a random number with a checkdigit at the end. So this: SELECT nextval('customers') would be replaced by something like this: SELECT get_new_rand_id('customer') The function would then re...
I'd like to update all records with an empty string in all columns of all tables in PostgreSQL. Is there a way to do this with a query? Or at least how to query for all columns that don't have a NOT NULL constraint. ...
I need to setup my PostgreSQL DB's text encoding to handle non-American English characters that you'd find showing up in languages such as German, Spanish, and French. What character encoding should I use? ...
Hey, I have 2 tables in PostgreSql: 1 - documents: id, title 2 - updates: id, document_id, date and some data: documents: | 1 | Test Title | updates: | 1 | 1 | 2006-01-01 | | 2 | 1 | 2007-01-01 | | 3 | 1 | 2008-01-01 | So All updates are pointing to the same document, but all with different dates for the updates. What I am try...
I had to migrate from a mySql based ruby on rails app to using postgresql. No problems but one so far, and I don't know how to solve it. The migration of data brought ids along with it, and postgresql is now having problems with existing ids: it's not clear to me where it gets the value that it uses to determine the base for nextval: i...
I have the following line in my ActiveRecord model: class Record < ActiveRecord::Base has_many :users, :through => :record_users, :uniq => true, :order => "record_users.index ASC" This is intended to enable me to read out record.users in a way that I order using an index field in the record_users model. The problem is that this f...
Hi, I'm using Spring JdbcTemplate, and I'm stuck at the point where I have a query that updates a column that is actually an array of int. The database is postgres 8.3.7. This is the code I'm using : public int setUsersArray(int idUser, int idDevice, Collection<Integer> ids) { int update = -666; int[] tipi = new int[3]; tipi[0] = j...
Hi, While restoring some database backups I noticed that pg_dump is actually using INSERTS rather than COPY. I am not even specifying -d flag but it's still using INSERTS for every database / table I try to dump which is why restores take hours rather than minutes. According to the pg docs pg_dump should use COPY by default but in my c...
I want to join few attributes in select statement as one for example select id, (name + ' ' + surname + ' ' + age) as info from users this doesn't work, how to do it? I'm using postgreSQL. ...
I have a website that allows users from around the world to submit profiles. Somewhere between storing/retrieving/displaying the characters, they are not rendering correctly. I'm not sure which step is having problems, but here is a breakdown of what is happening. When I do a SELECT from my PostgreSQL DB via the psql command line inte...
I'm occasionally but quite often getting an unhandled exception in cursor.execute (django1.1/db/models/sql/query.py line 2369), using psycopg2 with postgresql. Looks like the database drops connection in some way, so Django crashes. For unhandled exception there is a ticket in Django's bugtrack (#11015), but I'm rather interested in re...
Hi, I'm writing an application in Python with Postgresql 8.3 which runs on several machines on a local network. All machines 1) fetch huge amount of data from the database server ( lets say database gets 100 different queries from a machine with in 2 seconds time) and there are about 10 or 11 machines doing that. 2) After processing ...
What is the best way to emulate Tagged union in databases? I'm talking about something like this: create table t1 { vehicle_id INTEGER NOT NULL REFERENCES car(id) OR motor(id) -- not valid ... } where vehicle_id would be id in car table OR motor table, and it would know which. (assume that motor and car tables have nothing in com...
I need to save a copy of an ActiveRecord object so I'm using something like: @original = Model.find(params[:id]) @copy = @original.clone However, when I attempt to save this to Postgres: PGError: ERROR: null value in column "id" violates not-null constraint In the console: @copy.id => nil How do ...
I'll take the simplest of the SQL functions as an example: CREATE OR REPLACE FUNCTION skater_name_match(INTEGER,VARCHAR) RETURNS BOOL AS $$ SELECT $1 IN (SELECT skaters_skater.competitor_ptr_id FROM skaters_skater WHERE name||' '||surname ILIKE '%'||$2||'%' OR surname||' '||name ILIKE '%'||$2||'%'); $$ LANGUAGE SQL; If I ...
Could someone help me find the equivalent of Timestamp/Rowversion (SQL Server) with PostgreSQL? I'm using NHibernate on Mono (UNIX). I think that Timestamp (PostgreSQL) is the equivalent of Datetime (SQL Server) - it's not what I'm looking for. Edit 1: For those who don't know what is a Timestamp/Rowversion in SQL Server: http://msdn...
Couldn't find anything related to the subject in Zend Framework API. What is the best way to implement Postgres array based property into Zend Model Class? I know one can parse array from "{ smth1, smth2 }" like string, but I still hope that there is more unattended approach in Zend case. Thanks. ...