[SQL] refining view using temp tables
Using postgre with dotnet VS8. I came up with a scheme of having a web user
search thru the database and selecting into a temp table. Then a further
"refined" search would use the temp table as input and another temp table as
output. Then swap the source and destination so as to not use up resources.
Maybe this is not a good mechanism. I found right away I could not easily
check for table existence nor drop non-existent tables without getting a
server error (even with try {} catch {}).
I may have the same user bring up two or more pages during the same session.
Ditto for other users. I would not want to code up temporary names using
timestamps and usersnames if I could avoid it. It would be nice if the sql
engine would handle this for me. Can someone suggest how postgresql could
handle a google like "search within results".
--
View this message in context:
http://www.nabble.com/refining-view-using-temp-tables-tf2546966.html#a7097897
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] Distribution of results
Hi, I have a table like this: create table(xyz char(10), answer number(4)). There are a lot of rows in this table. I am looking at a query that will help me represent the distribution of data records based on the column answer. For example, we may take that the highest entry for answer column is 90 and the lowest is 2 and there are 1000 records. I am looking at a query that will tell how the 1000 records are distributed between the highest and lowest answer (in this case between 90 and 2). Can anyone please help? Regards, Raghu The information contained in, or attached to, this e-mail, contains confidential information and is intended solely for the use of the individual or entity to whom they are addressed and is subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of the company. The recipient should check this e-mail and any attachments for the presence of viruses. The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. www.aztecsoft.com
Re: [SQL] fetching unique pins in a high-transaction environment...
I think we've figured out a way to implement the equivalent of a READPAST hint in a function. The basic idea is to loop until we find the next available unlocked row, using the lock_not_available exception to determine if the record is locked or not. Our early testing seems to indicate that this solution will work, but we would love to hear about simpler and more efficient ways to accomplish this. Here's a simplified version of the function which illustrates the principle: CREATE OR REPLACE FUNCTION "getpin"() RETURNS varchar as $$ DECLARE v_id integer := 0; v_pin varchar; BEGIN LOOP BEGIN -- Find the first available PIN. -- Note: we cannot lock down the row here since we need to be -- able to store the ID of the pin to implement the READPAST. select id into v_id from pins where id > v_id and status = 0 order by id limit 1; -- Exit if there are no PINs available. IF NOT FOUND THEN RAISE EXCEPTION 'no pins available'; END IF; -- Lock down the PIN. If another transaction beat us to it, we -- trap the error (see below) and loop looking for the next -- available pin. If another transaction already updated the -- status to 1 in between this select and the previous, then we -- loop (see ELSE statement). select pin into v_pin from pins where id = v_id and status = 0 for update nowait; IF FOUND THEN -- Update the PIN. The status = 0 check is unnecessary, -- but better safe than sorry. update pins set status = 1 where id = v_id and status = 0; -- I don't think this should ever happen. IF NOT FOUND THEN RAISE EXCEPTION 'this should never happen'; END IF; RETURN v_pin; ELSE -- Somebody snuck in and updated/grabbed the pin. Loop. END IF; EXCEPTION WHEN lock_not_available THEN -- Loop looking for the next available unlocked pin. END; END LOOP; END; $$ language plpgsql; Thanks... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] show privileges
Hi, How can I view the privileges that an user or a role has? Or what is the equivalent of "show privileges" from MySQL? Thanks, Ray ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Composite Types
Hello
I am using postgres r8.1 and i make 2 Composite Types
as:
CREATE TYPE regiao AS (pais_nome
varchar(150), estado_nome
varchar(150), estado_sigla
varchar(4), cidade_nome varchar(150));ALTER TYPE
regiao OWNER TO postgres;==
and
CREATE TYPE telcontato AS
(telefone1 varchar(15), telefone2
varchar(15), telefone3 varchar(15));ALTER TYPE
telcontato OWNER TO postgres;===
and a table as
REATE TABLE unidades( unidade_pa int4
NOT NULL, unidade_tipo int4, unidade_nome varchar(100) NOT
NULL, unidade_razao varchar(150), unidade_bandeira
varchar(1), unidade_endereco varchar(150), unidade_bairro
varchar(80), unidade_regiao regiao, unidade_cep
varchar(10), unidade_cnpj varchar(110), unidade_radio
varchar(15), unidade_contato telcontato, unidade_abertura
date, unidade_contrato date, CONSTRAINT unidades_pkey
PRIMARY KEY (unidade_pa)) WITHOUT OIDS;ALTER TABLE unidades OWNER TO
postgres;
I can insert without problems rows in my table and
of corse I can retreave this information if I use select * from
.
ok
My problem is , How I can make a query to
retreave the cidade_nome from table unidades
If I use for example
select unidade_regiao.estado_sigla from
unidades
I recive this message:
ERROR: missing FROM-clause entry for
table "unidade_regiao"
If I try use
select unidade_regiao.(estado_sigla) from unidades
I recive this message
ERROR: syntax error at or near "(" at character 23
So I ask How I can retrive a specific information if I use a Composite
Types ?
Thaks any help
J. V. Finger
[SQL] fetching unique pins in a high-transaction environment...
Hi, I posted this question to the "general" forum, but then discovered this one which I think is more appropriate. Apologies for the cross-post. We are in the process of porting an application from SQL Server to PostgresQL. We have a table which contains a bunch of prepaid PINs. What is the best way to fetch the next available unique pin from the table in a high-traffic environment with lots of concurrent requests? For example, our PINs table might look like this and contain thousands of records. (FYI, the PIN numbers are generated by a third party and loaded into the table): IDPIN USED_BYDATE_USED 100 1864678198 101 7862517189 102 6356178381 10 users request a pin at the same time. What is the easiest/best way to ensure that the 10 users will get 10 unique pins, while eliminating any waiting? SQL Server supports the notion of a SELECT FOR UPDATE with a READPAST hint which tells SQL Server to skip over locked rows instead of waiting until the lock is lifted. This guarantees a unique pin will be acquired every time without hampering performance. Is there any equivalent in Postgres? Any help would be greatly appreciated... ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Database recovery in postgres 7.2.4.
Hi All. My setup is as follows: OS: Sun Solaris 5.8. Postgres: 7.2.4 I have very large database, which contain 15 tables and each table is contain more than 10,00,000 records. My application is parsing text data files and inserting records into database. When this process was running last night, machine was got down because of power failure. Today when I come back to office and I have compaired record count in data files and in database and find that some records are missing in database. Then I have checked postgres log and found log similar to as follows: = DEBUG: The DataBase system was not properly shut down Automatic recovery is in progress... DEBUG: Redo starts at (0, 1064) = I have read some WAL related stuff on postgres site but not found any solution to recover those uncommited records. Can any one tell me how to recover those missing records? Thanks, Santosh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] PLPGSQL question
Hi All,I have a stored procedure that fails and don't know how to fix it, so I hope to find some help here.I works on a table called 'locations' that looks like this:design=# \d locations Table "public.locations" Column | Type | Modifiers-+--+- store | character(1) | not null coord | character varying(8) | not null room | real | not null mu | smallint | not null block | boolean | default false tags | bit varying(100) | default B'0'::"bit" details | text |Indexes: "locations_pkey" PRIMARY KEY, btree (store, coord) "store_coord_locations" btree (store, coord)Check constraints: "locations_room_check" CHECK (room >= 0.0::double precision)Foreign-key constraints: "locations_store_fkey" FOREIGN KEY (store) REFERENCES stores(code)After some work, it tries to execute the following update:UPDATE locations SET tags=SUBSTRING(bv,1,r),room=room-$3 WHERE coord=$2 AND store=$1;and the variables, shown byraise notice '% % % % % %',bv,r,$3,$2,$1,data.room;look like this:NOTICE: 11101111100...0 13 4 BK1B ÷ 4.58The first value is a binary string, 96 bits long, the tail are just zeroes, so I cut it.The initial values in the table are:design=# select * from locations where coord='BK1B' and store=chr(247); store | coord | room | mu | block | tags | details---+---+--++---+---+- ÷ | BK1B | 4.58 | 1 | f | 101011111 | D(1 row)And now the error:ProgrammingError: ERROR: new row for relation "locations" violates check constraint"location_room_check"and then the contextIf I try to make the update manualy, works flawless (as 4.58-4 >= 0.0).For now I droped the constrained, but how can I solve it?Thanks,Sorin
[SQL] a celko-puzzle from long time ago
While reading celko's SQL puzzles (second edition) i followed a reference to http://www.dbmsmag.com/9801d06.html. There is a puzzle that counts items in boxes. When i try to run the proposed solution on PG 8.1.5, PG says: ERROR: column reference "qty" is ambiguous apparently the variable declaration used in the solution is not proper for PostgreSQL. Is there a way to solve this puzzle in PostgreSQL? /stig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Function to reset sequence.....
It is not the answer to your question, but please note that the NATURAL JOIN may cause problems if the underlaying database structure changes (for example after a version upgrade). See the following thread for more information: http://forums.oracle.com/forums/thread.jspa?threadID=440287 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Nested select
Hi, I am migrating my application from MySQL to Postgresql and have met following situation: SELECT (sum(sold_price)/(select sum(sold_price) from car_archive))*100 as CA_pcent, reason_text FROM car_archive group by reason_text order by CA_pcent desc works perfectly in MySQL, but not in Postgresql. The problem seems to be the nested select which should deliver a sum of the whole table, but it does not. If I replace this nested select with a fix value, then it works: SELECT (sum(sold_price)/(333))*100 as CA_pcent, reason_text FROM car_archive group by reason_text order by CA_pcent desc Has someone a solution for that situation? Thanks, Hubert Retif
Re: [SQL] Nested select
Can you write the error message here? --Imad www.EnterpriseDB.com On 11/6/06, Hubert Retif <[EMAIL PROTECTED]> wrote: Hi, I am migrating my application from MySQL to Postgresql and have met following situation: SELECT (sum(sold_price)/(select sum(sold_price) from car_archive))*100 as CA_pcent, reason_text FROM car_archive group by reason_text order by CA_pcent desc works perfectly in MySQL, but not in Postgresql. The problem seems to be the nested select which should deliver a sum of the whole table, but it does not. If I replace this nested select with a fix value, then it works: SELECT (sum(sold_price)/(333))*100 as CA_pcent, reason_text FROM car_archive group by reason_text order by CA_pcent desc Has someone a solution for that situation? Thanks, Hubert Retif ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Composite Types
"Jose" <[EMAIL PROTECTED]> writes: > If I try use > select unidade_regiao.(estado_sigla) from unidades No, you should do select (unidade_regiao).estado_sigla from unidades regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Groups and Roles and Users
Hi list,Could someone suggest me how to get a documentation about Groups and Roles and Users ?It would be nice to see a simple and easy documentation (a tutorial could be better, with pgadmin much better). Regards =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Atenciosamente (Sincerely)Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] fetching unique pins in a high-transaction environment...
On Sun, 2006-10-29 at 10:36, Bobus wrote:
> Hi,
>
> I posted this question to the "general" forum, but then discovered this
> one which I think is more appropriate. Apologies for the cross-post.
>
> We are in the process of porting an application from SQL Server to
> PostgresQL.
>
> We have a table which contains a bunch of prepaid PINs. What is the
> best way to fetch the next available unique pin from the table in a
> high-traffic environment with lots of concurrent requests?
>
> For example, our PINs table might look like this and contain thousands
> of records. (FYI, the PIN numbers are generated by a third party and
> loaded into the table):
>
> IDPIN USED_BYDATE_USED
>
> 100 1864678198
> 101 7862517189
> 102 6356178381
>
>
> 10 users request a pin at the same time. What is the easiest/best way
> to ensure that the 10 users will get 10 unique pins, while eliminating
> any waiting?
Best practice, to me, is to do a couple things. One, create a sequence
and set it to the first available pin number. Let's say you have pins
available from the number 1 to . Create a default sequence, it'll
start on 1. Then, select nextval('yourseqhere') and use that to fetch
the pin like so:
begin;
select nextval('yourseqhere'); -- store in a var
update pin set date_used=now() where id=$var and date_used IS NULL
If date_used is not null, then someone grabbed it from you. Given that
we're grabbing them using a sequence, this is unlikely, but you never
know when things might go south.
Otherwise you just reserved it. Then grab it:
select pin from table where id=$var;
commit;
if a transaction fails, you might not use a pin, no big loss. Better
than accidentally giving it out twice.
I'd wrap what I just wrote in a simple pl/pgsql script using security
definer and set the perms so ONLY the user defined function can get you
a new pin.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Nested select
=?us-ascii?Q?Hubert_Retif?= <[EMAIL PROTECTED]> writes: > I am migrating my application from MySQL to Postgresql and have met > following situation: > ... > works perfectly in MySQL, but not in Postgresql. If you want useful help, you need to explain exactly what results you got and what you expected to get. "Works perfectly" is content-free. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Distribution of results
"Raghuraman K" <[EMAIL PROTECTED]> writes: > Hi, > >I have a table like this: create table(xyz char(10), answer number(4)). > There are a lot of rows in this table. I am looking at a query that will help > me represent the distribution of data records based on the column answer. For > example, we may take that the highest entry for answer column is 90 and the > lowest is 2 and there are 1000 records. I am looking at a query that will > tell how the 1000 records are distributed between the highest and lowest > answer (in this case between 90 and 2). Can anyone please help? > I believe this isn't hard if you use a statistical function. You can have one fairly quickly with PL/R. -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Composite Types
This is *not* a good practice to handle elements of a composite data
type seperately because they all build it up together and without one
of it, the data type is meaningless.
Secondly, If you are going to base our application on this principle,
you better rethink your design.
--Imad
www.EnterpriseDB.com
On 11/1/06, Jose <[EMAIL PROTECTED]> wrote:
Hello
I am using postgres r8.1 and i make 2 Composite Types
as:
CREATE TYPE regiao AS
(pais_nome varchar(150),
estado_nome varchar(150),
estado_sigla varchar(4),
cidade_nome varchar(150));
ALTER TYPE regiao OWNER TO postgres;
==
and
CREATE TYPE telcontato AS
(telefone1 varchar(15),
telefone2 varchar(15),
telefone3 varchar(15));
ALTER TYPE telcontato OWNER TO postgres;
===
and a table as
REATE TABLE unidades
(
unidade_pa int4 NOT NULL,
unidade_tipo int4,
unidade_nome varchar(100) NOT NULL,
unidade_razao varchar(150),
unidade_bandeira varchar(1),
unidade_endereco varchar(150),
unidade_bairro varchar(80),
unidade_regiao regiao,
unidade_cep varchar(10),
unidade_cnpj varchar(110),
unidade_radio varchar(15),
unidade_contato telcontato,
unidade_abertura date,
unidade_contrato date,
CONSTRAINT unidades_pkey PRIMARY KEY (unidade_pa)
)
WITHOUT OIDS;
ALTER TABLE unidades OWNER TO postgres;
I can insert without problems rows in my table and of corse I can retreave
this information if I use select * from .
ok
My problem is , How I can make a query to retreave the cidade_nome from
table unidades
If I use for example
select unidade_regiao.estado_sigla from unidades
I recive this message:
ERROR: missing FROM-clause entry for table "unidade_regiao"
If I try use
select unidade_regiao.(estado_sigla) from unidades
I recive this message
ERROR: syntax error at or near "(" at character 23
So I ask How I can retrive a specific information if I use a Composite Types
?
Thaks any help
J. V. Finger
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Distribution of results
What else do you want to know about it? (keeping in mind the example you gave) Because, apparently this is just a matter of min and max. --Imad On 11/1/06, Raghuraman K <[EMAIL PROTECTED]> wrote: Hi, I have a table like this: create table(xyz char(10), answer number(4)). There are a lot of rows in this table. I am looking at a query that will help me represent the distribution of data records based on the column answer. For example, we may take that the highest entry for answer column is 90 and the lowest is 2 and there are 1000 records. I am looking at a query that will tell how the 1000 records are distributed between the highest and lowest answer (in this case between 90 and 2). Can anyone please help? Regards, Raghu The information contained in, or attached to, this e-mail, contains confidential information and is intended solely for the use of the individual or entity to whom they are addressed and is subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of the company. The recipient should check this e-mail and any attachments for the presence of viruses. The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. www.aztecsoft.com
Re: [SQL] a celko-puzzle from long time ago
> While reading celko's SQL puzzles (second edition) i followed a reference to > http://www.dbmsmag.com/9801d06.html. > There is a puzzle that counts items in boxes. > When i try to run the proposed solution on PG 8.1.5, PG says: ERROR: column > reference "qty" is > ambiguous > > > apparently the variable declaration used in the solution is not proper for > PostgreSQL. Is there > a way to solve this puzzle in PostgreSQL? I believe that qty is a parameter variable that must first be set in psql. I've seen some thread in various lists that describe how to set parameter variables before executing a query that uses them. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Groups and Roles and Users
> Could someone suggest me how to get a documentation about Groups and Roles > and Users ? > > It would be nice to see a simple and easy documentation (a tutorial could be > better, with pgadmin much better). This is the documentment that I know of: http://www.postgresql.org/docs/8.1/interactive/user-manag.html http://www.postgresql.org/docs/8.1/interactive/client-authentication.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] fetching unique pins in a high-transaction environment...
> Best practice, to me, is to do a couple things. One, create a sequence
> and set it to the first available pin number. Let's say you have pins
> available from the number 1 to . Create a default sequence, it'll
> start on 1. Then, select nextval('yourseqhere') and use that to fetch
> the pin like so:
>
> begin;
> select nextval('yourseqhere'); -- store in a var
> update pin set date_used=now() where id=$var and date_used IS NULL
>
> If date_used is not null, then someone grabbed it from you. Given that
> we're grabbing them using a sequence, this is unlikely, but you never
> know when things might go south.
>
> Otherwise you just reserved it. Then grab it:
>
> select pin from table where id=$var;
> commit;
>
> if a transaction fails, you might not use a pin, no big loss. Better
> than accidentally giving it out twice.
>
> I'd wrap what I just wrote in a simple pl/pgsql script using security
> definer and set the perms so ONLY the user defined function can get you
> a new pin.
It is my understanding that nexval and even currentval are safe across
transactions or even user
sessions. I was curious of the datatype for pin, in the previous example I
think that it was
defined as a varchar. Perhaps casting the sequence to a varchar would be the
finial step before
updating/inserting the records.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Database recovery in postgres 7.2.4.
On Mon, 2006-10-30 at 04:25, Santosh wrote: > Hi All. > > My setup is as follows: > OS: Sun Solaris 5.8. > Postgres: 7.2.4 Just so you know, 7.2 is ancient. You should, at a minimum be running the latest 7.2 release, 7.2.8. You should really look into upgrading to a later version as soon as possible. > I have very large database, which contain 15 tables and each table is > contain more than 10,00,000 records. > > My application is parsing text data files and inserting records into > database. > > When this process was running last night, machine was got down because > of power failure. > > Today when I come back to office and I have compaired record count in > data files and in database and find that some records are missing in > database. You may have lost data. Hard to say from what you've told us. How did you determine that records are missing? > Then I have checked postgres log and found log similar to as follows: > = > DEBUG: The DataBase system was not properly shut down > Automatic recovery is in progress... > DEBUG: Redo starts at (0, 1064) > = This is normal. It's the messages after this you need to worry about. Did the logs say anything else after this? > I have read some WAL related stuff on postgres site but not found any > solution to recover those uncommited records. If the records were committed, then they'd get put into the db by the wal recovery process. If the hardware (i.e. the hard drive and its controller) wasn't lying about fsync. If the hardware was lying, you need to look into that (hint, IDE and many SATA drives lie about fsync) > Can any one tell me how to recover those missing records? there are some older tools laying about, like pgfsck that might help. I'd ask on admin or another list. SQL isn't really the list for admin / system problems... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] fetching unique pins in a high-transaction environment...
On Mon, 2006-11-06 at 14:04, Richard Broersma Jr wrote:
> > Best practice, to me, is to do a couple things. One, create a sequence
> > and set it to the first available pin number. Let's say you have pins
> > available from the number 1 to . Create a default sequence, it'll
> > start on 1. Then, select nextval('yourseqhere') and use that to fetch
> > the pin like so:
> >
> > begin;
> > select nextval('yourseqhere'); -- store in a var
> > update pin set date_used=now() where id=$var and date_used IS NULL
> >
> > If date_used is not null, then someone grabbed it from you. Given that
> > we're grabbing them using a sequence, this is unlikely, but you never
> > know when things might go south.
> >
> > Otherwise you just reserved it. Then grab it:
> >
> > select pin from table where id=$var;
> > commit;
> >
> > if a transaction fails, you might not use a pin, no big loss. Better
> > than accidentally giving it out twice.
> >
> > I'd wrap what I just wrote in a simple pl/pgsql script using security
> > definer and set the perms so ONLY the user defined function can get you
> > a new pin.
>
> It is my understanding that nexval and even currentval are safe across
> transactions or even user
> sessions. I was curious of the datatype for pin, in the previous example I
> think that it was
> defined as a varchar. Perhaps casting the sequence to a varchar would be the
> finial step before
> updating/inserting the records.
Well, in the original, he had an id field to go with the pin, so I
assumed that he was generating them ahead of time in that format. so,
given an id number that increments to accompany the pins, you can be
sure that by using a sequence you'll never accidentally grab the same
pin twice.
By wrapping the logic in a pl/pgsql function and using sec definer, you
can be sure that some bug in your app logic can give you the same pin
twice by working around your well thought out rules of how to get a new
one.
Note that I was using the date_used field as a kind of "taken" marker.
If it was set, then there was an error, and you should NOT do the select
pin from table where id=$var but in fact get a new sequence number and
try again. Or error out.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Is there anyway to...
Hello all, Is there an existing mechanism is postgresql that can automatically increment/decrement on a daily basis w/out user interaction? The use case I'm considering is where a student is in some type of contract with an instructor of some sort, and that contract puts a time limit on the student requiring her to pay a fee by a certain day. IF that day comes to pass - or a certain number of days elapse - and that payment requirement hasn't been met, I want to trigger a function. The one requirement I want to impose is, that no end user of the DB application, needs to do anything to set the trigger, other than the initialization of making the student of this type. An example would be: Day1 - Application user(typically the instructor) creates a profile for a new student - John Doe, which sets a 30 day time limit for John Doe to pay $100.00 Day2 -> Day31 - John Doe didn't make the payment Day 31 - Trigger of event occurs when the instructor logs in. Basically on Day 1 when John Doe's profile was created, I want a decrement counter to occur daily on his profile(some attribute/timer) and nothing should happen until day 31 when he doesn't pay. Any ideas? -- Email:[EMAIL PROTECTED] WebSite: http://www.linuxlouis.net "Open the pod bay doors HAL!" -2001: A Space Odyssey "Good morning starshine, the Earth says hello." -Willy Wonka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [SQL] Case Preservation disregarding case
At Teradata, we certainly interpreted the spec to allow case-preserving, but case-insensitive, identifiers. Users really liked it that way: If you re-created a CREATE TABLE statement from the catalog, you could get back exactly the case the user had entered, but people using the table didn't need to worry about case. And column titles in reports would have the nice case preserving information. Sort of like how Windows systems treat file names... The case is preserved, but you don't need to know it to access the file. I know UNIX users usually think "case-preserving with case-insensitive" a foreign concept, but that doesn't mean the average user feels the same. If I want my column named "WeeklyTotalSales", I really don't want to have to always quote it and type in the exact case. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Monday, October 30, 2006 7:24 PM To: beau hargis Cc: pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case sensitivity? beau hargis <[EMAIL PROTECTED]> writes: > Considering the differences that already exist between database systems and > their varying compliance with SQL and the various extensions that have been > created, I do not consider that the preservation of case for identifiers > would violate any SQL standard. That's not how I read the spec. It is true that we are not 100% spec compliant, but that isn't a good argument for moving further away from spec. Not to mention breaking backwards compatibility with our historical behavior. The change you propose would fix your application at the cost of breaking other people's applications. Perhaps you should consider fixing your app instead. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Case Preservation disregarding case
We treated quoted identifiers as case-specific, as the spec requires. In the catalog, we stored TWO columns... The column name with case converted as appropriate (as PostgreSQL already does), used for looking up the attribute, And a second column, which was the column name with the case exactly as entered by the user. So, your example would work just fine. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, October 30, 2006 10:35 PM To: Chuck McDevitt Cc: beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case sensitivity? "Chuck McDevitt" <[EMAIL PROTECTED]> writes: > At Teradata, we certainly interpreted the spec to allow case-preserving, > but case-insensitive, identifiers. Really? As I see it, the controlling parts of the SQL spec are (SQL99 sec 5.2) 26) A and a are equivalent if the of the (with every letter that is a lower-case letter replaced by the corresponding upper-case letter or letters) and the of the (with all occurrences of replaced by and all occurrences of replaced by ), considered as the repetition of a that specifies a of SQL_IDENTIFIER and an implementation-defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". 27) Two s are equivalent if their s, considered as the repetition of a that specifies a of SQL_IDENTIFIER and an implementation-defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". Note well the "sensitive to case" bits there. Now consider CREATE TABLE tab ( "foobar" int, "FooBar" timestamp, "FOOBAR" varchar(3) ); We can *not* reject this as containing duplicate column names, else we have certainly violated rule 27. Now what will you do with SELECT fooBar FROM tab; ? The spec is unquestionably on the side of "you selected the varchar column"; historical Postgres practice is on the side of "you selected the int column". AFAICS a case-insensitive approach would have to fail with some "I can't identify which column you mean" error. I am interested to see where you find support for that in the spec... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Case Preservation disregarding case
Oh... And Microsoft SQLServer does something similar. At Greenplum, we've already gotten complaints from customers about this when they were switching from MSSQL to GP's PostgreSQL-based database. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, October 30, 2006 10:35 PM To: Chuck McDevitt Cc: beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case sensitivity? "Chuck McDevitt" <[EMAIL PROTECTED]> writes: > At Teradata, we certainly interpreted the spec to allow case-preserving, > but case-insensitive, identifiers. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Case Preservation disregarding case
There was some discussion a couple of years ago on the -hackers list about it, so you might like to review the archives. The consensus seemed to be that behaviour would need to be set no later than createdb time. The options I thought of were: . current postgres behaviour (we need to do this for legacy reasons, of course, as well as to keep happy the legions who hate using upper case for anything) . strictly spec compliant (same as current behaviour, but folding to upper case for unquoted identifiers rather than lower) . fully case sensitive even for unquoted identifiers (not spec compliant at all, but nevertheless possibly attractive especially for people migrating from MS SQLServer, where it is an option, IIRC). To this you propose, as I understand it, to have a fourth possibility which would be spec compliant for comparison purposes but would label result set columns with the case preserved name originally used (or would you use the casing used in the query?). These could be accomplished I think with a second catalog column like you suggest, in a number of places, but making sure all the code paths were covered might be somewhat laborious. We could probably add the second option without being nearly so invasive, though, and some people might feel that that would be sufficient. cheers andrew Chuck McDevitt wrote: We treated quoted identifiers as case-specific, as the spec requires. In the catalog, we stored TWO columns... The column name with case converted as appropriate (as PostgreSQL already does), used for looking up the attribute, And a second column, which was the column name with the case exactly as entered by the user. So, your example would work just fine. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, October 30, 2006 10:35 PM To: Chuck McDevitt Cc: beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case sensitivity? "Chuck McDevitt" <[EMAIL PROTECTED]> writes: At Teradata, we certainly interpreted the spec to allow case-preserving, but case-insensitive, identifiers. Really? As I see it, the controlling parts of the SQL spec are (SQL99 sec 5.2) 26) A and a are equivalent if the of the (with every letter that is a lower-case letter replaced by the corresponding upper-case letter or letters) and the of the (with all occurrences of replaced by and all occurrences of replaced by ), considered as the repetition of a that specifies a of SQL_IDENTIFIER and an implementation-defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". 27) Two s are equivalent if their s, considered as the repetition of a that specifies a of SQL_IDENTIFIER and an implementation-defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". Note well the "sensitive to case" bits there. Now consider CREATE TABLE tab ( "foobar" int, "FooBar" timestamp, "FOOBAR" varchar(3) ); We can *not* reject this as containing duplicate column names, else we have certainly violated rule 27. Now what will you do with SELECT fooBar FROM tab; ? The spec is unquestionably on the side of "you selected the varchar column"; historical Postgres practice is on the side of "you selected the int column". AFAICS a case-insensitive approach would have to fail with some "I can't identify which column you mean" error. I am interested to see where you find support for that in the spec... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Case Preservation disregarding case
Sorry, my last mail wasn't well thought out. Yes, the information_schema needs the case-folded name (although it might be ok to add additional columns to the information_schema for extra information). But, stepping back from all that, what is it the users want? 1) When re-creating a CREATE TABLE statement from whatever catalog info, they'd like the names to come back exactly as then entered them. If I do: CREATE TABLE BobsTable (WeeklySales numeric(10,2), "SomeStrangeName" int); They'd like to see exactly that when the CREATE TABLE gets re-created, not what we do now: CREATE TABLE bobstable (weeklysales numeric(10,2), "SomeStrangeName" int); 2) When doing reports, they'd like the name as entered to be the title of the column: Select * from bobstable; Would be nice if they saw this: WeeklySalesSomeStrangeName ------ For compatibility with existing apps and spec compliance, you'd still want PQfname() to return the case-folded name. But there isn't any reason you couldn't also return a "suggested title" field (PQftitle?) which preserves the user's case. You could also extend the concept of a PQftitle to make nicer titles for expressions. Instead of SELECT sum(WeeklySales) from BobsTable; Producing "?column?" or somesuch to use in the report, it could return a title like "sum(WeeklySales)" -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 31, 2006 10:38 PM To: Chuck McDevitt Cc: Stephan Szabo; beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case "Chuck McDevitt" <[EMAIL PROTECTED]> writes: > Equivalent, yes. But I can interpret that clause it mean I can show > either the case folded or non-case-folded value in the information > schema, as they are equivalent. Well, that's an interesting bit of specs-lawyering, but I don't see how you can defend it against these rules in SQL99 5.2: 21) For every IB there is exactly one corresponding case-normal form CNF. CNF is an derived from IB as follows. Let n be the number of characters in IB. For i ranging from 1 (one) to n, the i-th character M(i) of IB is translated into the corresponding character or characters of CNF as follows. Case: a) If M(i) is a lower case character or a title case character for which an equivalent upper case sequence U is defined by Unicode, then let j be the number of characters in U; the next j characters of CNF are U. b) Otherwise, the next character of CNF is M(i). 22) The case-normal form of the of a is used for purposes such as and including determination of identifier equivalence, representation in the Definition and Information Schemas, and representation in diagnostics areas. NOTE 44 - Any lower-case letters for which there are no upper- case equivalents are left in their lower-case form. Again, obviously we are not compliant because we fold to lower rather than upper case, but I do not see how you can read (22) as not requiring the information schema to show the upper-cased form. The output of functions such as PQfname() might be considered closer to diagnostics info than information schema, but that's covered too. But the really serious problem with what you propose is that it would allow two table columns with names that the system considers distinct to show as the same string in the information schema and diagnostic outputs. That can't be acceptable --- it's going to break any application that does any nontrivial analysis of what it sees there, not to mention that it violates various primary key constraints in the information schema specification. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Case Preservation disregarding case
On Wed, 2006-11-01 at 11:31 -0500, Chuck McDevitt wrote: > But, stepping back from all that, what is it the users want? > > 1) When re-creating a CREATE TABLE statement from whatever catalog > info, they'd like the names to come back exactly as then entered them. > If I do: > CREATE TABLE BobsTable (WeeklySales numeric(10,2), > "SomeStrangeName" int); > > They'd like to see exactly that when the CREATE TABLE gets > re-created, not what we do now: > > CREATE TABLE bobstable (weeklysales numeric(10,2), > "SomeStrangeName" int); This would be very good indEEd. It can be very annoying trying to locate a table when the user swears they called it one thing and actually the case or quotation is different. Current behaviour isn't useful, even if it is "onspec" (or is that OnSpec?). Would be better to make this behaviour a userset switchable between the exactly compliant and the more intuitive. We have namespaces to differentiate between two sources of object names, so anybody who creates a schema where MyColumn is not the same thing as myColumn is not following sensible rules for conceptual distance. It's certainly an error of best practice, even if its not actually a bug. > 2) When doing reports, they'd like the name as entered to be the title > of the column: > Select * from bobstable; > > Would be nice if they saw this: > WeeklySalesSomeStrangeName > ------ ... > Producing "?column?" or somesuch to use in the report, it could return a > title like "sum(WeeklySales)" That would be just great. I'm not sure the spec says what the titles should be, does it? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [SQL] Case Preservation disregarding case
On Tue, Oct 31, 2006 at 12:55:46PM -0500, Andrew Dunstan wrote: > To this you propose, as I understand it, to have a fourth possibility > which would be spec compliant for comparison purposes but would label > result set columns with the case preserved name originally used (or > would you use the casing used in the query?). The big issue I see with this is that it would break PQfname on the client end, since that's case sensetive too. Most client languages are, so you really are between a rock and a hard place. Making PQfname case-insensetive also screws up in Tom's example. One way to appraoch this is to consider this a setting of the collation of the name datatype. If a case-insensetive collation is selected at initdb time, then Tom's example would indeed fail, but that's a choice someone made. Problem being, you'd have to export that choice to clients to make PQfname work, and that's going to messy. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] [SQL] Case Preservation disregarding case
-Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 31, 2006 10:23 AM To: Chuck McDevitt Cc: Tom Lane; beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case On Tue, 31 Oct 2006, Chuck McDevitt wrote: > We treated quoted identifiers as case-specific, as the spec requires. > > In the catalog, we stored TWO columns... The column name with case > converted as appropriate (as PostgreSQL already does), used for looking > up the attribute, > And a second column, which was the column name with the case exactly as > entered by the user. Wouldn't using that second column's value tend to often violate 5.2SR10 (at least that's the reference item in SQL92)? AFAICT, that rule basically says that the regular identifier is equivalent to the case-folded one for purposes of information and definition schema and similar purposes which seems like it would be intended to include things like column labeling for output. There's a little bit of flexibility there on both similar purposes and equivalence, though. Equivalent, yes. But I can interpret that clause it mean I can show either the case folded or non-case-folded value in the information schema, as they are equivalent. Anyway, we have many things that are "enhancements" beyond the spec, and this could also be considered an enhancement. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Requirement for PostgreSQL Database Developer
Hi , Location: San Diego, CA [You can also TeleCommute...] Duration: 6+ months. This is Mark with ProV International, This email is in regards to the requirement we have with one of our direct client in San Diego, CA. PostgreSQL Database Developer This position involves creating tables, views, functions and stored procedures to support front end OLTP and reporting applications. The ideal developer will have thorough knowledge of SQL (PL/pgSQL), experience with at least one other PostgreSQL language (e.g. PL/Perl), and extensive experience with complex stored procedures, code optimization, and index tuning in PostgreSQL. Ideal candidate will have the following qualifications: 5+ years database development with PostgreSQL Knowledge of at least one other language in addition to PL/pgSQL, such as PL/Perl or PL/Java. Experience implementing PostgreSQL replication . Some experience with either SQL Server 2000 or Oracle 9i/10g. Significant background in creating complex stored procedures and SQL scripts Understanding of database normalization concepts Some experience in logical and physical database design and implementation Prior experience working in a project oriented environment and meeting deadlines under tight time constraints Strong analytical skills Capable of working independently with minimal supervision. If you find yourself comfortable with this job profile & find it interesting please send me your resume in MS Word Format. thanks , Mark,ProV InternationalTampa, FL 33607Tel 408 - 241 - 7795 Xtn - 27[EMAIL PROTECTED]www.provintl.com
Re: [SQL] Requirement for PostgreSQL Database Developer
Thanks for I think the list that you are looking for is: [EMAIL PROTECTED] --- Mark <[EMAIL PROTECTED]> wrote: > > Hi , > > > Location: San Diego, CA [You can also TeleCommute...] > > Duration: 6+ months. > > > > This is Mark with ProV International, This email is in regards to the > requirement we have with > one of our direct client in San Diego, CA. > > > > PostgreSQL Database Developer > > This position involves creating tables, views, functions and stored > procedures to support front > end OLTP and reporting applications. The ideal developer will have thorough > knowledge of SQL > (PL/pgSQL), experience with at least one other PostgreSQL language (e.g. > PL/Perl), and extensive > experience with complex stored procedures, code optimization, and index > tuning in PostgreSQL. > > > > Ideal candidate will have the following qualifications: > > 5+ years database development with PostgreSQL > > Knowledge of at least one other language in addition to PL/pgSQL, such as > PL/Perl or PL/Java. > > Experience implementing PostgreSQL replication . > > Some experience with either SQL Server 2000 or Oracle 9i/10g. > > Significant background in creating complex stored procedures and SQL scripts > > Understanding of database normalization concepts > > Some experience in logical and physical database design and implementation > > Prior experience working in a project oriented environment and meeting > deadlines under tight > time constraints > > Strong analytical skills > > Capable of working independently with minimal supervision. > > > > > > If you find yourself comfortable with this job profile & find it interesting > please send me your > resume in MS Word Format. > > > > thanks , > > > > Mark, > ProV International > Tampa, FL 33607 > Tel 408 - 241 - 7795 Xtn - 27 > [EMAIL PROTECTED] > www.provintl.com > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Fwd: [SQL] refining view using temp tables
-- Forwarded message --From: Aaron Bono <[EMAIL PROTECTED]>Date: Nov 6, 2006 4:51 PM
Subject: Re: [SQL] refining view using temp tablesTo: BeemerBiker <[EMAIL PROTECTED]>Cc: pgsql-sql@postgresql.org
On 10/31/06, BeemerBiker <[EMAIL PROTECTED]> wrote:
Using postgre with dotnet VS8. I came up with a scheme of having a web usersearch thru the database and selecting into a temp table. Then a further"refined" search would use the temp table as input and another temp table as
output. Then swap the source and destination so as to not use up resources.Maybe this is not a good mechanism. I found right away I could not easilycheck for table existence nor drop non-existent tables without getting a
server error (even with try {} catch {}).I may have the same user bring up two or more pages during the same session.Ditto for other users. I would not want to code up temporary names usingtimestamps and usersnames if I could avoid it. It would be nice if the sql
engine would handle this for me. Can someone suggest how postgresql couldhandle a google like "search within results". The best approach will probably vary depending on the volume of usage on your site.
One thing I would try is to create a table for searches and search results with a primary key equal to the user's session ID or log in ID. This would only give them one search but if you need more you can use a serogate key. Then have everyone use the same table - no temp tables needed.
Table:user_search user_search_id (PK), session_id (indexed), create_dt, last_access_dtuser_search_param (one record for each search parameter they entered for the filter)
user_search_param_id (PK), user_search_id (FK), param_name, param_valueuser_search_results (one record per record returned in search) user_search_results_id (PK - this may not be necessary),
user_search_id (FK), sort_index (to help preserve sort order), values (this can be CSV, XML or even broken into another table)Then you can add a process that regularly deletes searches who's last access date is older than so many minutes (cascade delete that is). Or you can add a trigger that fires off this clean up whenever a new search is added.
== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com==
-- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
Re: [SQL] show privileges
On 11/2/06, Rares Vernica <[EMAIL PROTECTED]> wrote: Hi,How can I view the privileges that an user or a role has?Or what is the equivalent of "show privileges" from MySQL? select * from pg_user;Hey guys, this comes up every so often. Could some kind of syntax be added, at least to the psql tool, to get this kind of information. It would be really handy for administrators. Also, if it were on the documentation under the GRANT privileges section that would help immensely. I always have to hunt this down when I need it. Thanks!== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] a celko-puzzle from long time ago
Should be entirely executable in postgres using psql variables (once you get around the psql quoting weirdnesses). do this: gp_test=# \set item '\''Apple' gp_test=# \set qty 6 gp_test=# \echo :item 'Apple' gp_test=# \echo :qty 6 and then run his query. Now, i am not sure what DBMS lets Mr. Celko insert the string 'Orange' into a CHAR(5) field, but he's a smartie, so I am sure there must be a way ;-) George > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Richard > Broersma Jr > Sent: Monday, November 06, 2006 11:55 AM > To: stig erikson; pgsql-sql@postgresql.org > Subject: Re: [SQL] a celko-puzzle from long time ago > > > While reading celko's SQL puzzles (second edition) i > followed a reference to > > http://www.dbmsmag.com/9801d06.html. > > There is a puzzle that counts items in boxes. > > When i try to run the proposed solution on PG 8.1.5, PG > says: ERROR: column reference "qty" is > > ambiguous > > > > > > apparently the variable declaration used in the solution is > not proper for PostgreSQL. Is there > > a way to solve this puzzle in PostgreSQL? > > I believe that qty is a parameter variable that must first be > set in psql. I've seen some thread > in various lists that describe how to set parameter variables > before executing a query that uses > them. > > Regards, > > Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] show privileges
On Mon, 2006-11-06 at 17:01 -0600, Aaron Bono wrote: > On 11/2/06, Rares Vernica <[EMAIL PROTECTED]> wrote: > Hi, > > How can I view the privileges that an user or a role has? > > Or what is the equivalent of "show privileges" from MySQL? > > > select * from pg_user; > > Hey guys, this comes up every so often. Could some kind of syntax be > added, at least to the psql tool, to get this kind of information. It > would be really handy for administrators. Also, if it were on the > documentation under the GRANT privileges section that would help > immensely. I always have to hunt this down when I need it. You mean something like \du at the psql prompt? Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] converting Informix outer to Postgres
Hi all, I have been working on this Informix SQL query which has an outer join. I have attached Informix query and my "supposedly" solution to this query but I cannot get the same count. I appreciate for any help. Thanks. --Informix query select count(u.id) from user u, invention i, inv_contracts ic, inv_milestones im1, milestonedef mdef1, OUTER inv_milestones im2, milestonedef mdef2 where u.id = i.user_id and ic.inv_id = i.id and ic.contract_id = mdef1.contract_id and im1.inv_id = i.id and mdef1.id = im1.milestone_id and im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and ic.contract_id = mdef2.contract_id and im2.inv_id = i.id and mdef2.id = im2.milestone_id and im1.datereceived IS NULL --Postges query select count(u.id) from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef mdef1, --OUTER inv_milestones im2, milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id LEFT OUTER JOIN invention i ON im2.inv_id = i.id where u.id = i.user_id and ic.inv_id = i.id and ic.contract_id = mdef1.contract_id and im1.inv_id = i.id and mdef1.id = im1.milestone_id and im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and ic.contract_id = mdef2.contract_id and --im2.inv_id = i.id and --mdef2.id = im2.milestone_id and im1.datereceived IS NULL - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] converting Informix outer to Postgres
Why don't you start by presenting the query in a more readable form (hint use SQL-standard JOIN syntax for all of your joins) and maybe narrowing just to a couple of tables to isolate the problem. As it is, it is hard to understand. In the process of rewriting you will be forced to think about each join and maybe clarify the query for yourself. So, for example, maybe this is the query you want (just an attempt to quickly rewrite the Informix using standard JOIN syntax--I have a hunch that you want only one outer join): select count(u.id) from user u inner join invention i on u.id = i.user_id inner join inv_contracts ic on ic.inv_id = i.id inner join milestonedef mdef1 on ic.contract_id = mdef1.contract_id inner join milestonedef mdef2 on ic.contract_id = mdef2.contract_id inner join inv_milestones im1 on im1.inv_id = i.id and mdef1.id = im1.milestone_id left join inv_milestones im2 on im2.inv_id = i.id and mdef2.id = im2.milestone_id where im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and im1.datereceived IS NULL ; > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Sent: Monday, November 06, 2006 4:11 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] converting Informix outer to Postgres > > Hi all, > I have been working on this Informix SQL query which has an > outer join. > I have attached Informix query and my "supposedly" solution > to this query > but I cannot get the same count. I appreciate for any help. > Thanks. > > --Informix query > select count(u.id) > from user u, invention i, inv_contracts ic, inv_milestones > im1, milestonedef mdef1, > OUTER inv_milestones im2, > milestonedef mdef2 > where u.id = i.user_id and > ic.inv_id = i.id and > ic.contract_id = mdef1.contract_id and > im1.inv_id = i.id and > mdef1.id = im1.milestone_id and > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > ic.contract_id = mdef2.contract_id and > im2.inv_id = i.id and > mdef2.id = im2.milestone_id and > im1.datereceived IS NULL > > --Postges query > select count(u.id) > from dbuser u, inv_contracts ic, inv_milestones im1, > milestonedef mdef1, > --OUTER inv_milestones im2, > milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON > mdef2.id = im2.milestone_id > LEFT OUTER JOIN invention i ON im2.inv_id = i.id > where u.id = i.user_id and > ic.inv_id = i.id and > ic.contract_id = mdef1.contract_id and > im1.inv_id = i.id and > mdef1.id = im1.milestone_id and > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > ic.contract_id = mdef2.contract_id and > --im2.inv_id = i.id and > --mdef2.id = im2.milestone_id and > im1.datereceived IS NULL > > - > This mail sent through IMP: www.resolution.com > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] converting Informix outer to Postgres
--- [EMAIL PROTECTED] wrote: > Hi all, > I have been working on this Informix SQL query which has an outer join. > I have attached Informix query and my "supposedly" solution to this query > but I cannot get the same count. I appreciate for any help. > Thanks. > > --Informix query > select count(u.id) > from user u, invention i, inv_contracts ic, inv_milestones im1, milestonedef > mdef1, > OUTER inv_milestones im2, > milestonedef mdef2 > where u.id = i.user_id and > ic.inv_id = i.id and > ic.contract_id = mdef1.contract_id and > im1.inv_id = i.id and > mdef1.id = im1.milestone_id and > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > ic.contract_id = mdef2.contract_id and > im2.inv_id = i.id and > mdef2.id = im2.milestone_id and > im1.datereceived IS NULL > > --Postges query > select count(u.id) > from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef mdef1, > --OUTER inv_milestones im2, > milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = > im2.milestone_id > LEFT OUTER JOIN invention i ON im2.inv_id = i.id > where u.id = i.user_id and > ic.inv_id = i.id and > ic.contract_id = mdef1.contract_id and > im1.inv_id = i.id and > mdef1.id = im1.milestone_id and > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > ic.contract_id = mdef2.contract_id and > --im2.inv_id = i.id and > --mdef2.id = im2.milestone_id and > im1.datereceived IS NULL Is there a reason that these two lines are commented out in the postgresql query? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] writing a simple sql parser and database program
Hi, I would like to learn how to write a simple SQL parser and database program. This is for an embeded situation where I can't use a database program like postgre. Jumping into the postgre source code seems a little overwelming as I don't have a general understanding of how this is done. Is there a link or book that explains the basics of how a database parses SQL and how it then operates on the data? Thank you, Peter ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
