Re: [SQL] getting details about integrity constraint violation
| |You can't, at the moment, except by parsing the text message. | |The "error fields" facility in the FE/BE protocol could be extended |in that direction, and I think there's already been some discussion |about it; but no one has stepped up with a concrete proposal, much |less volunteered to do the work ... | | regards, tom lane So there must be at least a bunnch of error codes (which could be printed in addition)? Or has noone defined such, yet? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] getting details about integrity constraint violation
В Птн, 03/06/2005 в 10:00 +0200, KÖPFERL Robert пишет: > | > |You can't, at the moment, except by parsing the text message. > | > |The "error fields" facility in the FE/BE protocol could be extended > |in that direction, and I think there's already been some discussion > |about it; but no one has stepped up with a concrete proposal, much > |less volunteered to do the work ... > | > | regards, tom lane > > > So there must be at least a bunnch of error codes (which could be printed in > addition)? There are, but they only say something along the lines of "unique constraint violated", they don't say which one. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(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
[SQL] 'true'::TEXT::BOOLEAN
Hi, What's the type I need to convert text to before I can convert it to boolean? Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(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] 'true'::TEXT::BOOLEAN
O Markus Bertheau β^Ψ έγραψε στις Jun 3, 2005 : > Hi, > > What's the type I need to convert text to before I can convert it to > boolean? just 't' will suffice. > > Markus > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'true'::TEXT::BOOLEAN
В Птн, 03/06/2005 в 14:20 +0300, Achilleus Mantzios пишет:
> O Markus Bertheau β^Ψ έγραψε στις Jun 3, 2005 :
>
> > Hi,
> >
> > What's the type I need to convert text to before I can convert it to
> > boolean?
>
> just 't' will suffice.
Well, that's not my question. I have a plpgsql function like that:
CREATE FUNCTION object_new(class TEXT, properties TEXT[])
which I call like
object_new('Car', ARRAY['color', 'red', 'new', 'true'])
That means set color to red and new to true. In the function I need to
call
object_set_boolean_property(object_id INT, property_name TEXT,
property_value BOOLEAN)
And I can't call it with a TEXT variable, because casting from TEXT to
BOOLEAN isn't possible.
Markus
--
Markus Bertheau ☭ <[EMAIL PROTECTED]>
---(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] 'true'::TEXT::BOOLEAN
O Markus Bertheau β^Ψ έγραψε στις Jun 3, 2005 :
> Π? Π?Ρ?Π½, 03/06/2005 Π² 14:20 +0300, Achilleus Mantzios ΠΏΠΈΡ?Π΅Ρ?:
> > O Markus Bertheau Ξ²^Ψ ΞΞ³Ο?Ξ±Ο?Ξ΅ Ο?Ο?ΞΉΟ? Jun 3, 2005 :
> >
> > > Hi,
> > >
> > > What's the type I need to convert text to before I can convert it to
> > > boolean?
> >
> > just 't' will suffice.
>
> Well, that's not my question. I have a plpgsql function like that:
>
> CREATE FUNCTION object_new(class TEXT, properties TEXT[])
>
> which I call like
>
> object_new('Car', ARRAY['color', 'red', 'new', 'true'])
>
> That means set color to red and new to true. In the function I need to
> call
>
> object_set_boolean_property(object_id INT, property_name TEXT,
> property_value BOOLEAN)
>
> And I can't call it with a TEXT variable, because casting from TEXT to
> BOOLEAN isn't possible.
Then use the
case when ... then ... when ... then ... else ... end
construct, e.g.
case when mytext='true' then 't'::boolean else 'f'::boolean end
Hmm, why dont you leave it as 'true' or 'false' without any castings.
>
> Markus
>
--
-Achilleus
---(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] 'true'::TEXT::BOOLEAN
В Птн, 03/06/2005 в 14:45 +0300, Achilleus Mantzios пишет: > Then use the > case when ... then ... when ... then ... else ... end > construct, e.g. > case when mytext='true' then 't'::boolean else 'f'::boolean end Because I don't want to reimplement postgres' boolean parsing. > Hmm, why dont you leave it as 'true' or 'false' without any castings. Because then pg doesn't find the function because it looks for one with a text argument. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(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] 'true'::TEXT::BOOLEAN
On Jun 3, 2005, at 8:52 PM, Markus Bertheau ☭ wrote:
And I can't call it with a TEXT variable, because casting from TEXT to
BOOLEAN isn't possible.
I'd be surprised if there weren't a some way to coerce the cast from
text to boolean, but you might want to just make a simple convenience
function in the interim:
test=# create or replace function text2bool (text)
returns boolean language sql as $$
select case
when lower($1) = 'true'
then true
else false
end;
$$;
CREATE FUNCTION
test=# select text2bool('true');
text2bool
---
t
(1 row)
test=# select text2bool('false');
text2bool
---
f
(1 row)
Just an idea.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'true'::TEXT::BOOLEAN
On Jun 3, 2005, at 9:23 PM, Markus Bertheau ☭ wrote: This also bypasses the built in postgresql boolean literal parsing. I think casting from text to boolean should be possible, and use the same algorithm that's used when casting from "unknown" to boolean. Actually, looking at the system tables, I don't think it is. There don't appear to be any casts to (or from) boolean. I may be looking at it wrong, but that's how it appears to me. Corrections, anyone? select type_source.typname as source, type_target.typname as target from pg_cast join pg_type type_source on (castsource = type_source.oid) join pg_type type_target on (casttarget = type_target.oid) where type_target.typname = 'bool' or type_source.typname = 'bool' order by type_source.typname; source | target + (0 rows) This is in v8.0.3 Michael Glaesemann grzm myrealbox com PS. Please don't top post. ---(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] 'true'::TEXT::BOOLEAN
This also bypasses the built in postgresql boolean literal parsing.
I think casting from text to boolean should be possible, and use the
same algorithm that's used when casting from "unknown" to boolean.
Markus
В Птн, 03/06/2005 в 21:14 +0900, Michael Glaesemann пишет:
> On Jun 3, 2005, at 8:52 PM, Markus Bertheau ☭ wrote:
>
> > And I can't call it with a TEXT variable, because casting from TEXT to
> > BOOLEAN isn't possible.
>
>
> I'd be surprised if there weren't a some way to coerce the cast from
> text to boolean, but you might want to just make a simple convenience
> function in the interim:
>
> test=# create or replace function text2bool (text)
> returns boolean language sql as $$
> select case
> when lower($1) = 'true'
> then true
> else false
> end;
> $$;
> CREATE FUNCTION
>
> test=# select text2bool('true');
> text2bool
> ---
> t
> (1 row)
>
> test=# select text2bool('false');
> text2bool
> ---
> f
> (1 row)
>
>
> Just an idea.
>
> Michael Glaesemann
> grzm myrealbox com
--
Markus Bertheau ☭ <[EMAIL PROTECTED]>
---(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] index row size 2728 exceeds btree maximum, 27
Bruno Wolff III wrote: On Thu, Jun 02, 2005 at 18:00:17 +0100, Richard Huxton wrote: Certainly, but if the text in the logfile row is the same, then hashing isn't going to make a blind bit of difference. That's the root of my concern, and something only Dinesh knows. Sure it is. Because the hash can be used in the primary key instead of of the error message which should reduce the size of the key enough that he can use a btree index. Sorry - obviously not being clear. Since he's using the index via a primary key he'll need the columns that key is over to be unique. If the columns fail that test in the real world, hashing will replace the index-size error with an "unable to insert duplicates" error. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] 'true'::TEXT::BOOLEAN
O Michael Glaesemann έγραψε στις Jun 3, 2005 : > > On Jun 3, 2005, at 9:23 PM, Markus Bertheau β? wrote: > > > This also bypasses the built in postgresql boolean literal parsing. > > > > I think casting from text to boolean should be possible, and use the > > same algorithm that's used when casting from "unknown" to boolean. > > Actually, looking at the system tables, I don't think it is. There > don't appear to be any casts to (or from) boolean. I may be looking > at it wrong, but that's how it appears to me. Corrections, anyone? > > select type_source.typname as source, type_target.typname as target > from pg_cast > join pg_type type_source on (castsource = type_source.oid) > join pg_type type_target on (casttarget = type_target.oid) > where type_target.typname = 'bool' > or type_source.typname = 'bool' > order by type_source.typname; > > source | target > + > (0 rows) > > This is in v8.0.3 Also according to the docs: http://www.postgresql.org/docs/current/static/datatype-boolean.html "Tip: Values of the boolean type cannot be cast directly to other types (e.g., CAST (boolval AS integer) does not work). This can be accomplished using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE 'value if false' END." I suppose the reverse must be true also. > > Michael Glaesemann > grzm myrealbox com > > PS. Please don't top post. > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] 'true'::TEXT::BOOLEAN
В Птн, 03/06/2005 в 15:46 +0300, Achilleus Mantzios пишет: > Also according to the docs: > http://www.postgresql.org/docs/current/static/datatype-boolean.html > > "Tip: Values of the boolean type cannot be cast directly to other types > (e.g., CAST (boolval AS integer) does not work). This can be accomplished > using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE > 'value if false' END." Ah, that works. Thanks very much. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] CASE WHEN foo IS NULL THEN DEFAULT ELSE foo END
Hi, is it planned to support the following insert syntax? INSERT INTO table VALUES (CASE WHEN arg_whatever IS NULL THEN DEFAULT ELSE arg_whatever END); I have the DEFAULT inside the CASE expression in mind. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'true'::TEXT::BOOLEAN doesn't work
В Птн, 03/06/2005 в 15:07 +0200, Markus Bertheau ☭ пишет: > В Птн, 03/06/2005 в 15:46 +0300, Achilleus Mantzios пишет: > > > Also according to the docs: > > http://www.postgresql.org/docs/current/static/datatype-boolean.html > > > > "Tip: Values of the boolean type cannot be cast directly to other types > > (e.g., CAST (boolval AS integer) does not work). This can be accomplished > > using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE > > 'value if false' END." > > Ah, that works. Thanks very much. Correcting myself, that doesn't work. It says something along the lines of case expression must be of type boolean. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] 'true'::TEXT::BOOLEAN
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes: > Well, that's not my question. I have a plpgsql function like that: > ... > And I can't call it with a TEXT variable, because casting from TEXT to > BOOLEAN isn't possible. In plpgsql it is: just assign the text value to a boolean variable. plpgsql's notions of type safety are pretty lax ;-) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] CASE WHEN foo IS NULL THEN DEFAULT ELSE foo END
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes: > is it planned to support the following insert syntax? > INSERT INTO table VALUES (CASE WHEN arg_whatever IS NULL THEN DEFAULT > ELSE arg_whatever END); No. AFAICS, SQL99 only defines DEFAULT as the direct INSERT or UPDATE target expression. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] using a selected row as a function parameter
Hi all, I've been struggling with this for a while and haven't found anything on the 'Net about it. I've created a function that takes two table rows as a parameters. I'd like to use the output of a select (two single rows) as the parameters, but I can't get it to work. What am I missing? The function prototype looks like this: CREATE OR REPLACE FUNCTION queue.apply_routing_rule( queue.messages, queue.routing_rules ) RETURNS int2 and I would like to call it like this: SELECT queue.apply_routing_rule( (SELECT * from queue.messages WHERE id = 1), (SELECT * from queue.routing_rules WHERE id = 1) ); I get an error message along the lines of "sub-query must return a single value". I've tried different combinations of "CAST" and "ROW" functions, but they give syntax errors. Any suggestions? Regards, Ami. ---(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] 'true'::TEXT::BOOLEAN
On Fri, Jun 03, 2005 at 14:23:37 +0200,
Markus Bertheau ??? <[EMAIL PROTECTED]> wrote:
> This also bypasses the built in postgresql boolean literal parsing.
>
> I think casting from text to boolean should be possible, and use the
> same algorithm that's used when casting from "unknown" to boolean.
You probably want boolin.
area=> select boolin('0'), boolin('f'), boolin('false');
boolin | boolin | boolin
++
f | f | f
(1 row)
Note that the function will error out if the argument is garbage.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] using a selected row as a function parameter
Ami Ganguli <[EMAIL PROTECTED]> writes: > SELECT queue.apply_routing_rule( > (SELECT * from queue.messages WHERE id = 1), > (SELECT * from queue.routing_rules WHERE id = 1) > ); Not sure if that particular syntax should be expected to work, but why not SELECT queue.apply_routing_rule(messages.*, routing_rules.*) FROM queue.messages, queue.routing_rules WHERE messages.id = 1 AND routing_rules.id = 1; The query as you want to write it will certainly fail anyway if there's more than one row with id = 1 in either table, so it's not like there's some huge inefficiency in doing it as a join. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'true'::TEXT::BOOLEAN
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Markus Bertheau ??? <[EMAIL PROTECTED]> wrote:
>> I think casting from text to boolean should be possible, and use the
>> same algorithm that's used when casting from "unknown" to boolean.
> You probably want boolin.
That won't actually work either, because boolin wants cstring:
egression=# select boolin('f'::text);
ERROR: function boolin(text) does not exist
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
You can get it to work like this, if you're determined:
regression=# select boolin(textout('f'::text));
boolin
f
(1 row)
and of course
regression=# select textin(boolout(true));
textin
t
(1 row)
There's been discussion of allowing all datatypes to be explicitly
casted to or from text by generating conversions like these
automatically. But I'm not sure if everyone's convinced it's a good
idea or not. You'd also have to argue about whether varchar should
be included in the special dispensation ...
regards, tom lane
---(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] using a selected row as a function parameter
On Fri, Jun 03, 2005 at 05:44:59PM +0300, Ami Ganguli wrote: > > SELECT queue.apply_routing_rule( > (SELECT * from queue.messages WHERE id = 1), > (SELECT * from queue.routing_rules WHERE id = 1) > ); > > I get an error message along the lines of "sub-query must return a > single value". The error I get is "subquery must return only one column". Queries like the following should work in 8.0.x: SELECT queue.apply_routing_rule(m, r) FROM (SELECT * FROM queue.messages WHERE id = 1) AS m, (SELECT * FROM queue.routing_rules WHERE id = 1) AS r; SELECT queue.apply_routing_rule(m, r) FROM queue.messages AS m, queue.routing_rules AS r WHERE m.id = 1 AND r.id = 1; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 'true'::TEXT::BOOLEAN
В Птн, 03/06/2005 в 11:28 -0400, Tom Lane пишет: > There's been discussion of allowing all datatypes to be explicitly > casted to or from text by generating conversions like these > automatically. But I'm not sure if everyone's convinced it's a good > idea or not. I certainly consider the way you proposed in the other mail a workaround. What are the counter arguments? Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(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] 'true'::TEXT::BOOLEAN
On Fri, Jun 03, 2005 at 11:28:02 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > That won't actually work either, because boolin wants cstring: Thanks for pointing that out. I was actually surprised to see my test work, since I knew boolin expected cstring. I forgot that by not providing a type strings get treated specially. > There's been discussion of allowing all datatypes to be explicitly > casted to or from text by generating conversions like these > automatically. But I'm not sure if everyone's convinced it's a good > idea or not. You'd also have to argue about whether varchar should > be included in the special dispensation ... I don't remember ever running accross documentation on how to solve this problem. Even something noting that most of the type input and output functions are named typein and typeout and that to convert to text you can use textin(typeout(typevalue)) and to convert from text you can use typein(textout(text)) would probably be good enough. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] getting details about integrity constraint violation
The "error fields" facility in the FE/BE protocol could be extended in that direction, and I think there's already been some discussion about it; but no one has stepped up with a concrete proposal, much less volunteered to do the work ... Um, if changing the protocol is a bother, you could also add parseable infos to the error messages... instead of : "ERROR: duplicate key violates unique constraint "testinteg_one_key"" it would say "ERROR: duplicate key violates unique constraint "testinteg_one_key" [code:"" error:"integrity" type:"unique" column:"something" constraint:"testinteg_one_key"]" Which could be hackfully added by a "parseable" locale (but with a more restrained form...) SET lc_messages TO parseable regards, tom lane ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
