Re: [SQL] Case Sensitive "WHERE" Clauses?
On Fri, 27 Sep 2002, Ian Barwick wrote: > On Friday 27 September 2002 05:19, Tom Lane wrote: > > Ian Barwick <[EMAIL PROTECTED]> writes: > > > Anyone know what the ANSI standard is? I don`t recall any other > > > database apart from MySQL which default to case-insensitive > > > CHAR or VARCHAR columns. > > > > I believe the spec has a notion of a "collation attribute" attached > > to character-type columns. You could define a collation that makes > > comparisons case insensitive and then mark selected columns that way. > > We don't have anything like that yet, though Tatsuo has been heard > > muttering about how to make it happen ... > > For reference, MySQL treats CHAR and VARCHAR columns as > case insensitive by default; to be treated as case sensitive, fields > must be defined or redefined as CHAR BINARY / VARCHAR BINARY. > > Personally I prefer handling case (in)sensitivity explicitly in the WHERE > clause or at application level, though if the standard allows it and it's > optional, enabling specific columns to be case insensitive in comparisions > can only be a Good Thing (TM). AFAICT it's not only a table column thing, it's all the way through, most times you're specifying a character string of some sort of or another you can attach an explicit collation with COLLATE. The rules for how this all works look fairly arcane though. (As an example, it looks like group by can get them so you might be able to say "group by col1 COLLATE foo" in order to use the foo collation in order to do the grouping) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Date/Time types
Hello, I'm french, so excuse me if my english is not correct. I'd like to create a table with a Timestamp row (named date for example) and width a resolution as smaller as possible (1 microsecond if possible). What is exactly the correct query for that question ? The query I've written is : CREATE TABLE "table" ( "id" int4 NOT NULL, "date" timestamp [13] NOT NULL ) If this correct, I've a second question : how can I insert a value ??? I've tried many ways but any works ! I'm lost I've tried to cast, to use CURRENT_TIMESTAMP with a precision, but nothing works... Thanks for your help Seb ---(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
[SQL] Null not equal to '' (empty)
Why is ''(empty) not equal to null? Its a major headache when porting
from other RDBMS like Oracle. Anyone knows any easy workarounds?
i.e. if you use:create table tbl (
c1 varchar(5));
insert into tbl values ('');
select * from tbl where c1 is null; will return
zero rows.
Also try this: select TO_DATE('','DD-MM-YY'); and
this TO_DATE('','DD-Mon-YY');
;-)
-Ajit ([EMAIL PROTECTED])
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Two Permance Questions
The select without subselect of course is better, and faster. Check your "explain analyze select ..." to se the performance, and to check the index usage if any. C. CN LIOU wrote: > Hi! > > Q1. Is subquery better or join? > > For subquery: > > SELECT t1.c1,(SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1) FROM t1 > > I wonder it will loop n times if t1 returns n rows. If this is the case, is it >better to use join like this: > > SELECT t1.c1,t2.c2 FROM t1,t2 WHERE t2.c1 = t1.c1 > > Q2. If the query is not optimize like this: > > SELECT t1.c1,t2.c2 FROM t1,t2,t1,t2,t2 WHERE t2.c1=t1.c1 AND t1.c1=t2.c1 AND >t1.c1=t2.c1 > > and the size of this clause can soar up to several kbytes, then can this query cause >performance problem? > > Regards, ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] Monitoring a Query
Bruce Momjian <[EMAIL PROTECTED]> writes: > Aaron Held wrote: > > Is there any way to monitor a long running query? > > Oh, sorry, you want to know how far the query has progressed. Gee, I > don't think there is any easy way to do that. Would it be a good idea to add the time that the current query began execution at to pg_stat_activity? Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] HELP w/ SQL -- distinct select with non distinct fields?
"RVL" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > I'm work with Sybase on the Sun... and, being a clueles newbee in SQL > department, hope you could help. I have a set of data: > > acct name qty link date memo > 101 item_A 100 0001 9/2/02 blah > 101 item_A 250 0001 9/3/02 n/a > 101 item_A80 0002 9/3/02 n/a > 101 item_B90 0002 8/8/02 n/a > 101 item_B 120 0003 9/7/02 n/a > 101 item_B 100 0003 9/2/02 abcd > 102 item_B 100 0004 9/3/02 xyz > 102 item_B 100 0004 9/7/02 xyz > 102 item_C15 0005 9/1/02 n/a > 102 item_C 180 0005 9/5/02 n/a > > I need it to be consolidated by [link] and sorted by [acct] [name] and > subtotaled by [qty]. This is easy if I don't use date and memo: >SELECT DISTINCT acct, name, sum(qty), link FROM item_list >GROUP BY acct, name, link ORDER BY acct, name, line > > acct name qty link > 101 item_A 350 0001 > 101 item_A 170 0002 > 101 item_B 220 0003 > 102 item_B 200 0004 > 102 item_C 195 0005 > > However, I want [date] and [memo] from the _first_ record of the group > to be included. > > acct name qty link date memo > 101 item_A 350 0001 9/2/02 blah > 101 item_A 170 0002 9/3/02 n/a > 101 item_B 220 0003 8/8/02 n/a > 102 item_B 200 0004 9/3/02 xyz > 102 item_C 195 0005 9/1/02 n/a > > Fields [date] and [memo] are not diplicates, so I cannot consolidate > the set if I add them to SELECT. Is there another way to solve this? It helps to supply a CREATE TABLE and an INSERT so that the problem is better defined and a proposed solution can be easily tested. DATE is reserved in SQL so I'll change the column name to the less readable "d". Also, I believe your result is incorrect. CREATE TABLE item_list ( acct INT NOT NULL, name VARCHAR(10) NOT NULL, qty INT NOT NULL, link VARCHAR(5) NOT NULL, d DATETIME NOT NULL, memo VARCHAR(10) NOT NULL, PRIMARY KEY (acct, name, link, d) ) INSERT INTO item_list VALUES (101, ' item_A', 100, '0001', '20020902', 'blah') INSERT INTO item_list VALUES (101, 'item_A', 250, '0001', '20020903', 'n/a') INSERT INTO item_list VALUES (101, 'item_A',80 , '0002', '20020903', 'n/a') INSERT INTO item_list VALUES (101, 'item_B',90, '0002', '20020808', 'n/a') INSERT INTO item_list VALUES (101, 'item_B', 120, '0003', '20020907', 'n/a') INSERT INTO item_list VALUES (101, 'item_B', 100, '0003', '20020902', 'abcd') INSERT INTO item_list VALUES (102, 'item_B', 100, '0004', '20020903', 'xyz') INSERT INTO item_list VALUES (102, 'item_B', 100, '0004', '20020907', 'xyz') INSERT INTO item_list VALUES (102, 'item_C',15, '0005', '20020901', 'n/a') INSERT INTO item_list VALUES (102, 'item_C', 180, '0005', '20020905', 'n/a') SELECT acct, name, SUM(qty) AS total, link, MIN(d) AS first_date, (SELECT memo FROM item_list WHERE acct = i.acct AND name = i.name AND link = i.link AND d = MIN(i.d)) AS first_memo FROM item_list AS i GROUP BY acct, name, link ORDER BY acct, name, link which returns acct nametotal linkfirst_date first_memo 101 item_A 350 0001 2002-09-02 00:00:00.000 blah 101 item_A 80 0002 2002-09-03 00:00:00.000 n/a 101 item_B 90 0002 2002-08-08 00:00:00.000 n/a 101 item_B 220 0003 2002-09-02 00:00:00.000 abcd 102 item_B 200 0004 2002-09-03 00:00:00.000 xyz 102 item_C 195 0005 2002-09-01 00:00:00.000 n/a Regards, jag ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] HELP w/ SQL -- distinct select with non distinct fields?
I'm work with Sybase on the Sun... and, being a clueles newbee in SQL department, hope you could help. I have a set of data: acct name qty link date memo 101 item_A 100 0001 9/2/02 blah 101 item_A 250 0001 9/3/02 n/a 101 item_A80 0002 9/3/02 n/a 101 item_B90 0002 8/8/02 n/a 101 item_B 120 0003 9/7/02 n/a 101 item_B 100 0003 9/2/02 abcd 102 item_B 100 0004 9/3/02 xyz 102 item_B 100 0004 9/7/02 xyz 102 item_C15 0005 9/1/02 n/a 102 item_C 180 0005 9/5/02 n/a I need it to be consolidated by [link] and sorted by [acct] [name] and subtotaled by [qty]. This is easy if I don't use date and memo: SELECT DISTINCT acct, name, sum(qty), link FROM item_list GROUP BY acct, name, link ORDER BY acct, name, line acct name qty link 101 item_A 350 0001 101 item_A 170 0002 101 item_B 220 0003 102 item_B 200 0004 102 item_C 195 0005 However, I want [date] and [memo] from the _first_ record of the group to be included. acct name qty link date memo 101 item_A 350 0001 9/2/02 blah 101 item_A 170 0002 9/3/02 n/a 101 item_B 220 0003 8/8/02 n/a 102 item_B 200 0004 9/3/02 xyz 102 item_C 195 0005 9/1/02 n/a Fields [date] and [memo] are not diplicates, so I cannot consolidate the set if I add them to SELECT. Is there another way to solve this? Please help. Thank you. P.S. Please forward your reply to my email: rlyudmirsky@linkonline -- Rostislav "Steve" Lyudmirsky [EMAIL PROTECTED] http://rvlstuff.bizland.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Mon, Sep 23, 2002 at 09:02:00PM +0200, Manfred Koizar wrote: > On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <[EMAIL PROTECTED]> > >We concluded that the spec defines the behavior as > >implementation-dependent, > > AFAICT the spec requires the returned value to meet two conditions. > > C1: If a statement contains more than one , > they all have to return (maybe different formats of) the same value. > > C2: The returned value has to represent a point in time *during* the > execution of the SQL-statement. > > The only thing an implementor is free to choose is which point in time > "during the execution of the SQL-statement" is to be returned, i.e. a > timestamp in the interval between the start of the statement and the > first time when the value is needed. Well, what I would suggest is that when you wrap several statements into a single transaction with begin/commit, the whole lot could be considered a single statement (since they form an atomic transaction so in a sense they are all executed simultaneously). And hence Postgresql is perfectly compliant. My second point would be: what is the point of a timestamp that keeps changing during a transaction? If you want that, there are other functions that serve that purpose. > I understand that with subselects, functions, triggers, rules etc. it > is not easy to implement the specification. If we can't do it now, we > should at least add a todo and make clear in the documentation that > CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant. The current definition is, I would say, the most useful definition. Can you give an example where your definition would be more useful? -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] Monitoring a Query
Bruce Momjian dijo: > Roberto Mello wrote: > > Forgive my ignorance here, but what is GUC? And how would I access the > > query duration? > > GUC is postgresql.conf and SET commands. They are variables that can be > set. Just for the record, GUC is an acronym for "Grand Unified Configuration". -- Alvaro Herrera () "El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens) ---(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
[SQL] database abstraction -> functions
Hi, When developing applications is a good thing to create abstraction between different layers The one concerning the database would be the persistence layer. To create such abstraction I want all mij datababase activitie runned through functions. But how can I return a set of rows instead of a single datatype (I looked into the setof but never found a clear simple)? kind regards Jeroen Olthof ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Tue, Sep 24, 2002 at 10:33:51AM +0200, Manfred Koizar wrote: > > The people who wrote the specification knew about transactions. If > they had wanted what you describe above, they would have written: > > 3) If a transaction generally contains more than one reference > to one or more s, then all such ref- > erences are effectively evaluated simultaneously. The time of > evaluation of the during the execution > of the transaction is implementation-dependent. > > But they wrote "SQL-statement", not "transaction". > > >And hence Postgresql is perfectly compliant. > > I'm not so sure. > > >The current definition is, I would say, the most useful definition. Can you > >give an example where your definition would be more useful? > > I did not write the standard, I'm only reading it. I have no problem > with an implementation that deviates from the standard "because we > know better". But we should users warn about this fact and not tell > them it is compliant. At first, I also found the idea of now() freezing during a transaction odd. But now I seems the right thing to do - I can't really come up with a use-case for current_timestamp to vary. For the relational algebra and transactional logic purists out there, having current_timetamp be a fixed transaction time reinforces the 'atomicity' of a transaction - it's _supposed_ to happen all at once, as far as the rest of the system is concerned. Many parts of the the standard deviate from the ideals, however, probably due to the desire of those with existing software to make it 'standards compliant' by bending the standard, instead of fixing the software. There are places in SQL92, especially, where if you know the exact feature set of some of the big DBs from that era, you can imagine the conversation that lead to inserting specific ambiguities into the document. As you've probably noticed, SQL92 (and '99, from what I've look at in it) are _not_ examples of the clearest, most pristine english in the world. I sometimes wonder if the committee was actually an early attempt at machine generated natural language, then I realize if that were true, it would be clearer and more self-consistent. ;-) All this is a very longwinded way for me to say leave now() as transaction time, and get Peter to interpret this passage, to see what should happen with current_timestamp. He seems to be one of the best at disentagling the standards verbiage. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] lastoid from sql
Hi, how can I get tha lastoid variable from sql? thx. C. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] lastoid from sql
select :LASTOID; sorry! :) and thx;) CoL wrote: > Hi, > > how can I get tha lastoid variable from sql? > > thx. > > C. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
> > Christoph Haller wrote: > > Hi pgsql-sql list, > > I did some testing around tables using a column > > timestamp with time zone not null default now(). > > I have noticed a valuable feature: > > As long as being inside a transaction initiated by > > begin; > > the return value of the now() function does not change. > > Is this intended - as written in the documentation? > > Can I rely on it, so future releases of PostgreSQL > > will act the same. > > Yes, this is intended and will not change. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > I vote the now() behaviour not to change. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Dublicates pairs in a table.
Ries van Twisk wrote:
> I have a small question which I could not clearly find in the postgreSQL
> manual.
>
> if I create this table and index
> CRAEATE TABLE test (
> id SERIAL,
> c1 VARCHAR(32),
> c2 VARCHAR(32),
> c3 VARCHAR(32)
> );
>
> CREATE UNIQUE INDEX test_idx ON test(id, c1,c2);
>
> what I try to archive here is that I don't want duplicate pais in my table:
> example
>
> INSET INTO test (c1,c2) VALUES('a', 'a'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'b'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Allowed
> INSET INTO test (c1,c2) VALUES('a', 'a'); -- Not allowed since we already
> have a duplicate ('a', 'a') pair
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Not allowed since we already
> have a duplicate ('b', 'c') pair
>
> etc. etc. I think you get the idea...
I have the same issue with a table that currently holds well
over 600,000 rows. The case you left out is this:
INSERT INTO test (c1,c2) VALUES('a','c');
INSERT INTO test (c1,c2) VALUES('c','a');
I want that to fail, but I haven't been able to get it to fail
using unique indexes. I presume ordering is significant. Instead,
I am doing a SELECT prior to insert to insure the pair doesn't
already exist. If you've been able to get order-independent
pairs restricted to being unique using indexes, I'd like to know
about it. :-)
Kevin
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] function return multiply rows
What is going wrong here? An example of what I'm trying to do. vw_teams is a view but same problem when trying it on a single table CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;' LANGUAGE 'sql'; SELECT test(); results in test --- 137789256 137789256 (2 rows) and this is what I expect team | datum --+ groenwit | 2002-07-09 ordenbos | 2002-09-14 (2 rows) kind regard Jeroen Olthof ---(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
[SQL] Is it possible to use lo_write in SQL?
Hi! Is it possible to use lo_write in SQL? If so, how? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] PL/pgsql
Hi,
I'm a new PostgreSql user but I have some experience in Oracle. So I
decided to use PL/pgsql to build my procedure (functions).
I create an database called MyDatabase and add this language into it
with: createlang plpgsql MyDatabase. After this, I wrote a function with
pgaccess :
Name: spi_novo_parametro paramters:
returns: char language: plpgsql
DECLARE
val INTEGER :=1;
BEGIN
insert into identificador_pri values(1, "OK");
RETURN "OK";
END;
It was OK when I saved it in pgaccess front end.
When I tested it, by query tab, writing select spi_novo_parametro();, it
return me this message:
Error: can't read
"PgAcVar(mw,.pgaw:22,nrecs)": no such element in array
and the stack is:
Error: can't read "PgAcVar(mw,$wn,nrecs)": no such element in array
while executing
"For {set row 0} {$row<$PgAcVar(mw,$wn,nrecs)} {incr row}{
if {[lindex $PgAcVar(mw,$wn,nrecs) $row]>$y} break }"
(procedure "Tables::canvasClick" line 5)
invoked from within
"Tables::canvasClick .pgaw:22 229 240"
(command bound to event)
Could you help me?
Thanks in advance.
Tadao
---(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
[SQL] query problem "server sent binary data ... without prior row description ..."
I'm having a problem with postgres on HPUX. My version is:
VERSION = 'PostgreSQL 7.2.2 on hppa2.0w-hp-hpux11.11,
compiled by aCC -Ae'
I'm trying to do a query and it consistently gives the
following errors:
wily=# \a
Output format is unaligned.
wily=# \f ';'
Field separator is ';'.
wily=# \t
Showing only tuples.
wily=# select * from wt_metric_backup where intended_end_ts
< '2002-08-16 00:00:00.000-7';
the query runs for 10 minutes or so, then outputs:
server sent binary data ("B" message) without prior row
description ("T" message)
server sent binary data ("B" message) without prior row
description ("T" message)
server sent binary data ("B" message) without prior row
description ("T" message)
unexpected character n following empty query response ("I"
message)
server sent data ("D" message) without prior row description
("T" message)
server sent data ("D" message) without prior row description
("T" message)
server sent binary data ("B" message) without prior row
description ("T" message)
then it prompts me for some input:
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \.
server sent binary data ("B" message) without prior row
description ("T" message)
unexpected response from server; first received character
was "0"
lost synchronization with server, resetting connection
Asynchronous NOTIFY 'ntsTask|perform:Response Time (mccoust
r@ ' from backend with pid 1667460981 received.
I've tried this several times. After I got the error the
first time I ran a vacuum analysis on the table and added an index on the
query field (wt_metric_backup.intended_end_ts) but I get the same results.
The error log doesn't show anything unusual until after I kill the query.
Looks like it's losing packets/messages (server sent binary
data ("B" message) without prior row description ("T" message)), but the
errors are consistent between attempts - I would expect packet loss to be
random. Simpler queries such as:
select min(intended_end_ts) from wt_metric_backup;
work okay.
Any ideas?
Thanks,
Bill Jones
Systems Architect
Middleware Services
Wells Fargo Services Company
Office --415.222.5226
PCS -- 415.254.3831 ([EMAIL PROTECTED])
Views expressed are mine. Only in unusual circumstances are they shared by
my employer.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Help tuning query
All; Can anyone please help with the tuning of this query? With 77000 rows in the operator_messages database the query is taking almost 15 seconds to return. Preference woul dbe under 5 seconds if possible. System load on a dual processor P3 with 1.5GB of memory remains under .4 during the query. The query and explain are noted below as well as description of the tables; Note both ANALYZE and VACUUM have been run numerous times. any help would be appreciated. -Kev virgin=# explain SELECT opr.msg_id, opr.status, opc.std_time virgin-# FROM op_msg_folder opc, operator_messages opr virgin-# WHERE opr.username = 'khp' virgin-# AND opr.foldername = 'inbox' virgin-# and opr.msg_id = opc.msg_id; NOTICE: QUERY PLAN: Merge Join (cost=25037.29..27675.47 rows=47958 width=54) -> Index Scan using opmf_i on op_msg_folder opc (cost=0.00..1797.37 rows=48579 width=32) -> Sort (cost=25037.29..25037.29 rows=47958 width=22) -> Seq Scan on operator_messages opr (cost=0.00..20722.26 rows=47958 width=22) virgin=# \d operator_messages Table "operator_messages" Column | Type | Modifiers +--+--- msg_id | numeric | username | text | foldername | text | status | character(1) | Indexes: op_msgs_i, opr_msgs_foldername_i, opr_msgs_username_i virgin=# \d op_msgs_i Index "op_msgs_i" Column | Type +- msg_id | numeric btree virgin=# \d opr_msgs_foldername_i Index "opr_msgs_foldername_i" Column | Type +-- foldername | text btree virgin=# \d opr_msgs_username_i Index "opr_msgs_username_i" Column | Type --+-- username | text btree virgin=# \d op_msg_folder Table "op_msg_folder" Column | Type | Modifiers +--+--- msg_id | numeric | status | character(1) | std_time | text | julian_time| text | smi| character(3) | description| text | type | text | flight | text | tail | text | dep_station| text | dest_station | text | op_description | text | Unique keys: opmf_i virgin=# \d opmf_i; Index "opmf_i" Column | Type +- msg_id | numeric unique btree ---(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] Case Sensitive "WHERE" Clauses?
Ian Barwick wrote: > > Anyone know what the ANSI standard is? I don`t recall any other > database apart from MySQL which default to case-insensitive > CHAR or VARCHAR columns. SQL:1999 says collation dependent. Jochem ---(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
[SQL] 7.3 schemas
Hi! I've just started to look at 7.3, and have created a schema with the "CREATE SCHEMA" command. How do I use/connect to theese schemas? I also want to use the schemas form JDBC, how is the connect-URL sopposed to be? Any links to docs on the new schema support are appreciated. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Null not equal to '' (empty)
This is because '' is not equal to NULL
'' Means a empty string
NULL means a empty set
So this: SELECT * FROM tbl WHERE c1 IS NULL;
is totally different then: SELECT * FROM tbl WHERE c1='';
Ries
-Oorspronkelijk bericht-
Van: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Namens Ajit Aranha
Verzonden: vrijdag 20 september 2002 8:09
Aan: [EMAIL PROTECTED]
Onderwerp: [SQL] Null not equal to '' (empty)
Why is ''(empty) not equal to null? Its a major headache when porting
from other RDBMS like Oracle. Anyone knows any easy workarounds?
i.e. if you use:create table tbl (
c1 varchar(5));
insert into tbl values ('');
select * from tbl where c1 is null; will return
zero rows.
Also try this: select TO_DATE('','DD-MM-YY'); and
this TO_DATE('','DD-Mon-YY');
;-)
-Ajit ([EMAIL PROTECTED])
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Null not equal to '' (empty)
On Friday 20 Sep 2002 7:09 am, Ajit Aranha wrote:
> Why is ''(empty) not equal to null? Its a major headache when porting
> from other RDBMS like Oracle. Anyone knows any easy workarounds?
By definition it is different - null means "not known" or "no value" not empty
string. Do you think it should be zero for numbers?
> i.e. if you use:create table tbl (
> c1 varchar(5));
>insert into tbl values ('');
> select * from tbl where c1 is null; will return
> zero rows.
Yep - that's the way it should be. If you want empty-strings, ask for them. If
you don't want to allow null values in a column define it as NOT NULL.
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Dublicates pairs in a table.
On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> I have the same issue with a table that currently holds well
> over 600,000 rows. The case you left out is this:
>
> INSERT INTO test (c1,c2) VALUES('a','c');
> INSERT INTO test (c1,c2) VALUES('c','a');
>
> I want that to fail, but I haven't been able to get it to fail
> using unique indexes. I presume ordering is significant. Instead,
> I am doing a SELECT prior to insert to insure the pair doesn't
> already exist. If you've been able to get order-independent
> pairs restricted to being unique using indexes, I'd like to know
> about it. :-)
Functional indexes sir - define a function that puts the columns into a sorted
order.
richardh=> CREATE TABLE foo (a text, b text);
CREATE
richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
ERROR: DefineIndex: index function must be marked iscachable
richardh=> \i ordfn.txt
DROP
CREATE
richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
CREATE
richardh=> insert into foo values ('aa','bb');
INSERT 332596 1
richardh=> insert into foo values ('aa','cc');
INSERT 332597 1
richardh=> insert into foo values ('bb','aa');
ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
richardh=> insert into foo values ('aa','bb');
ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
Function defined as:
CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
SELECT (CASE
WHEN $1 < $2
THEN $1 || $2
ELSE $2 || $1
END) as t;
' LANGUAGE SQL WITH (iscachable);
--
Richard Huxton
---(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] 7.3 schemas
--On vendredi 27 septembre 2002 11:42 + Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > Hi! > > I've just started to look at 7.3, and have created a schema with the > "CREATE SCHEMA" command. How do I use/connect to theese schemas? I also > want to use the schemas form JDBC, how is the connect-URL sopposed to be? > > Any links to docs on the new schema support are appreciated. I believe that : http://developer.postgresql.org/docs/postgres/ddl-schemas.html is what you want :) -- Mathieu Arnold ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Date/Time types
Seb, > I'm french, so excuse me if my english is not correct. Whereas if you were a native English speaker, you would realize that there is no such thing as "correct English" > I'd like to create a table with a Timestamp row (named date for > example) and > width a resolution as smaller as possible (1 microsecond if > possible). > > What is exactly the correct query for that question ? > > The query I've written is : > CREATE TABLE "table" ( > "id" int4 NOT NULL, > "date" timestamp [13] NOT NULL ) Sorry, the syntax above creates a table with an *array* of 13 timestamp values. I don't know what the syntax you want for an extra-precision timestamp is. Assuming there is one at all, which there may not be ... I'd also advise against creating tables with reserved words (such as "date" ) as column names. It can cause you all kinds of headaches later on, -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Passing array to PL/SQL and looping
Greg, > CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS ' > DECLARE > return_array VARCHAR[]; > BEGIN > return_array[0] := ''test''; > return_array[1] := ''test 1''; > return_array[2] := ''test 2''; > RETURN (return_array); > END;' > LANGUAGE 'plpgsql'; No, it's not possible to do the above. This is a flaw in the current implementation of PL/pgSQL that will not be resolved until we attract some new Postgres hackers who really care about upgrading PL/pgSQL. Currently, if you want to use an array, it has to be passed as a parameter, or come from an external table. You cannot declare an Array data type. Annoying, really. -Josh Berkus ---(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] Dublicates pairs in a table.
What's wrong with
CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
???
Richard Huxton wrote:
>
> On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> > I have the same issue with a table that currently holds well
> > over 600,000 rows. The case you left out is this:
> >
> > INSERT INTO test (c1,c2) VALUES('a','c');
> > INSERT INTO test (c1,c2) VALUES('c','a');
> >
> > I want that to fail, but I haven't been able to get it to fail
> > using unique indexes. I presume ordering is significant. Instead,
> > I am doing a SELECT prior to insert to insure the pair doesn't
> > already exist. If you've been able to get order-independent
> > pairs restricted to being unique using indexes, I'd like to know
> > about it. :-)
>
> Functional indexes sir - define a function that puts the columns into a sorted
> order.
>
> richardh=> CREATE TABLE foo (a text, b text);
> CREATE
> richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
> ERROR: DefineIndex: index function must be marked iscachable
> richardh=> \i ordfn.txt
> DROP
> CREATE
> richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
> CREATE
> richardh=> insert into foo values ('aa','bb');
> INSERT 332596 1
> richardh=> insert into foo values ('aa','cc');
> INSERT 332597 1
> richardh=> insert into foo values ('bb','aa');
> ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
> richardh=> insert into foo values ('aa','bb');
> ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
>
> Function defined as:
> CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
> SELECT (CASE
> WHEN $1 < $2
> THEN $1 || $2
> ELSE $2 || $1
> END) as t;
> ' LANGUAGE SQL WITH (iscachable);
>
> --
> Richard Huxton
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Dublicates pairs in a table.
On Fri, 27 Sep 2002, Richard Huxton wrote:
> On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> > I have the same issue with a table that currently holds well
> > over 600,000 rows. The case you left out is this:
> >
> > INSERT INTO test (c1,c2) VALUES('a','c');
> > INSERT INTO test (c1,c2) VALUES('c','a');
> >
> > I want that to fail, but I haven't been able to get it to fail
> > using unique indexes. I presume ordering is significant. Instead,
> > I am doing a SELECT prior to insert to insure the pair doesn't
> > already exist. If you've been able to get order-independent
> > pairs restricted to being unique using indexes, I'd like to know
> > about it. :-)
>
> Function defined as:
> CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
> SELECT (CASE
> WHEN $1 < $2
> THEN $1 || $2
> ELSE $2 || $1
> END) as t;
> ' LANGUAGE SQL WITH (iscachable);
Note, that for a final system, you'll may want to also add a
delimiter that doesn't show up in $1 or $2 if ('a','ab') and
('aa','b') aren't supposed to cause an error.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Null not equal to '' (empty)
Ajit, > Why is ''(empty) not equal to null? Its a major headache when > porting > from other RDBMS like Oracle. '' is not equal to NULL because that is the ANSI SQL92 and SQL99 international specification. The fact that other databases fail to follow the specification (and '' = NULL is *not* standard Oracle 8 behavior, either, so I don't know what you are porting from ... MS Access?) is not our concern. NULL is not equal to *anything*, including itself. Nor is it greater or less than anything. In fact, any operation involving NULL should result in NULL. This is the SQL spec, becuase NULL represents "unknown" and thus cannot be evaluated. For my intranet applications, I wrote a set of functions called "is_empty(data)" since my web programmer is rather liberal in substituting NULL for '' or for '0' or whatever. They go like this: CREATE FUNCTION is_empty( VARCHAR ) RETURNS BOOLEAN AS ' SELECT $1 IS NULL OR BTRIM($1) = ''; ' LANGUAGE 'sql' WITH (ISCACHABLE); CREATE FUNCTION is_empty( NUMERIC ) RETURNS BOOLEAN AS ' SELECT $1 IS NULL OR $1 = 0::NUMERIC; ' LANGUAGE 'sql' WITH (ISCACHABLE); etc. This will give you an all-purpose "empty value" detector. -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Dublicates pairs in a table.
On Friday 27 Sep 2002 5:17 pm, Jean-Luc Lachance wrote:
> What's wrong with
> CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
> ???
Because he specifically wanted values of ('a','b') and ('b','a') to be treated
as equivalent (see quote).
> > > INSERT INTO test (c1,c2) VALUES('a','c');
> > > INSERT INTO test (c1,c2) VALUES('c','a');
Note Stephen Szabo's observation that I'd missed the obvious need for some
separator so ('a','ab') is different from ('aa','b') - Doh!
- Richard Huxton
---(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] function return multiply rows
Jeroen Olthof wrote: > What is going wrong here? > > An example of what I'm trying to do. > > vw_teams is a view but same problem when trying it on a single table > CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;' > LANGUAGE 'sql'; > > SELECT test(); > > results in > >test > --- > 137789256 > 137789256 > (2 rows) The capability to return composite types (multi-column rows) is limited in <= PostgreSQL 7.2.x. What you are seeing are pointers to the rows, not the rows themselves. Version 7.3, in beta testing now, will do what you are looking for. If you can, please give it a try. See: http://developer.postgresql.org/docs/postgres/xfunc-tablefunctions.html for more info and examples. HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Dublicates pairs in a table.
Oh, sorry I missed that.
Still if C1 and C2 are interchangable, a rule could force C1 <= C2 and
swap them if necessary.
Richard Huxton wrote:
>
> On Friday 27 Sep 2002 5:17 pm, Jean-Luc Lachance wrote:
> > What's wrong with
> > CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
> > ???
>
> Because he specifically wanted values of ('a','b') and ('b','a') to be treated
> as equivalent (see quote).
>
> > > > INSERT INTO test (c1,c2) VALUES('a','c');
> > > > INSERT INTO test (c1,c2) VALUES('c','a');
>
> Note Stephen Szabo's observation that I'd missed the obvious need for some
> separator so ('a','ab') is different from ('aa','b') - Doh!
>
> - Richard Huxton
---(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] [GENERAL] CURRENT_TIMESTAMP
SQL> create table rbr_foo (a date); Table created. SQL> begin 2 insert into rbr_foo select sysdate from dual; [...wait about 10 seconds...] 3 insert into rbr_foo select sysdate from dual; 4 end; 5 / PL/SQL procedure successfully completed. SQL> select * from rbr_foo; A - SEP 27, 2002 12:57:27 SEP 27, 2002 12:57:27 Note that, as near as I can tell, Oracle 8 does NOT have timestamp or current_timestamp. Online docs say both are present in Oracle 9i. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Constraint problems
Hello,
I'm trying to create a constraint that will check to see if the inserted data is found
in another column in the table.
It could be that I'm going about this the wrong way, any clues for me?
Here is the present setup data for the table.
--SNIP--
--
--
--
DROP SEQUENCE sysusers_user_id_seq;
CREATE SEQUENCE sysusers_user_id_seq START 1000;
DROP TABLE sysusers;
CREATE TABLE sysusers (
user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
UNIQUE NOT NULL PRIMARY KEY,
usernametext NOT NULL UNIQUE,
passwordtext NOT NULL,
createbytext NOT NULL DEFAULT 'syscreate',
status char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
comment1text,
comment2text,
tstamp timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
INSERT INTO sysusers (username, password, createby) VALUES ('syscreate', 'testing',
'syscreate');
ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
INSERT INTO sysusers (username, password) VALUES ('gclarkii', 'testing');
--SNIP--
It is the constraint on createby that I'm trying to get to work.
Thanks for any and all help.
GB
--
GB Clark II | Roaming FreeBSD Admin
[EMAIL PROTECTED] | General Geek
CTHULU for President - Why choose the lesser of two evils?
---(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] Constraint problems
On Fri, 27 Sep 2002, GB Clark wrote:
> CREATE TABLE sysusers (
> user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> UNIQUE NOT NULL PRIMARY KEY,
> username text NOT NULL UNIQUE,
> password text NOT NULL,
>
> createby text NOT NULL DEFAULT 'syscreate',
>
> statuschar(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
> comment1 text,
> comment2 text,
> tstamptimestamp DEFAULT CURRENT_TIMESTAMP
> );
>
> CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
>
> INSERT INTO sysusers (username, password, createby) VALUES ('syscreate', 'testing',
>'syscreate');
>
> ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
This is only going to check the value of username in the current row.
Why not use a foreign key from createby to username?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] PGSQL-Performance mailing list.
Hey, folks! I'm mailing to remind everyone about the new PGSQL-PERFORMANCE mailing list. This list was added about 2 weeks ago to provide a forum for the following topics: 1. Postgres server hardware tuning. 2. Postgresql.conf tweaking 3. Index management 4. VACUUMing, ANALYZE and STATISTICS 5. query parsing for efficiency 6. etc. Please join me there! To subscribe, send an e-mail to: [EMAIL PROTECTED] ... with only the word "subscribe" in the body of the message. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Constraint problems
On Fri, 27 Sep 2002 11:29:34 -0700 (PDT)
Stephan Szabo <[EMAIL PROTECTED]> wrote:
> On Fri, 27 Sep 2002, GB Clark wrote:
>
> > CREATE TABLE sysusers (
> > user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> > UNIQUE NOT NULL PRIMARY KEY,
> > usernametext NOT NULL UNIQUE,
> > passwordtext NOT NULL,
> >
> > createbytext NOT NULL DEFAULT 'syscreate',
> >
> > status char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
> > comment1text,
> > comment2text,
> > tstamp timestamp DEFAULT CURRENT_TIMESTAMP
> > );
> >
> > CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
> >
> > INSERT INTO sysusers (username, password, createby) VALUES ('syscreate',
>'testing', 'syscreate');
> >
> > ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
>
> This is only going to check the value of username in the current row.
>
> Why not use a foreign key from createby to username?
>
Thanks! The FK was just what I was looking for. I had not relized that I could
do a current table reference in a FK declaration.
Thanks,
GB
--
GB Clark II | Roaming FreeBSD Admin
[EMAIL PROTECTED] | General Geek
CTHULU for President - Why choose the lesser of two evils?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/pgsql
On Wednesday 25 September 2002 21:38, Gustavo Tadao Okida wrote: (...) > After this, I wrote a function with > pgaccess : > > Name: spi_novo_parametro paramters: > returns: char language: plpgsql > > DECLARE > val INTEGER :=1; > BEGIN > insert into identificador_pri values(1, "OK"); > RETURN "OK"; > END; > > It was OK when I saved it in pgaccess front end. > > When I tested it, by query tab, writing select spi_novo_parametro();, it > return me this message: > > Error: can't read > "PgAcVar(mw,.pgaw:22,nrecs)": no such element in array (...) I don't know PgAccess, but possibly your function is incorrect and the errors produced on execution are confusing PgAccess? Try defining and executing the function in the psql command line client. This may give you a better idea of what is happening. I would also strongly recommend replacing the double quotes with pairs of single quotes, e.g. ''OK'' instead of "OK" . Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
