[SQL] empty string casting to typed value
Hello It is clear that '' is a bad integer or timestamp representation but during the user input NULLs are usually represented with empty strings sometimes bunch of 'if empty' instructions grows huge (and in case of casting to timestamp apostrophes make sense) Why you prohibit casting ''::int to NULL ? What would you advice ? thnx ---(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] Formatting problems with negative intervals, TO_CHAR
On Sun, Jun 06, 2004 at 06:40:56PM -0400, Tom Lane wrote:
> Jeff Boes <[EMAIL PROTECTED]> writes:
> > This seems ... well, counter-intuitive at least:
> > (using Pg 7.4.1)
>
> > # select to_char('4 minutes'::interval -
> > '5 minutes 30 seconds'::interval, 'mi:ss');
>
> > to_char
> > -
> > -1:-3
> > (1 row)
>
> > Why is the trailing zero lost? Why are there two minus signs?
>
> > I would expect '-1:30'.
>
> Yeah, me too. The underlying interval value seems right:
>
> regression=# select '4 minutes'::interval - '5 minutes 30 seconds'::interval;
> ?column?
> ---
> -00:01:30
> (1 row)
>
> so I think this is a to_char() bug. Possibly it's platform-dependent
> --- the roundoff behavior for division with a negative input varies
> across machines. However I do see the bug on HPUX 10.20 with CVS tip.
Please, read PostgreSQL docs.
http://www.postgresql.org/docs/7.4/static/functions-formatting.html
Warning: to_char(interval, text) is deprecated and should not be
used in newly-written code. It will be removed in the next version.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Formatting problems with negative intervals, TO_CHAR
On Monday, 07 June 2004 09:52, Karel Zak wrote:
> http://www.postgresql.org/docs/7.4/static/functions-formatting.html
>
> Warning: to_char(interval, text) is deprecated and should not be
> used in newly-written code. It will be removed in the next version.
This is news for me. Are there any suggestions what we should replace
TO_CHAR with? For example, we were using TO_CHAR to print timestamp
values in ISO format without milliseconds ("-MM-DD HH24:MI:SS" style),
regardless of the current datestyle setting.
I see only three solutions to do this without using TO_CHAR, and IMO none
of them are very attractive:
- do the formatting at the application level,
- change the datestyle for this query only
- an ugly construct using 6 EXTRACT functions
I hope there is a more elegant way to solve this...
Why was TO_CHAR deprecated anyway? It seemed to me like a very useful and
flexible way to do date/time formatting.
Tnx,
stefan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Formatting problems with negative intervals, TO_CHAR
On Mon, Jun 07, 2004 at 11:08:37AM +0200, Stefan Weiss wrote:
> On Monday, 07 June 2004 09:52, Karel Zak wrote:
> > http://www.postgresql.org/docs/7.4/static/functions-formatting.html
> >
> > Warning: to_char(interval, text) is deprecated and should not be
> > used in newly-written code. It will be removed in the next version.
>
> This is news for me. Are there any suggestions what we should replace
> TO_CHAR with? For example, we were using TO_CHAR to print timestamp
> values in ISO format without milliseconds ("-MM-DD HH24:MI:SS" style),
> regardless of the current datestyle setting.
Ah.. ONLY the INTERVAL version of TO_CHAR() is deprecated! All others
versions for numbers, timestamp or date are supported now and in future
versions too.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(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] empty string casting to typed value
On Mon, Jun 07, 2004 at 10:37:44 +0400, sad <[EMAIL PROTECTED]> wrote: > Hello > > It is clear that '' is a bad integer or timestamp representation > > but during the user input NULLs are usually represented with empty strings > sometimes bunch of 'if empty' instructions grows huge > (and in case of casting to timestamp apostrophes make sense) An empty string is not an obvious particular time. Different applications might treat this differently. Not applicable, missing, and current time are all reasonable defaults. > > Why you prohibit casting ''::int to NULL ? > What would you advice ? The application should handle mapping appropiate inputs to NULL or perhaps DEFAULT. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] empty string casting to typed value
On Mon, 7 Jun 2004, sad wrote:
> It is clear that '' is a bad integer or timestamp representation
>
> but during the user input NULLs are usually represented with empty strings
> sometimes bunch of 'if empty' instructions grows huge
> (and in case of casting to timestamp apostrophes make sense)
> Why you prohibit casting ''::int to NULL ?
In part, this is for spec complience. The spec pretty explicitly says that
casting something that does not look like a signed numeric literal to a
numeric type results in an error and empty string does not look like a
signed numeric literal.
In part, it is for the fact that doing such conversions opens the doors to
alot of odd behavior depending on how it is defined. For example, if the
cast that effectively happens on insert is allowed to do that conversion,
a phrase like VALUES ('', '', '') may insert 0-3 nulls into a table
depending on the datatypes since ''=>NULL shouldn't happen for character
types. If it's allowed to happen on implicit casts in expressions,
a phrase like WHERE col != '' may silently be written into something
meaningless (col != NULL) for some types. Even when limited to explicit
casts only, it blurs the line with non-NULL values and will make people
confused when it doesn't occur for character types.
Finally, you should be able to write functions that take a text argument
and return an integer, datetype, whatever that return NULL for empty
string and the value cast to integer for others so there's probably not
much reason to break the cast semantics.
---(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] pl/pgsql and transaction locks
Hello, I have an instance where I have a series of pl/pgsql calls, that report stat results to a common table. When other queries try to hit the stat table (with DML commands; SELECT, INSERT, UPDATE, DELETE etc.) they are forced to wait in a queue until the pl/pgsql has finished executing. will: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; before these DML queries eliminate the locking? -- marcus whitney chief architect : cold feet creative www.coldfeetcreative.com 800.595.4401 cold feet presents emma email marketing for discriminating organizations everywhere visit www.myemma.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Getting FK relationships from information_schema
I'm trying to get my application to deduce foreign key relationships automatically so it can perform appropriate joins for the user. I'm new to information_schema and having problems getting what I want. Here is a test script to be run on a database called "test." -- Demonstrate issues with information_schema create table empl ( empl_pk int4primary key, namevarchar ); create table empl_addr ( empl_k int4references empl, typevarchar, address varchar, primary key (empl_k, type) ); create table doc ( doc_pk int4primary key, namevarchar ); create table empl_doc ( empl_k int4, doc_k int4, primary key (empl_k,doc_k), foreign key (empl_k) references empl, foreign key (doc_k) references doc ); create table doc_empl ( doc_k int4, empl_k int4, primary key (doc_k,empl_k), foreign key (doc_k) references doc, foreign key (empl_k) references empl ); -- Show info about the tables' primary keys select tc.table_name, tc.constraint_type, cu.column_name, cu.ordinal_position from information_schema.key_column_usage cu, information_schema.table_constraints tc where cu.constraint_name = tc.constraint_name and cu.table_name = tc.table_name and tc.constraint_type = 'PRIMARY KEY' and tc.table_catalog = 'test' and tc.table_schema = 'public' order by 2 ; -- Show info about the tables' foreign keys select tc.table_name, tc.constraint_type, cu.column_name, cu.ordinal_position from information_schema.key_column_usage cu, information_schema.table_constraints tc where cu.constraint_name = tc.constraint_name and cu.table_name = tc.table_name and tc.constraint_type = 'FOREIGN KEY' and tc.table_catalog = 'test' and tc.table_schema = 'public' order by 2 ; -- Show constraints for our table select constraint_name, table_name, constraint_type, is_deferrable, initially_deferred from information_schema.table_constraints order by 2,3,1 ; -- Show foreign key constraints select --* constraint_name, unique_constraint_name, match_option,update_rule,delete_rule from information_schema.referential_constraints order by 2,1 ; This produces the following output: CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE table_name | constraint_type | column_name | ordinal_position +-+-+-- empl | PRIMARY KEY | empl_pk |1 empl_addr | PRIMARY KEY | empl_k |1 empl_addr | PRIMARY KEY | type|2 doc| PRIMARY KEY | doc_pk |1 empl_doc | PRIMARY KEY | empl_k |1 empl_doc | PRIMARY KEY | doc_k |2 doc_empl | PRIMARY KEY | doc_k |1 doc_empl | PRIMARY KEY | empl_k |2 (8 rows) table_name | constraint_type | column_name | ordinal_position +-+-+-- empl_addr | FOREIGN KEY | empl_k |1 empl_doc | FOREIGN KEY | empl_k |1 empl_doc | FOREIGN KEY | doc_k |1 doc_empl | FOREIGN KEY | doc_k |1 doc_empl | FOREIGN KEY | empl_k |1 (5 rows) constraint_name | table_name | constraint_type | is_deferrable | initially_deferred -++-+---+ doc_pkey| doc| PRIMARY KEY | NO| NO $1 | doc_empl | FOREIGN KEY | NO| NO $2 | doc_empl | FOREIGN KEY | NO| NO doc_empl_pkey | doc_empl | PRIMARY KEY | NO| NO empl_pkey | empl | PRIMARY KEY | NO| NO $1 | empl_addr | FOREIGN KEY | NO| NO empl_addr_pkey | empl_addr | PRIMARY KEY | NO| NO $1 | empl_doc | FOREIGN KEY | NO| NO $2 | empl_doc | FOREIGN KEY | NO| NO empl_doc_pkey | empl_doc | PRIMARY KEY | NO| NO (10 rows) constraint_name | unique_constraint_name | match_option | update_rule | delete_rule -++--+-+- $1 | doc_pkey | NONE | NO ACTION | NO ACTION $2 | doc_pkey | NONE | NO ACTION | NO ACTION $1 | empl_pkey | NONE | NO ACTION | NO ACTION $1 | empl_pkey
[SQL] Last day of month
Dear friends, Postgres 7.3.4 How to find the last sunday/mon/sat of any given month. Thanks Kumar
[SQL] Cursor returned from procedure ignores setFetchSize() on CallableStatement
Hello all -
I need to return large result sets and therefore need a cursor with a small
fetch size (to avoid caching the entire query at once). However, it appears
that when a cursor is returned from a callable statement setFetchSize is
ignored.
I set up a query that crashes with outofMemoryError if entire query is
cached. I am able to sucsessfully use a Statement and setFetchSize() to
avoid the crash. However, when I put the code in a procedure, the
outofMEmoryError throws before I even try to access the result set,
indicating that the entire query is caching. This is true with or without
calling setFetchSize on the ResultSet in addition to the CallableStatement.
Is this a bug or am I doing something wrong? Any comments greatly
appreciated. Thanks
Client
Code
---
cn.setAutoCommit(false);
CallableStatement proc = cn.prepareCall("{ ? = call reffunc2('cursor1') }");
proc.setFetchSize(100);
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
rds = (ResultSet) proc.getObject(1);
rds.setFetchSize(100);
while (rds.next()){
...
}
Function
-
CREATE OR REPLACE FUNCTION public.reffunc2(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT ..;
RETURN $1;
END;
' LANGUAGE 'plpgsql' VOLATILE;
---(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] Scalar in a range (but textual not numeric)
Richard Huxton <[EMAIL PROTECTED]> writes: > Large table representing non-overlapping blocks: > blocks(id int4, min varchar, max varchar) > SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max; > The estimator gets the wrong plan because it doesn't realise there's > (at most) only one block that can match. Even if it did realize that, it couldn't do much, because this query isn't indexable as it stands. I wonder whether you could adapt the "line segment" datatype (see contrib/seg/) into a sort of "text segment" thingy and use the GiST indexing support on that. You'd have a query like WHERE min_max_object overlaps-operator 'ABCDE' and the overlaps operator would be a GiST-indexable one. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Getting FK relationships from information_schema
Kyle <[EMAIL PROTECTED]> writes: > I'm trying to get my application to deduce foreign key relationships > automatically so it can perform appropriate joins for the user. I'm new > to information_schema and having problems getting what I want. > ... > I can determine all the primary key fields nicely, and I can tell what > fields are foreign keys. The problem is, I can't determine where the > foreign keys are pointing. The problem is, the constraint names ($1, > $2, etc.) are not unique so I don't know how to join the third query > into the fourth. Hmm, this is messy :-(. The SQL spec requires constraint names to be unique within a schema. Postgres doesn't require them to be unique even within a table. We were aware that there were some compatibility issues there, but I hadn't realized that the information_schema design is fundamentally dependent on the assumption of schema-wide uniqueness for these names. For a number of reasons (backwards compatibility being the hardest to argue with), adopting the spec's restriction on constraint names seems unlikely to happen. You could of course follow it within your own database designs, but I don't foresee Postgres enforcing it on everyone. In the short run I think your only answer is to dig deeper than information_schema and look directly at the Postgres catalogs. In the long run it'd be nice to have a cleaner answer, but I'm not sure what it ought to look like. Can we get away with adding implementation-specific columns to information_schema tables? If not, what other alternatives are there? regards, tom lane ---(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] Scalar in a range (but textual not numeric)
Richard Huxton <[EMAIL PROTECTED]> writes:
> On Wednesday 25 February 2004 21:32, Tom Lane wrote:
>>> SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max;
>>
>> Even if it did realize that, it couldn't do much, because this query
>> isn't indexable as it stands.
> Well, it is in the sense that an index can be used. Here I'd defined pkey as
> (min,max,id) and set enable_seqscan=off
> -> Index Scan using prnblock_range_pkey on prnblock_range
> (cost=0.00..1403.99 rows=892 width=33) (actual time=23.88..24.07 rows=1
> loops=1)
> Index Cond: (('09050091234'::character varying >= pr_min) AND
> ('09050091234'::character varying <= pr_max))
You're mistaking "I can put all the relevant columns into an index" for
"this index is useful". There's a reason why the planner will not use
that plan without a gun to its head, and it is that you have only a
one-sided restriction on the first index column. At runtime, the index
machinery will have to scan starting at pr_min = '09050091234' and
continuing clear to the end of the index. It will avoid visiting the
heap for the index entries past the desired range, but since it does not
understand that there's any relation between the pr_min and pr_max
columns, it won't realize that it doesn't need to continue the index
scan past the first failure of '09050091234' <= pr_max. For all it
knows, there could be lots of entries with larger pr_min and smaller
pr_max.
Now that I think about it, you can exploit your knowledge that the
min/max subranges don't overlap without building a new index type.
What you have to do is put the knowledge into the query. Instead of
WHERE 'ABCDE' >= pr_min AND 'ABCDE' <= pr_max
try writing
WHERE 'ABCDE' >= pr_min AND 'ABCDE' <= pr_max
AND pr_min < (SELECT pr_min FROM table
WHERE pr_min > 'ABCDE'
ORDER BY pr_min LIMIT 1)
The idea here is to add an upper bound on pr_min to the index scan
conditions, so that the scan can stop short of the end of the index.
The sub-SELECT will efficiently use the index to pick out a safe
stopping point.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
