Re: [SQL]
On Thursday 29 January 2004 12:02, MUKTA wrote: > > res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); > > Is there some special way to insert variables rather than plain values? do > i have to build functions (in sql) or somehting?help! Thanx Yes, but the something you want is probably ECPG - see the "client interfaces" section of the documentation. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Sometimes referential integrity seems not to work
Stephan Szabo wrote: On Sat, 31 Jan 2004, Tom Lane wrote: Stephan Szabo <[EMAIL PROTECTED]> writes: > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: >> In a database I am working, I sometimes have to delete all the records in >> some tables. According to the referential integrity defined in the creation >> of the tables, postmaster should not delete the records, but it does. I have >> used the following commands: "delete from table_1" and "truncate table_1". >> ... >> can the postgres user delete records despite referential integrity? I think the first PG release or two that had TRUNCATE TABLE would allow you to apply it despite the existence of foreign-key constraints on the table. Recent releases won't though. Yeah, truncate didn't worry me much, but the implication that delete from table_1; worked did. TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks for foreign keys. So I guess Enio is getting but ignoring the error message when trying the delete, but then the truncate does the job in his pre-7.3 database. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Sometimes referential integrity seems not to work
> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > for foreign keys. So I guess Enio is getting but ignoring the error In 7.4 truncate is transaction safe. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sometimes referential integrity seems not to work
On Mon, 2 Feb 2004, Jan Wieck wrote: > Stephan Szabo wrote: > > > On Sat, 31 Jan 2004, Tom Lane wrote: > > > >> Stephan Szabo <[EMAIL PROTECTED]> writes: > >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> In a database I am working, I sometimes have to delete all the records in > >> >> some tables. According to the referential integrity defined in the creation > >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> ... > >> >> can the postgres user delete records despite referential integrity? > >> > >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> you to apply it despite the existence of foreign-key constraints on the > >> table. Recent releases won't though. > > > > Yeah, truncate didn't worry me much, but the implication that delete from > > table_1; worked did. > > TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > for foreign keys. So I guess Enio is getting but ignoring the error > message when trying the delete, but then the truncate does the job in > his pre-7.3 database. Yes it can. I think it was starting in 7.3. => select * from test2; info - abc'123 123 (2 rows) => begin; BEGIN => truncate test2; TRUNCATE TABLE => rollback; ROLLBACK => select * from test2; info - abc'123 123 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Novice SQL Question
I need to the following query: select distinct event_code, level from logs join stat on (stat.prime is not null) where order_num = 130680; Ok, no problem. Does exactly what I want; however, I need to sort this is a particular way to get the right results. When I try to add the order by clause, I get an error. Here is the ORDER BY: ORDER BY event_date DESC, event_time DESC, event_secs DESC If I put this where I thought it should go as in: select distinct event_code,level from logs join stat on (stat.prime is not null) where order_num = 130680 order by event_date,event_time,event_secs; I get the following error: ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list No matter where I put it I get errors. Obviously, I'm a novice. Can somebody give me any advice? I'm using Postgres 7.2.3 on RedHat. Thanks... -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Novice SQL Question
To answer my own question: I discoverd that the order by fields had to be in the select list. Apparently, this is a requirement when using "DISTINCT". On Monday 02 February 2004 05:38 pm, Terry Lee Tucker wrote: > I need to the following query: > select distinct event_code, level from logs join stat on (stat.prime is not > null) where order_num = 130680; > > Ok, no problem. Does exactly what I want; however, I need to sort this is a > particular way to get the right results. When I try to add the order by > clause, I get an error. Here is the ORDER BY: > ORDER BY event_date DESC, event_time DESC, event_secs DESC > > If I put this where I thought it should go as in: > select distinct event_code,level from logs join stat on (stat.prime is not > null) where order_num = 130680 order by event_date,event_time,event_secs; > > I get the following error: > ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target > list > > No matter where I put it I get errors. Obviously, I'm a novice. Can > somebody give me any advice? I'm using Postgres 7.2.3 on RedHat. > > Thanks... -- Quote: 48 "Exceeding the bounds of authority is no more a right in a great than in a petty officer, no more justifiable in a king than in a constable; but is so much the worse in him, in that he has more trust put in him, has already a much greater share than the rest of his brethren, and is supposed from the advantages of his education, employment, and coun- sellors, to be more knowing in the measures of right and wrong." --John Locke Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Novice SQL Question
Terry Lee Tucker <[EMAIL PROTECTED]> writes: > If I put this where I thought it should go as in: > select distinct event_code,level from logs join stat on (stat.prime is not > null) where order_num = 130680 order by event_date,event_time,event_secs; > I get the following error: > ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list Right. Think about what SELECT DISTINCT does: it combines all rows with the same values of event_code and level into a single row. The group of rows with a particular pair of event_code/level might contain many different values of event_date etc. Which of these values should be used to sort the combined row? The result just isn't well-defined in general. You need to alter the query so that it completely defines the result you want. One way to do that is suggested by the error message: add the ORDER BY columns into the DISTINCT list. But that's not necessarily the way that will get the result you want. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Outer join
Hi, I'm using postgresql 7.3.4 on debian. I get bad results from a two-table left outer join. First table: select * from descriptions; desc_id | description -+- 909097 | cap 107890 | resis 223940 | ic 447652 | electro (4 rows) Second table: select * from parts; part_id | desc_id | mounting | man_id -+-+--+ 2 | 107890 | SMD | 7 1 | 909097 | LEADED | 1 3 | 223940 | LEADED | 8 (3 rows) Join: SELECT parts.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN descriptions d ON p.desc_id=d.desc_id; NOTICE: Adding missing FROM-clause entry for table "parts" part_id | desc_id | mounting | man_id | description | desc_id -+-+--++-+- 2 | 107890 | SMD | 7 | resis | 107890 1 | 909097 | LEADED | 1 | resis | 107890 3 | 223940 | LEADED | 8 | resis | 107890 2 | 107890 | SMD | 7 | ic | 223940 1 | 909097 | LEADED | 1 | ic | 223940 3 | 223940 | LEADED | 8 | ic | 223940 2 | 107890 | SMD | 7 | cap | 909097 1 | 909097 | LEADED | 1 | cap | 909097 3 | 223940 | LEADED | 8 | cap | 909097 (9 rows) /\ /\ || || p.desc_id d.desc_id I don't see why there are rows with p.desc_id and d.desc_id different. (I learnt sql last week) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Outer join
Dnia 2004-02-03 07:28, Użytkownik Russell Shaw napisał: Hi, I'm using postgresql 7.3.4 on debian. I get bad results from a two-table left outer join. First table: select * from descriptions; desc_id | description -+- 909097 | cap 107890 | resis 223940 | ic 447652 | electro (4 rows) Second table: select * from parts; part_id | desc_id | mounting | man_id -+-+--+ 2 | 107890 | SMD | 7 1 | 909097 | LEADED | 1 3 | 223940 | LEADED | 8 (3 rows) Join: SELECT parts.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN ^^^ You can't access "parts" here - you used table alias, so the only way to access it is using "p.*" descriptions d ON p.desc_id=d.desc_id; NOTICE: Adding missing FROM-clause entry for table "parts" Rewrite your query and show your results. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Outer join
Tomasz Myrta wrote: Dnia 2004-02-03 07:28, Użytkownik Russell Shaw napisał: Hi, I'm using postgresql 7.3.4 on debian. I get bad results from a two-table left outer join. First table: select * from descriptions; desc_id | description -+- 909097 | cap 107890 | resis 223940 | ic 447652 | electro (4 rows) Second table: select * from parts; part_id | desc_id | mounting | man_id -+-+--+ 2 | 107890 | SMD | 7 1 | 909097 | LEADED | 1 3 | 223940 | LEADED | 8 (3 rows) Join: SELECT parts.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN ^^^ You can't access "parts" here - you used table alias, so the only way to access it is using "p.*" descriptions d ON p.desc_id=d.desc_id; NOTICE: Adding missing FROM-clause entry for table "parts" Rewrite your query and show your results. Thanks, it works now:) SELECT p.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN descriptions d ON p.desc_id=d.desc_id; part_id | desc_id | mounting | man_id | description | desc_id -+-+--++-+- 2 | 107890 | SMD | 7 | resis | 107890 3 | 223940 | LEADED | 8 | ic | 223940 1 | 909097 | LEADED | 1 | cap | 909097 (3 rows) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
