Re: [GENERAL] pg_dump without blobs

Joshua D. Drake skrev: > Vivek Khera wrote: >> >> On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote: >> >>> I guess the next question is 'what does postgresql considers a blob'? >>> bytea fields? How about a large text with megabytes worth of data? >> >> bytea and text fields are NOT blobs. they

Re: [GENERAL] Foreign key constraint question

Jeff Davis skrev: > On Fri, 2007-07-20 at 19:18 -0500, Perry Smith wrote: >>> The relational model handles inheritance and polymorphism very well if >>> you don't store types as values. >> What if I have just an id for an item? This will happen when another >> table references an item. How do I

Re: [GENERAL] Foreign key constraint question

Alvaro Herrera skrev: > Nis Jørgensen wrote: > >> What if, for instance, I want to render a list of shapes? >> >> To render the shape, I need to get its data, to get its data, I >> need to know what type it is. ISTM that the easiest way to achieve >> this is s

Re: [GENERAL] query to match '\N'

Alban Hertroys skrev: > Presumably he wanted col2 like E'%N%'. > But doesn't \N mean NULL, or would the OP be looking for literal '\N' > strings in his data? Because if he's looking for NULLs it may be better > to query for col2 IS NULL. My guess is that this string was used to signify NULL i

Re: [GENERAL] Optimising SELECT on a table with one million rows

Cultural Sublimation skrev: > Hi, > > I'm fairly new with Postgresql, so I am not sure if the performance > problems I'm having are due to poorly constructed queries/indices, > or if I bumped into more fundamental problems requiring a design of > my database structure. That's why I'm requesting y

Re: [GENERAL] alter table table add column

Ronald Rojas skrev: > Hi, > > Anybody knows how to add column with reference to BEFORE or AFTER any > given column? Let say here's my table structure: > > Column | Type| Modifiers > --+---+--- > surname | character varying | > lastname | chara

Re: [GENERAL] finding reusable ids

Kenji Morishige skrev: > I have a table that creates "check-out" records that stores information when > a particular resource is being utilized. I want to maintain a friendly > shortened ID so people can reference these check outs. > > At any given time, there should not be more than 99 or so

Re: [GENERAL] Removing a schema

Naz Gassiep skrev: > I'm trying to remove a schema and move all the tables to another schema. > I've manually run alter table on every table to move them, however all > the foreign keys still reference the old schema, and there are too many > to do by hand. > > Is there an easy way to update one o

Re: [GENERAL] SQL query with IFs (?) to "Eliminate" NULL Values

Stefan Schwarzer skrev: > Hi there, > > I want to calculate per Capita values on-the-fly, taking for example the > "Total GDP" data set and divide it by "Total Population". Now, each of > these data sets have a couple of "0" or "-" values (the latter being > the indicator for : "no data availa

Re: [GENERAL] Need suggestion on how best to update 3 million rows

hubert depesz lubaczewski skrev: > On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote: >> create index tmp_idx on table(number) where number != trim(number); >> analyze table; >> update table set number = trim(number) where number != trim(number); > > dont use !=. use <>. != does somet

[GENERAL] Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Ardian Xharra skrev: > *From:* Anoo Sivadasan Pillai >> I am not using any sequences, The following batch can reproduce the >> behaviour. >> CREATE TABLE master ( m1 INT primary key , m2 int unique ) ; >> INSERT INTO master VALUES ( 1, 1 ) ; >> INSERT INTO master VAL

[GENERAL] Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Alban Hertroys skrev: > Nis Jørgensen wrote: >> If you can't wait, you are probably better off working around the >> problem. Standard solution is to do: >> >> UPDATE master SET m2 = -m2; >> UPDATE master SET m2 = -m2+1; >> >> or something sim

Re: [GENERAL] Normalized Tables & SELECT [was: Find "smallest common year"]

A few more comments on your table design. Stefan Schwarzer skrev: > So, instead of the earlier mentioned database design, I would have > something like this: > >- one table for the country names/ids/etc. (Afghanistan, 1; Albania, > 2) There is a well-established natural key for countrie

Re: [GENERAL] Normalized Tables & SELECT [was: Find "smallest common year"]

Stefan Schwarzer skrev: >>> What would you recommend for say, 500 global national statistical >>> variables, >>> 500 regional and 500 subregional and 500 global aggregations? Years >>> being >>> covered having something between 10 and 60 years for each of these >>> variables. All available for 240

[GENERAL] Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Alban Hertroys skrev: > Nis Jørgensen wrote: >> Alban Hertroys skrev: >>> Would something like >>> >>> UPDATE master set m2 = master2.m2 >>> FROM ( >>> SELECT m2 +1 >>> FROM master m >>> WHERE m.master_id = m

[GENERAL] Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Alban Hertroys skrev: > > Nis Jørgensen wrote: >> Alban Hertroys skrev: > >> As I said, I don't understand what you think it does. What you are doing >> is similar to writing >> >> SELECT m2 >> FROM master, ( >> SELECT m2 >>

[GENERAL] Re: ERROR: invalid byte sequence from psql - Works perfectly from pgAdmin III query window

Albe Laurenz skrev: > Anoo Sivadasan Pillai wrote: >> Why the Fun_ABC1 is created and Fun_ABC12 is raising the >> following error, while run through psql, ( I Could create >> both the functions from PgAdmin III query ) >> >> ERROR: invalid byte sequence for encoding "UTF8": 0x93 > > Because th

Re: [GENERAL] Calculation of per Capita on-the-fly - problems with SQL syntax

Stefan Schwarzer skrev: > Hi there, > > I need to calculate per Capita data on-the-fly. My table for a given > variable looks like this: > > year|value |id_country > --- > 2001| 123 | 1 > 2002| 125 |

Re: [GENERAL] SQL spec/implementation question: UPDATE

Albe Laurenz skrev: > Tom Lane wrote: >>> I don't have handy a spec guide. Does this mean that MySQL >>> is indeed showing incorrect behavior? >> I think this is really outside the spec. > [...] >> There is not anything I can see addressing whether an >> "update" should or should not be considere

Re: [GENERAL] Changing column names in tables 2

Tony Cade skrev: > select relfilenode from pg_class where relname='rates' > > update pg_attribute set attname=lower(attname) where attnum >0 and > attrelid= ( from above query) Instead of the second one, do SELECT 'ALTER TABLE rates RENAME COLUMN ' || attname || ' TO ' || lower(attname