Re: [SQL] tid_le comparison for tuple id (ctid) values?
george young wrote: Well, I don't have any need for it to correlate with the age of the tuple. My use of step.ctid How do you know there is only 1 duplicate? Anyway, if (x,y) are the same but (z) is not then you can compare against max(z) or min(z). Something like: SELECT t1.x AS update_me_x, t1.y AS update_me_y, t1.z AS update_me_z FROM test_tbl AS t1, ( SELECT x,y,max(z) AS max_z FROM test_tbl GROUP BY x,y ) AS t2 WHERE t1.x = t2.x AND t1.y = t2.y AND t1.z = t2.max_z -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] tid_le comparison for tuple id (ctid) values?
Richard Huxton wrote: george young wrote: Well, I don't have any need for it to correlate with the age of the tuple. My use of step.ctid How do you know there is only 1 duplicate? Anyway, if (x,y) are the same but (z) is not then you can compare against max(z) or min(z). Something like: SELECT t1.x AS update_me_x, t1.y AS update_me_y, t1.z AS update_me_z FROM test_tbl AS t1, ( SELECT x,y,max(z) AS max_z FROM test_tbl GROUP BY x,y ) AS t2 WHERE t1.x = t2.x AND t1.y = t2.y AND t1.z = t2.max_z Oh, of course the easiest way to do it is to add an additional column of type SERIAL to your temp-table. That way you have your rows nicely numbered as you import them. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Please help to wite the constraint.
> I can't understand why you are doing this big cycle.. but certainly > when constraints can't help you.. you can use triggers to enforce > integrity.. In my system I have to have arbitrary contact info records about my users. I mean not only fixed like names, email and phone but many many other fields which could be created dynamically. And all of them can ba in different languages. So this was kind of example: contact_info_fields - arbitrary contact info fields contact_info_records - users' contact info records countries - sample contact info field with fixed range of values countires_names - values of the field Well, that was it, nothing more. Does it really look so wierd? Please, I appreciate any comments. > On 11/20/05, Grigory O. Ptashko <[EMAIL PROTECTED]> wrote: >> Hello, everybody! >> >> I don't whether it is possible to do the following but anyway I can't. >> I need to write a constraint as described below. >> Here are four tables: >> >> >> CREATE TABLE countries >> (id SERIAL, >> name VARCHAR(255), >> PRIMARY KEY (id) >> ); >> >> CREATE TABLE countries_names >> (id INT NOT NULL, >> id_lang INT NOT NULL, >> name VARCHAR(255), >> PRIMARY KEY (id, id_lang), >> FOREIGN KEY (id) REFERENCES countries (id), >> FOREIGN KEY (id_lang) REFERENCES lang (id) >> >> ); >> >> CREATE TABLE contact_info_fields >> (id SERIAL, >> name VARCHAR(255) NOT NULL, >> PRIMARY KEY (id) >> ); >> >> CREATE TABLE contact_info_records >> (id_user INT NOT NULL, >> id_ci_field INT NOT NULL, >> id_lang INT NOT NULL, >> value TEXT, >> PRIMARY KEY (id_user, id_ci_field, id_lang), >> FOREIGN KEY (id_user) REFERENCES users (id), >> FOREIGN KEY (id_ci_field) REFERENCES contact_info_fields (id), >> FOREIGN KEY (id_lang) REFERENCES lang (id) >> ); >> >> >> >> The last table contains contact information records of different types. >> These types are taken from the table contact_info_fields. In particular, >> there can be the type 'country' say with id=1. Then the contact_info_records >> table can contain the following info: id_ci_field=1 and the VALUE field must >> contain a country's name but ONLY if it exists in the countries table >> (column 'name'). So it turns out to be a wierd foreign key. Is it possible >> to write such a constraint? >> >> Thanks! >> >> >> ---(end of broadcast)--- >> TIP 2: Don't 'kill -9' the postmaster >> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] idea for a geographically distributed database: how best to implement?
David Bath wrote: > There are a couple of philosophical perspectives I've come across in > previous > work with cadastral data that may be useful...[snipped] Thanks, David In this particular application, structures such as postcode sectors, administrative boundaries etc. are not really of much importance, as most stuff is a simple coordinate based searches. Even with the problem partitioned into disjoint regions, within each region, the search remains trivial, as all the data that the user is allowed to access will be stored with that region (this includes data replicated from neighbouring regions). In this context, the interesting task isn't so much the actual database searching, or the exact definition of the disjoint regions. The interesting task is to define a system which can dynamically remap the hosting of regions to specific servers, so that no one server gets too busy. As demand grows, I simply plug in more 4 blades and press the 'reconfigure' button (Sorry - I was dreaming for a moment...) The only limiters are the number of servers available and the activity within a single region (which must be servable by a single server), but given the highly localised nature of the application, the regions can be very small, and I don't expect to ever see a region with more than 1GB of data - the aim being for all the data to be resident in RAM. So far, I've already seen some issues. I've been looking at slony-1 to handle the replication between adjacent regions, and not only is it asynchronous (I was hoping otherwise...slony-2 seems a long way off), but changing the db schema has ramifications too. (I.e. changing the schema means redefining each replication). Still - no show stoppers yet. Thanks for your insights, Andy ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Triggers
I'm trying to understand triggers. I have read the documentation in the manual as well as the few pages in the Douglas book about the subject, but I don't see how to implement a trigger that simply updates a 'last_edit' date field in my 'persons' table whenever I do an insert or update into my 'participants' table; that is a trigger that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE persons.person_id = participants.person_fk". Is that even possible? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Foreign key to 2 tables problem
Hi, Is there a way to create a foreign key to 2 tables: e.g. a bankaccount table that has a column "owner", that must point to a record in either the customer or the supplier table? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Triggers
O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > I'm trying to understand triggers. I have read the documentation in the > manual as well as the few pages in the Douglas book about the subject, > but I don't see how to implement a trigger that simply updates a > 'last_edit' date field in my 'persons' table whenever I do an insert or > update into my 'participants' table; that is a trigger that basically > does an "UPDATE persons SET 'last_edit' = NOW() WHERE persons.person_id > = participants.person_fk". Is that even possible? > smth like: foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' foodb'# BEGIN foodb'# UPDATE dad set lastedit=now() where id=new.dadid; foodb'# RETURN new; foodb'# END; foodb'# ' foodb-# LANGUAGE plpgsql; CREATE FUNCTION foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR EACH ROW EXECUTE PROCEDURE upd_dad(); -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Foreign key to 2 tables problem
As far as I'm aware, not without using an intermediatary table (id, cust_id, supplier_id) . Otherwise, how would you know which table the foreign key was referencing? That said, an intermediatary table isn't a very clean solution; What problem are you trying to solve, exactly? Kind Regards, Neil. On 11/22/05, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote: > Hi, > > Is there a way to create a foreign key to 2 tables: e.g. a bankaccount > table that has a column "owner", that must point to a record in either > the customer or the supplier table? > > > TIA > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > e-mail: [EMAIL PROTECTED] > web: www.askesis.nl > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Foreign key to 2 tables problem
O Joost Kraaijeveld έγραψε στις Nov 22, 2005 : > Hi, > > Is there a way to create a foreign key to 2 tables: e.g. a bankaccount > table that has a column "owner", that must point to a record in either > the customer or the supplier table? While there are techniques to accomplish this, i see a problem: How do you know if a certain value in owner is to be joined with e.g. customer and not supplier?? The right way is to have 2 columns that can be null, pointing to customer,supplier respectively, and then write a trigger to ensure that exactly one is not null. > > > TIA > > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Foreign key to 2 tables problem
I've never seen anything like that. I'm sure it's conceivable that you could write a weird trigger for it, but you have to consider maintainability, and what your queries are going to look like. I haven't seen your datamodel, but it would seem that you could accomplish what you're looking for by having two separate foriegn key columns in the bankaccount table, one for the customer and one for the supplier. While your queries may end up somewhat funky, I can't imagine they'd be any worse than what would occur with what you're suggesting. However, if customers or suppliers can have multiple accounts, you are going to need an intermediate table, as suggested by Neil. Joost Kraaijeveld wrote: Hi, Is there a way to create a foreign key to 2 tables: e.g. a bankaccount table that has a column "owner", that must point to a record in either the customer or the supplier table? TIA ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Foreign key to 2 tables problem
On Tue, 2005-11-22 at 16:24 +0100, Joost Kraaijeveld wrote: > Hi, > > Is there a way to create a foreign key to 2 tables: e.g. a bankaccount > table that has a column "owner", that must point to a record in either > the customer or the supplier table? No. What you need is an owner table that customers and suppliers are inherited from. Put your 'entity' data into the owner table. Put customer and supplier specific information into the customer and supplier structures. create table owner (owner_name varchar(120) primary key, ...); create table customer (customer_name varchar(120) references owner, ...); create table supplier (supplier_name varchar(120) references owner, ...); create table bankaccount (owner_name varchar(120) references owner); You can use a periodic check to ensure that all owners are a customer or supplier just incase your code breaks. Incidentally, this also allows a single entity with a single bankaccount to be both a customer and a supplier. They can supply you with product X and purchase product Y without 2 different accounts. -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Foreign key to 2 tables problem
However, if customers or suppliers can have multiple accounts, you are going to need an intermediate table, as suggested by Neil. Scratch that. If accounts can have multiple owners you'll need an intermediate table. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Triggers
On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : >> I'm trying to understand triggers. I have read the documentation in >> the manual as well as the few pages in the Douglas book about the >> subject, but I don't see how to implement a trigger that simply >> updates a 'last_edit' date field in my 'persons' table whenever I do >> an insert or update into my 'participants' table; that is a trigger >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE >> persons.person_id = participants.person_fk". Is that even possible? > >smth like: >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' >foodb'# BEGIN >foodb'# UPDATE dad set lastedit=now() where id=new.dadid; >foodb'# RETURN new; >foodb'# END; >foodb'# ' >foodb-# LANGUAGE plpgsql; >CREATE FUNCTION >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR > EACH ROW EXECUTE PROCEDURE upd_dad(); leif=> create or replace function update_last_edit() returns trigger as $$ leif$> begin leif$> update persons set last_edit=now() where person_id=new.person_fk; leif$> return new; leif$> end; leif$> $$ language plpgsql; CREATE FUNCTION leif=> create trigger update_last_edit after insert or update on participants leif-> for each row execute procedure update_last_edit(); CREATE TRIGGER leif=> insert into participants (participant_id,person_fk) values (1,1); ERROR: record "new" has no field "last_edit" CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment leif=> What am I missing? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Triggers
O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: > >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > >> I'm trying to understand triggers. I have read the documentation in > >> the manual as well as the few pages in the Douglas book about the > >> subject, but I don't see how to implement a trigger that simply > >> updates a 'last_edit' date field in my 'persons' table whenever I do > >> an insert or update into my 'participants' table; that is a trigger > >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE > >> persons.person_id = participants.person_fk". Is that even possible? > > > >smth like: > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' > >foodb'# BEGIN > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid; > >foodb'# RETURN new; > >foodb'# END; > >foodb'# ' > >foodb-# LANGUAGE plpgsql; > >CREATE FUNCTION > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR > > EACH ROW EXECUTE PROCEDURE upd_dad(); > > leif=> create or replace function update_last_edit() returns trigger as > $$ > leif$> begin > leif$> update persons set last_edit=now() where person_id=new.person_fk; > leif$> return new; > leif$> end; > leif$> $$ language plpgsql; > CREATE FUNCTION > leif=> create trigger update_last_edit after insert or update on > participants > leif-> for each row execute procedure update_last_edit(); > CREATE TRIGGER > leif=> insert into participants (participant_id,person_fk) values (1,1); > ERROR: record "new" has no field "last_edit" > CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment ^ > leif=> > > What am I missing? > apparently some forgotten process_last_edited() function. -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Triggers
Try: create or replace function update_last_edit() returns trigger as $$ begin new.last_edit=now(); return new; end; $$ language plpgsql; On 11/22/05, Leif B. Kristensen <[EMAIL PROTECTED]> wrote: > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: > >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > >> I'm trying to understand triggers. I have read the documentation in > >> the manual as well as the few pages in the Douglas book about the > >> subject, but I don't see how to implement a trigger that simply > >> updates a 'last_edit' date field in my 'persons' table whenever I do > >> an insert or update into my 'participants' table; that is a trigger > >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE > >> persons.person_id = participants.person_fk". Is that even possible? > > > >smth like: > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' > >foodb'# BEGIN > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid; > >foodb'# RETURN new; > >foodb'# END; > >foodb'# ' > >foodb-# LANGUAGE plpgsql; > >CREATE FUNCTION > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR > > EACH ROW EXECUTE PROCEDURE upd_dad(); > > leif=> create or replace function update_last_edit() returns trigger as > $$ > leif$> begin > leif$> update persons set last_edit=now() where person_id=new.person_fk; > leif$> return new; > leif$> end; > leif$> $$ language plpgsql; > CREATE FUNCTION > leif=> create trigger update_last_edit after insert or update on > participants > leif-> for each row execute procedure update_last_edit(); > CREATE TRIGGER > leif=> insert into participants (participant_id,person_fk) values (1,1); > ERROR: record "new" has no field "last_edit" > CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment > leif=> > > What am I missing? > -- > Leif Biberg Kristensen | Registered Linux User #338009 > http://solumslekt.org/ | Cruising with Gentoo/KDE > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] deferrable on unique
table t1: id integer primary key, seq integer not null unique the seq is for ordering the rows as the user likes. however, if the rows are moved around, eg begin update t1 set seq=4 where id=5 update t1 set seq=5 where id=4 end will bomb because the first update has two rows of seq=4 (although correct after the transaction). I thought "deferrable initally deferred" would fix this, but the phrase is not allowed on unique. is this correct? any ideas? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Triggers
And change AFER INSERT to BEFORE INSERT On 11/22/05, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > > > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: > > >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > > >> I'm trying to understand triggers. I have read the documentation in > > >> the manual as well as the few pages in the Douglas book about the > > >> subject, but I don't see how to implement a trigger that simply > > >> updates a 'last_edit' date field in my 'persons' table whenever I do > > >> an insert or update into my 'participants' table; that is a trigger > > >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE > > >> persons.person_id = participants.person_fk". Is that even possible? > > > > > >smth like: > > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' > > >foodb'# BEGIN > > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid; > > >foodb'# RETURN new; > > >foodb'# END; > > >foodb'# ' > > >foodb-# LANGUAGE plpgsql; > > >CREATE FUNCTION > > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR > > > EACH ROW EXECUTE PROCEDURE upd_dad(); > > > > leif=> create or replace function update_last_edit() returns trigger as > > $$ > > leif$> begin > > leif$> update persons set last_edit=now() where person_id=new.person_fk; > > leif$> return new; > > leif$> end; > > leif$> $$ language plpgsql; > > CREATE FUNCTION > > leif=> create trigger update_last_edit after insert or update on > > participants > > leif-> for each row execute procedure update_last_edit(); > > CREATE TRIGGER > > leif=> insert into participants (participant_id,person_fk) values (1,1); > > ERROR: record "new" has no field "last_edit" > > CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment > ^ > > > leif=> > > > > What am I missing? > > > > apparently some forgotten process_last_edited() function. > > -- > -Achilleus > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] deferrable on unique
It's a low-tech solution but you could: begin update t1 set seq=-1 where id=5 update t1 set seq=5 where id=4 update t1 set seq=4 where id=-1 end This is assuming that you don't naturally have -1 as a valid value of that column. chester c young wrote: table t1: id integer primary key, seq integer not null unique the seq is for ordering the rows as the user likes. however, if the rows are moved around, eg begin update t1 set seq=4 where id=5 update t1 set seq=5 where id=4 end will bomb because the first update has two rows of seq=4 (although correct after the transaction). I thought "deferrable initally deferred" would fix this, but the phrase is not allowed on unique. is this correct? any ideas? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Triggers
On Tuesday 22 November 2005 18:07, Achilleus Mantzios wrote: >O Leif B. Kristensen ?? Nov 22, 2005 : >> What am I missing? > >apparently some forgotten process_last_edited() function. Yes -- an earlier attempt at the same thing ... I finally managed to create my first trigger: CREATE OR REPLACE FUNCTION update_last_edit() RETURNS TRIGGER AS $$ BEGIN UPDATE persons SET last_edit=NOW() WHERE person_id=NEW.person_fk; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_last_edit BEFORE INSERT OR UPDATE ON participants FOR EACH ROW EXECUTE PROCEDURE update_last_edit(); And it's even working as it should :-) Thanks, guys. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE
[SQL] can UNIQUEness of TEXT datatype really be guaranteed?
I am in the process re-implementing some pgsql table declarations into a MySQL database. I have this one line where I say ... fieldname TEXT UNIQUE, ... in my pgsql table because I want to ensure that no one enters the same thing in this field and I didn't choose VARCHAR type because I have no idea how possibly long I might need to let the field be... ... but MySQL doesn't like this because a TEXT datatype is considered a BLOB that can't be indexed and it can't guarantee will be UNIQUE apparently... So I can't help but wonder, can Postgres really guarantee a TEXT field to be UNIQUE... or is declaring a TEXT field UNIQUE something an uninformed, novice user would do?... or is it something indicative of the strength and/or weeknesses that separate the functionality of the two DBMSs. Ferindo Middleton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] can UNIQUEness of TEXT datatype really be guaranteed?
On 11/22/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I am in the process re-implementing some pgsql table declarations into a > MySQL database. I have this one line where I say > ... > fieldname TEXT UNIQUE, > ... > in my pgsql table because I want to ensure that no one enters the same thing > in this field and I didn't choose VARCHAR type because I have no idea how > possibly long I might need to let the field be... > > ... but MySQL doesn't like this because a TEXT datatype is considered a BLOB > that can't be indexed and it can't guarantee will be UNIQUE apparently... > > So I can't help but wonder, can Postgres really guarantee a TEXT field to be > UNIQUE... or is declaring a TEXT field UNIQUE something an uninformed, novice > user would do?... or is it something indicative of the strength and/or > weeknesses that separate the functionality of the two DBMSs. > > Ferindo Middleton > TEXT fileds can be indexed in postgres and AFAIK it can be indexed with a unique index (and i don't see any good reason to think it can't guarantee uniqueness)... but you have a limitation, btree index rows can only have certain size (i don't remember the numbers now)... so if you exceed that size (and in a text field you can) you will get an error... you have to control within your application that... -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] can UNIQUEness of TEXT datatype really be guaranteed?
<[EMAIL PROTECTED]> writes: > So I can't help but wonder, can Postgres really guarantee a TEXT field to be > UNIQUE... or is declaring a TEXT field UNIQUE something an uninformed, novice > user would do?... or is it something indicative of the strength and/or > weeknesses that separate the functionality of the two DBMSs. In PG, it will work as long as no entry is too large to fit into a btree index entry (from memory, about 2700 bytes after compression, so the practical limit is probably 4KB or so). If you think you might have entries exceeding a few KB, you could use the trick of declaring a unique functional index on a checksum: create unique index myindex on mytable (md5(fieldname)); This will work as long as you don't get any md5 hash collisions, which is probably not a problem in practice. It will guarantee uniqueness in any case; the risk is that you might get false matches causing rejection of inputs that actually are distinct. A possibly simpler-to-understand way is to demand uniqueness in the first couple KB: create unique index myindex on mytable (substr(fieldname,1,2000)); regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] unplanned sub-select error?
Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
I have a query:
insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status,
ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from
prd_part where pnum = 1014),'work','2005-Nov-15',50,75);
That used to work fine under 7.1.3 but now gives the error:
ERROR: cannot handle unplanned sub-select
You need to offer a little more context, like what PG version you are
using now and what is the underlying DDL --- I suspect some rules or
views are involved here, but you didn't show them to us.
Sorry, you're right. I have now confirmed that this only happens when
updating via a view/rule (as you suspected). Attached is a minimalist
sql file that demonstrates the same error message from a blank
database. I'm using 8.1.0. I'm pretty sure this problem did not exist
on 8.0.3.
Kyle
-- Expose the "unplanned sub-select" error message
create table parts (
partnum varchar(18) primary key,
cost float8
);
create table shipped (
ttype char(2),
ordnum int4,
partnum varchar(18) references parts,
value float8,
primary key (ttype, ordnum)
);
create view shipped_view as
select * from shipped where ttype = 'wt';
create rule shipped_view_insert as on insert to shipped_view
do instead insert into shipped
(ttype, ordnum, partnum, value)
values
('wt', new.ordnum, new.partnum, new.value);
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view
(ordnum, partnum, value)
values
(100,1,(select cost from parts where partnum = 1));
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] unplanned sub-select error?
Kyle Bateman <[EMAIL PROTECTED]> writes: > Sorry, you're right. I have now confirmed that this only happens when > updating via a view/rule (as you suspected). Attached is a minimalist > sql file that demonstrates the same error message from a blank > database. I'm using 8.1.0. I'm pretty sure this problem did not exist > on 8.0.3. Thanks for the test case. I've confirmed it fails here in CVS tip but not in 8.0 branch, so indeed it must be a new bug. Will look into it. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] argument type problem with plpgsql function
I wrote a little function that has to work with big numbers CREATE OR REPLACE FUNCTION blast_evalue(seq_len bigint, db_size bigint, bit_score double precision) RETURNS double precision AS $$ BEGIN RETURN 2^(bit_score) * db_size * seq_len; END; $$ LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT; but it doesn't work properly unless I cast the db_size parameter when I call the function: select blast_evalue(273, 8903836, -55.4546); blast_evalue -- 2430747228 <- wrong number. This is 273 * 8903836 (1 row) select blast_evalue(273, 8903836::bigint, -55.4546); blast_evalue - 4.9231356421437e-08 <- that's correct (1 row) I don't understand why the cast is necessary. Is there a way to make this work without it? Thanks Luca ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] argument type problem with plpgsql function
On Tue, 22 Nov 2005, Luca Pireddu wrote: > I wrote a little function that has to work with big numbers > > CREATE OR REPLACE FUNCTION blast_evalue(seq_len bigint, db_size bigint, > bit_score double precision) > RETURNS double precision AS $$ > BEGIN > RETURN 2^(bit_score) * db_size * seq_len; > END; > $$ > LANGUAGE 'plpgsql' > IMMUTABLE > RETURNS NULL ON NULL INPUT; > > but it doesn't work properly unless I cast the db_size parameter when I call > the function: > > select blast_evalue(273, 8903836, -55.4546); > blast_evalue > -- >2430747228 <- wrong number. This is 273 * 8903836 > (1 row) > > select blast_evalue(273, 8903836::bigint, -55.4546); > blast_evalue > - > 4.9231356421437e-08 <- that's correct > (1 row) > > I don't understand why the cast is necessary. Is there a way to make this > work without it? I got the same answer (the second) for both calls from my 8.0 and 8.1 setups, what version were you trying on? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] argument type problem with plpgsql function
On November 22, 2005 17:13, Stephan Szabo wrote: > On Tue, 22 Nov 2005, Luca Pireddu wrote: > [snip] > > I got the same answer (the second) for both calls from my 8.0 and 8.1 > setups, what version were you trying on? > I forgot to mention that. I'm using version 8.0.4, built from source. I just tried it at home on a postgresql 8.0.3 server (debian package) and it worked the way it's supposed to. Puzzling... Luca ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] argument type problem with plpgsql function
Luca Pireddu <[EMAIL PROTECTED]> writes: > I just tried it at home on a postgresql 8.0.3 server (debian package) and it > worked the way it's supposed to. Puzzling... Maybe you have more than one blast_evalue() function with different argument types? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] argument type problem with plpgsql function
On November 22, 2005 20:24, Tom Lane wrote: > Luca Pireddu <[EMAIL PROTECTED]> writes: > > I just tried it at home on a postgresql 8.0.3 server (debian package) and > > it worked the way it's supposed to. Puzzling... > > Maybe you have more than one blast_evalue() function with different > argument types? > You were right! A blast_evalue(integer, integer, double precision). Thank you for your help. Luca ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Triggers
O Neil Saunders έγραψε στις Nov 22, 2005 : > And change AFER INSERT to BEFORE INSERT 1) it doesnt make any difference since we are updating a different table than the trigger's one 2) Your email text comes really garbled > -- -Achilleus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
