[SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
Hello. Is it possible to change an FK constraint from NOT DEFERRABLE (the default) to DEFERRABLE without dropping and re-creating it? One idea that came up was to create a parallel set of constraints which perform the same checks as the existing ones as DEFERRABLE (and then drop the old set), but the objection there was that it'd lock the tables during the initial check. We're having a fairly serious deadlock issue and the thinking goes that Tom's suggestion here http://www.webservertalk.com/archive139-2004-8-364172.html to defer FK checks until transaction commit would maybe help. Right now we can't try this because all the FK checks where created with default settings. We'd like to avoid taking the database down for recreating foreign keys. Regards, Frank ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ... On Wed, 30 Mar 2005 11:07:32 +0200, <[EMAIL PROTECTED]> wrote: Hello. Is it possible to change an FK constraint from NOT DEFERRABLE (the default) to DEFERRABLE without dropping and re-creating it? One idea that came up was to create a parallel set of constraints which perform the same checks as the existing ones as DEFERRABLE (and then drop the old set), but the objection there was that it'd lock the tables during the initial check. We're having a fairly serious deadlock issue and the thinking goes that Tom's suggestion here http://www.webservertalk.com/archive139-2004-8-364172.html to defer FK checks until transaction commit would maybe help. Right now we can't try this because all the FK checks where created with default settings. We'd like to avoid taking the database down for recreating foreign keys. Regards, Frank ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
On Wed, Mar 30, 2005 at 11:48:31AM +0200, PFC wrote: > > Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ... ALTER CONSTRAINT? I did check for that, and it does not appear to exist?! That's why I asked ... Rgds, Frank ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] New record position
Hello,
I am using Slackware Linux 10, Postgresql 8.0.1.
My computer had a incorrectly power down (last week) and I have executed the
vacuum command:
VACCUM FULL ANALYZE VERBOSE;
to recicle and verify my database.
Before the power-down, all records had inserted into a table have displayed at
LAST record. Like:
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
INSERT into tb1 values ('rec2','vl2');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec2 | vl2
But After the power-down and vacuum, the new records inserted have appeared in
random location (FIRST, between other records, etc...). Ie:
INSERT into tb1 values ('rec3','vl3');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec3 | vl3 <<=
rec2 | vl2
Why it? I can't undestand why the new record location was change. Shouldn't it
apper at the LAST record???
What need I do??
Thank you.
Lucas Vendramin
Brazil
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] New record position
This is a feature of relational databases, you should explicitly specify
ordering if you want persistent order.
btw, why do you bothering ?
Oleg
On Wed, 30 Mar 2005 [EMAIL PROTECTED] wrote:
Hello,
I am using Slackware Linux 10, Postgresql 8.0.1.
My computer had a incorrectly power down (last week) and I have executed the
vacuum command:
VACCUM FULL ANALYZE VERBOSE;
to recicle and verify my database.
Before the power-down, all records had inserted into a table have displayed at
LAST record. Like:
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
INSERT into tb1 values ('rec2','vl2');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec2 | vl2
But After the power-down and vacuum, the new records inserted have appeared in
random location (FIRST, between other records, etc...). Ie:
INSERT into tb1 values ('rec3','vl3');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec3 | vl3 <<=
rec2 | vl2
Why it? I can't undestand why the new record location was change. Shouldn't it
apper at the LAST record???
What need I do??
Thank you.
Lucas Vendramin
Brazil
---(end of broadcast)---
TIP 8: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] New record position
Why it? I can't undestand why the new record location was change. Shouldn't it apper at the LAST record??? What need I do?? Thank you. The SQL spec specifies that if you don't use ORDER BY, well, the records come out in any order they want. Actually it's the order they are on disk, which is more or less random as inserting new records will fill the space left by deleted ones, and vacuum full will compact them. If you want order, use ORDER BY. If you want to order them in the order they were inserted, order by a SERIAL PRIMARY KEY field... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] New record position
Okay, I will use the "order by" clause.
I was worried about it. I have thought that my database had crashed.
Thank you.
Quoting Oleg Bartunov :
This is a feature of relational databases, you should explicitly specify
ordering if you want persistent order.
btw, why do you bothering ?
Oleg
On Wed, 30 Mar 2005 [EMAIL PROTECTED] wrote:
Hello,
INSERT into tb1 values ('rec3','vl3');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec3 | vl3 <<=
rec2 | vl2
Why it? I can't undestand why the new record location was change.
Shouldn't it apper at the LAST record???
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: RE : [SQL] Foreign key
On Wed, Mar 30, 2005 at 02:09:05PM +0200, [EMAIL PROTECTED] wrote: > > Thanks for your answers but i make the modifications, the same error returned. > Here my script : > ... > Create table Salariés (Nom_salarié VARCHAR(20), >Prénom VARCHAR(20), > Fonction VARCHAR(50), > Service VARCHAR(50), > Adresse VARCHAR(100), > Numero_SS integer, > Matricule VARCHAR(6), > rs_ssii VARCHAR(30) references ssii, > PRIMARY KEY ( Nom_salarié, rs_ssii)) ; The salariés table has a primary key with two columns. > Create table Compteur (id integer, >Heures_travaillées decimal(6,2), > Cp_acquis decimal(6,2), > Cp_pris decimal(6,2), > RTT_acquis decimal(6,2), > RTT_pris decimal(6,2), > Nom_salarié VARCHAR(20) references salariés, > rs_ssii VARCHAR(30) references ssii, > PRIMARY KEY ( Nom_salarié,rs_ssii,Id)) ; The foreign key reference to salariés has only one column but the primary key for salariés has two columns, hence the error. It looks like you should be doing this: Nom_salarié VARCHAR(20), rs_ssii VARCHAR(30), FOREIGN KEY (Nom_salarié, rs_ssii) REFERENCES salariés, -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] RE : Foreign key
You should keep the list copied unless you have a specific reason not to. This allows other people to help and learn from the discussion. On Wed, Mar 30, 2005 at 14:09:07 +0200, [EMAIL PROTECTED] wrote: > Hello, > > Thanks for your answers but i make the modifications, the same error returned. > Here my script : > > > Create table ssii ( rs_ssii VARCHAR(30), >Numéro_siret integer , > Adresse VARCHAR(100), > Téléphone VARCHAR(9), > Fax VARCHAR(10), > PRIMARY KEY (Raison sociale)); > > > Create table client ( rs_client VARCHAR(30), > Téléphone VARCHAR(10), > Fax VARCHAR(10), > Contact VARCHAR(30), > PRIMARY KEY (rs_client)); > > > Create table Contrat ( numero_contrat integer, > Date_debut date, > Date_fin date, > rs_ssii VARCHAR(30) references ssii, > rs_client VARCHAR(30) references client, > code_activité VARCHAR(20) references activités, > PRIMARY KEY (numero_contrat, rs_ssii, rs_client, code_activité)) ; > > > > Create table activités (code_activité VARCHAR(20), >Libellé text, > Imputation VARCHAR(6), > Nature VARCHAR(20), > Commentaire text, > Durée decimal(5,3), > PRIMARY KEY ( Code_activité)) ; > > > > Create table Salariés (Nom_salarié VARCHAR(20), >Prénom VARCHAR(20), > Fonction VARCHAR(50), > Service VARCHAR(50), > Adresse VARCHAR(100), > Numero_SS integer, > Matricule VARCHAR(6), > rs_ssii VARCHAR(30) references ssii, > PRIMARY KEY ( Nom_salarié, rs_ssii)) ; > > > Create table Compteur (id integer, >Heures_travaillées decimal(6,2), > Cp_acquis decimal(6,2), > Cp_pris decimal(6,2), > RTT_acquis decimal(6,2), > RTT_pris decimal(6,2), > Nom_salarié VARCHAR(20) references salariés, Unless Nom_salarié is unique in the salariés table (and you add a UNIQUE declaration for it to that table), you won't be able to do this. > PRIMARY KEY ( Nom_salarié, Id)) ; > > NOTICE : create table/primary key will create implicit index "compteur_pkey > for table" compteur. > ERROR : < number of referencing and referenced colums for foreign key > disagree>. > > So i add this ligne for referencing the two primary key of table salariés > > Create table Compteur (id integer, >Heures_travaillées decimal(6,2), > Cp_acquis decimal(6,2), > Cp_pris decimal(6,2), > RTT_acquis decimal(6,2), > RTT_pris decimal(6,2), > Nom_salarié VARCHAR(20) references salariés, > rs_ssii VARCHAR(30) references ssii, > PRIMARY KEY ( Nom_salarié,rs_ssii,Id)) ; > > The same error is returned. This approach will work, but you aren't doing it correctly. Instead of two column references you want to make a foreign key referece such as: FOREIGN KEY (Nom_salarié, rs_ssii) REFERENCES salariés, > > alain SAKALALA > DOR/OCR Support N1 SMS et VOIX > Mailto:[EMAIL PROTECTED] > > > > > > > > > -Message d'origine- > De : Bruno Wolff III [mailto:[EMAIL PROTECTED] > Envoyé : dimanche 27 mars 2005 18:33 > À : SAKALALA, Alain > Cc : pgsql-sql@postgresql.org > Objet : Re: Foreign key > > On Fri, Mar 25, 2005 at 16:31:16 +0100, > [EMAIL PROTECTED] wrote: > > > > When i add table with foreign key in my database, this error return : < > > number of referencing and referenced colums for foreign key disagree>. > > > > How resolve this problem ? > > Besides what Mike said, one other thing to remember is that if you don't > specify columns in the referenced table, the primary key of that table > is used, NOT columns with names matching those of the referencing table. > > In cases like this it have helped if you had copied and pasted an example > displaying the problem in addition to the error message. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
On Wed, Mar 30, 2005 at 12:33:11 +0200, [EMAIL PROTECTED] wrote: > On Wed, Mar 30, 2005 at 11:48:31AM +0200, PFC wrote: > > > > Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ... > > ALTER CONSTRAINT? I did check for that, and it does not appear to > exist?! That's why I asked ... What version of Postgres are you running? I think ALTER CONSTRAINT is a relatively recent addition. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
On Wed, Mar 30, 2005 at 11:52:32AM -0600, Bruno Wolff III wrote: > > What version of Postgres are you running? I think ALTER CONSTRAINT is a > relatively recent addition. Where are you seeing ALTER CONSTRAINT? I don't see it in gram.y even in HEAD. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
On Wed, Mar 30, 2005 at 10:52:42 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Wed, Mar 30, 2005 at 11:52:32AM -0600, Bruno Wolff III wrote: > > > > What version of Postgres are you running? I think ALTER CONSTRAINT is a > > relatively recent addition. > > Where are you seeing ALTER CONSTRAINT? I don't see it in gram.y > even in HEAD. I guess only in the previous messages in the thread. I remembered some recent additions to the ALTER TABLE command and incorrectly assumed that ALTER CONSTRAINT was one of those. It does look like you can only ADD and DROP constraints, not directly alter or replace them. So making a reference deferable is go to require a DROP and ADD which will need to recheck the constraint. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] delphi access question?
One of my applications is in Delphi 5. I just went to change it over to Postgres (from MSSQL). I am using odbc and something a bit odd is happening. I can run a sql statement ok, even in sql builder I see all the fields. But the returned result set appears to be missing some of the fields. So my table is CREATE TABLE tbltranslations ( transnumber int4 NOT NULL, clientnum char(4) NOT NULL, lastran timestamp, lastupdated timestamp, firstrowhasheading char(1), fixed_delimited char(1), tblname varchar(50), delimeter char(1), textqualifier char(1), active bool, direction char(1), client_filename varchar(250), ftp_account int4, fixedlenghthascomma char(1), ftp_path varchar(250), ftp_filename varchar(50), fieldname_forid_on_insert varchar(50) ) but only fields transnumber lastran lastupdated active and ftp_account show up as fields I can add to the result. Any ideas? Joel Fradkin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] save me from an unconstrained join
It actually does what I want... but it offends my database
sensibilities... :-)
Heres the basics of the tables involved:
CREATE TABLE bds_filesize (
bds_filesize_id serial
name text NOT NULL,
byte_limit integer NOT NULL,
slots integer NOT NULL
);
CREATE TABLE software (
software_binary_id serial,
binary_file oid,
filename text,
filesize integer,
checksum text
);
query:
select
software_binary_id, min(byte_limit)
from
bds_filesize, software_binary
where
byte_limit > filesize GROUP BY software_binary_id;
Basically each software is assigned a "class" based on the size of its
binary into a predetermined range of classes that are defined as
relative filesizes. The above query really does work... but istm I ought
to be joining those tables somehow... any ideas?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Postgres 7.3 migrate to 8.0 date problems.
On Mon, 2005-03-28 at 15:48, Scott Marlowe wrote:
> On Mon, 2005-03-28 at 13:44, Thomas Seeber wrote:
> > Hi,
> >
> > We were upgrading from postgres 7.3 -> 8.0 and having a little
> > problems importing dates from some of our data sources. Say we have a
> > date like '2004-17-05'. In postgres 7.3, postgres would intrept this
> > as Year Day Month automatically. In the documentation, from postgres
> > 7.4 on this has to be specified in the Datestyle option and YDM is not
> > an option. Other data we have is coming in on the YMD formate which
> > would be more expected. I realize that this change is better for data
> > integrity, however we have alot of legacy systems where being able to
> > mimic the 7.3 behaviour would be desireable. Any ideas?
>
> Fix the data? I had to write a lot of scripts to keep dates like that
> OUT of my last PostgreSQL installation, which was running 7.2 Which is
> why, as the guy who whinged and moaned until this behavioural change was
> made, I feel for you, but honestly, the lackadaisical manner of handing
> that particular format (-DD-MM) never really seemed right even to
> the people who fought me on the idea of changing the default behaviour
> of DD/MM/ versus MM/DD/.
>
> While the US uses MM/DD/ and Europe uses DD/MM/, and there may
> be some arguments for handling a sloppy version of one of those,
> computer folk (and the government) who want easily ordered dates use
> -MM-DD, I've never seen a good argument made for the usage of
> -DD-MM before.
>
> Are you sure that the other dates in your data set are what you think
> they are? Because if the two numbers are both <=12, then you'll get one
> "date" and if the wrong one is >12 you'll get another. That can't be
> good.
>
Would it be possible to use a BEFORE trigger to reformat the -DD-MM
date to -MM-DD ? The error I see on 7.4 is ERROR: date/time field
value out of range: "2005-14-01" so ISTM you could do some data
manipulation if you wanted.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] delphi access question?
Something is not translating CHAR values correctly -- all the fields that you "see" are not char values. Have no idea off hand *why* this would be ... character encoding differences maybe ? Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Joel Fradkin [mailto:[EMAIL PROTECTED] Sent: Wed 3/30/2005 12:15 PM To: pgsql-sql@postgresql.org Cc: [EMAIL PROTECTED] Subject:[SQL] delphi access question? One of my applications is in Delphi 5. I just went to change it over to Postgres (from MSSQL). I am using odbc and something a bit odd is happening. I can run a sql statement ok, even in sql builder I see all the fields. But the returned result set appears to be missing some of the fields. So my table is CREATE TABLE tbltranslations ( transnumber int4 NOT NULL, clientnum char(4) NOT NULL, lastran timestamp, lastupdated timestamp, firstrowhasheading char(1), fixed_delimited char(1), tblname varchar(50), delimeter char(1), textqualifier char(1), active bool, direction char(1), client_filename varchar(250), ftp_account int4, fixedlenghthascomma char(1), ftp_path varchar(250), ftp_filename varchar(50), fieldname_forid_on_insert varchar(50) ) but only fields transnumber lastran lastupdated active and ftp_account show up as fields I can add to the result. Any ideas? Joel Fradkin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org !DSPAM:424b0a12126562811677690! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] delphi access question?
Yea odd thing is if I use a table type object it see the fields so it is only with query objects. The same odbc works ok with my asp pages. I will see if I can figure out the zeos stuff. Something is not translating CHAR values correctly -- all the fields that you "see" are not char values. Have no idea off hand *why* this would be ... character encoding differences maybe ? Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Joel Fradkin [mailto:[EMAIL PROTECTED] Sent: Wed 3/30/2005 12:15 PM To: pgsql-sql@postgresql.org Cc: [EMAIL PROTECTED] Subject:[SQL] delphi access question? One of my applications is in Delphi 5. I just went to change it over to Postgres (from MSSQL). I am using odbc and something a bit odd is happening. I can run a sql statement ok, even in sql builder I see all the fields. But the returned result set appears to be missing some of the fields. So my table is CREATE TABLE tbltranslations ( transnumber int4 NOT NULL, clientnum char(4) NOT NULL, lastran timestamp, lastupdated timestamp, firstrowhasheading char(1), fixed_delimited char(1), tblname varchar(50), delimeter char(1), textqualifier char(1), active bool, direction char(1), client_filename varchar(250), ftp_account int4, fixedlenghthascomma char(1), ftp_path varchar(250), ftp_filename varchar(50), fieldname_forid_on_insert varchar(50) ) but only fields transnumber lastran lastupdated active and ftp_account show up as fields I can add to the result. Any ideas? Joel Fradkin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org !DSPAM:424b0a12126562811677690! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] A SQL Question About distinct, limit, group by, having, aggregate
i have a little question, how to get 3 higher score student in every class. Data looks like as below problem: id class score johna 100 jenny a 70 ken a 59 maryb 85 jacky b 80 lilyb 70 kevin b 50 david b 30 tinac 85 tonyc 80 barec 70 vivian c 60 ericc 57 andyc 50 result: id class score johna 100 jenny a 70 ken a 59 maryb 85 jacky b 80 lilyb 70 tinac 85 tonyc 80 barec 70 -- http://alumni.cyut.edu.tw Open WebMail Project (http://openwebmail.org) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] A SQL Question About distinct, limit, group by, having, aggregate
On Thu, 31 Mar 2005 10:29:16 +0800, "æï[é" <[EMAIL PROTECTED]> wrote: > i have a little question, how to get 3 higher score student in every class. > Data looks like as below > > problem: > idclass score > john a 100 > jenny a 70 > ken a 59 > mary b 85 > jacky b 80 > lily b 70 > kevin b 50 > david b 30 > tina c 85 > tony c 80 > bare c 70 > vivianc 60 > eric c 57 > andy c 50 > > result: > idclass score > john a 100 > jenny a 70 > ken a 59 > mary b 85 > jacky b 80 > lily b 70 > tina c 85 > tony c 80 > bare c 70 > Actually, i don't see any difference between problem and results but maybe select * from yourTable order by class, score desc regards, Jaime Casanova ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres 7.3 migrate to 8.0 date problems.
On Wed, Mar 30, 2005 at 04:23:34PM -0500, Robert Treat wrote:
>
> Would it be possible to use a BEFORE trigger to reformat the -DD-MM
> date to -MM-DD ? The error I see on 7.4 is ERROR: date/time field
> value out of range: "2005-14-01" so ISTM you could do some data
> manipulation if you wanted.
I don't think that would work if the target column has type DATE,
presumably because NEW has the same type as a row of the table, so
NEW.datefield would be a DATE and the -DD-MM value would raise
an exception before the trigger was ever called.
CREATE TABLE foo (
id serial PRIMARY KEY,
datefield date NOT NULL
);
CREATE FUNCTION datefix() RETURNS trigger AS $$
BEGIN
RAISE INFO 'datefix';
NEW.datefield := current_date; -- for testing
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE datefix();
INSERT INTO foo (datefield) VALUES ('2005-03-30'); -- valid
INFO: datefix
INSERT 0 1
INSERT INTO foo (datefield) VALUES ('2005-30-03'); -- not valid
ERROR: date/time field value out of range: "2005-30-03"
HINT: Perhaps you need a different "datestyle" setting.
Notice that the trigger wasn't called for the second INSERT.
Just brainstorming now, but if you want to keep datefield as a DATE,
then maybe you could create a view with datefield cast to TEXT and
create an appropriate rule so you can insert into the view and have
-DD-MM converted to -MM-DD:
DROP TABLE foo;
DROP FUNCTION datefix();
CREATE TABLE foo (
id serial PRIMARY KEY,
datefield date NOT NULL
);
CREATE VIEW fooview AS SELECT id, datefield::text FROM foo;
CREATE RULE datefix AS ON INSERT TO fooview
DO INSTEAD
INSERT INTO foo (datefield) VALUES (to_date(NEW.datefield, '-DD-MM'));
INSERT INTO fooview (datefield) VALUES ('2005-30-03');
INSERT 0 1
SELECT * FROM foo;
id | datefield
+
1 | 2005-03-30
(1 row)
I admittedly haven't thought this through very far so it could have
problems, but it might be a starting point. On the other hand, I'm
inclined to agree with Scott Marlowe's advice: fix the data.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] delphi access question
hello I can run your sql statement in PgAdmin,too. so your statement should be OK! Can you post more example about add to result, let us have idea about your question. I think you can try to set char(1) to char(2), try it will useful.. -- http://alumni.cyut.edu.tw Open WebMail Project (http://openwebmail.org) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] A SQL Question About distinct, limit, group by, having, aggregate
below is the sql schema. i hope it will help.
i want the top 3 score students in every class
below is the original sql solution, but when we have 100 class , we have to
union 100 times?
have any better performance statement?
select * from (
(select * from allscore where class = 'a' order by score desc limit 3)
union
(select * from allscore where class = 'b' order by score desc limit 3)
union
(select * from allscore where class = 'c' order by score desc limit 3)
) as t1 order by class,score desc
CREATE TABLE allscore (
id character varying(20) NOT NULL,
"class" character(1) NOT NULL,
score integer
);
insert into allscore(id, class, score) values ('john','a','100');
insert into allscore(id, class, score) values ('jenny','a','70');
insert into allscore(id, class, score) values ('ken','a','59');
insert into allscore(id, class, score) values ('mary','b','85');
insert into allscore(id, class, score) values ('jacky','b','80');
insert into allscore(id, class, score) values ('lily','b','70');
insert into allscore(id, class, score) values ('kevin','b','50');
insert into allscore(id, class, score) values ('david','b','30');
insert into allscore(id, class, score) values ('tina','c','85');
insert into allscore(id, class, score) values ('tony','c','80');
insert into allscore(id, class, score) values ('bare','c','70');
insert into allscore(id, class, score) values ('vivian','c','60');
insert into allscore(id, class, score) values ('eric','c','57');
insert into allscore(id, class, score) values ('andy','c','50');
--
http://alumni.cyut.edu.tw
Open WebMail Project (http://openwebmail.org)
---(end of broadcast)---
TIP 3: 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] save me from an unconstrained join
Robert Treat wrote: It actually does what I want... but it offends my database sensibilities... :-) Heres the basics of the tables involved: CREATE TABLE bds_filesize ( bds_filesize_id serial name text NOT NULL, byte_limit integer NOT NULL, slots integer NOT NULL ); CREATE TABLE software ( software_binary_id serial, binary_file oid, filename text, filesize integer, checksum text ); query: select software_binary_id, min(byte_limit) from bds_filesize, software_binary where byte_limit > filesize GROUP BY software_binary_id; Basically each software is assigned a "class" based on the size of its binary into a predetermined range of classes that are defined as relative filesizes. The above query really does work... but istm I ought to be joining those tables somehow... any ideas? But you are joining them - via bds_filesize.byte_limit and software.fileszie. Now, it's not an equality test, but there's nothing wrong with that. You could probably do something clever with subqueries rather than using min() but it would only complicate the query afaics. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> It does look like you can only ADD and DROP constraints, not directly
> alter or replace them. So making a reference deferable is go to require
> a DROP and ADD which will need to recheck the constraint.
I asked the same question a few days ago on pgsql-general.
In short, if you want to skip the rechecking you have to update system tables
directly and you have to do two of them.
The updates you want would look something like these. But these would do *all*
your constraints, make sure to get only the ones you really want to change:
update pg_constraint set condeferrable = 't' where contype = 'f'
update pg_trigger set tgdeferrable=true where tgisconstraint = true
I think an ALTER CONSTRAINT to change these settings as well as the
ON {UPDATE,DELETE} behaviour would be neat.
--
greg
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] New record position
[EMAIL PROTECTED] writes: > Why it? I can't undestand why the new record location was change. Shouldn't it > apper at the LAST record??? > What need I do?? SQL only imposes an order on the return set if you add an "ORDER BY" clause. You can't expect any particular order to either recur or NOT recur unless you have specifically requested a particular ordering. There's no bug; just use ORDER BY if you need to, and, if you don't, make sure you don't expect any particular ordering... -- (format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com") http://www3.sympatico.ca/cbbrowne/spiritual.html "The present need for security products far exceeds the number of individualscapable ofdesigning secure systems. Consequently, industry has resorted to employing folks and purchasing "solutions" from vendors that shouldn't be let near a project involving securing a system." -- Lucky Green ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
