[SQL] Help with "missing FROM clause" needed
Hi,
to be compatible with the postgres standard syntax in 8.1.x i need some
help for rewriting my "delete" statements ("select" is not a problem). I
use the following statement:
DELETE FROM partner_zu
WHERE partner_zu.pa_id = partner.id
AND partner_zu.m_id = '25'
AND partner.open = 'm'
AND partner.a_id = partner_zu.a_id
AND partner_zu.a_id = '104335887112347';
I need to delete some entries in partner_zu but the decision which to
delete is to be made by an entry in the table partner.
There is no foreign key from partner_zu to partner (bad design, i
know...) and i need a single (and hopefully performant) statement to do
the job.
Any help is gratefully appreciated!
-tb
--
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---(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] Help with "missing FROM clause" needed
Richard Huxton wrote:
Thomas Beutin wrote:
Hi,
to be compatible with the postgres standard syntax in 8.1.x i need
some help for rewriting my "delete" statements ("select" is not a
problem). I use the following statement:
DELETE FROM partner_zu
WHERE partner_zu.pa_id = partner.id
AND partner_zu.m_id = '25'
AND partner.open = 'm'
AND partner.a_id = partner_zu.a_id
AND partner_zu.a_id = '104335887112347';
DELETE FROM partner_zu
WHERE
partner_zu.m_id = '25'
...etc...
AND partner_zu.pa_id IN (
SELECT id FROM partner WHERE open='m' AND a_id='104335887112347'
)
Does that work for you?
What should i do with the "AND partner.a_id = partner_zu.a_id" ? Without
this it would be working fine.
Greetings,
-tb
--
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---(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] UTF-8 Problem ?
Hi Milen, Milen Kulev wrote: > Hi Listers, > I want to insert some german specific characters (umlaut characters) into a > table, but I am getting the following > Error message: > postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xfc > > Or > > postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xdf > > Here are my object/statement definitions : > > A) PREPARE stmt( int, int, int, varchar) as insert INTO part values > ($1,$2,$3,$4); > > B) > postgres=# \d+ part > Table "public.part" > Column | Type | Modifiers | Description > ++---+- > id1| integer| not null | > id2| integer| not null | > id3| integer| not null | > filler | character varying(200) | | > > C) > > postgres=# l\l >List of databases > Name| Owner | Encoding > +---+--- > db1| user1 | SQL_ASCII > postgres | pg| UTF8 > template0 | pg| UTF8 > template1 | pg| UTF8 > > > How to solve my problem ? You should insert only correct utf8 strings or set the client encoding correctly: SET client_encoding = 'LATIN1'; or SET client_encoding = 'LATIN9'; IIRC postgresql must be compiled with --enable-recode to support this. Regards, -tb ---(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] UTF-8 Problem ?
Hi Milen, Milen Kulev wrote: > What actually the compile option --enable-recode is doing ? IIRC it enables the support for string recoding, but this might not be correct anymore ... > I haven't compiled PG with this option for sure (perhaps is the option > On by defalt ?), but oyu advice hepled me: [...] You're welcome :) Regards, -tb > -Original Message- > From: Thomas Beutin [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 15, 2006 2:45 PM > To: pgsql-sql@postgresql.org > Cc: Milen Kulev > Subject: Re: [SQL] UTF-8 Problem ? > > > Hi Milen, > > Milen Kulev wrote: >> Hi Listers, >> I want to insert some german specific characters (umlaut characters) >> into a table, but I am getting the following >> Error message: >> postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; >> ERROR: invalid UTF-8 byte sequence detected near byte 0xfc >> >> Or >> >> postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; >> ERROR: invalid UTF-8 byte sequence detected near byte 0xdf >> >> Here are my object/statement definitions : >> >> A) PREPARE stmt( int, int, int, varchar) as insert INTO part values >> ($1,$2,$3,$4); >> >> B) >> postgres=# \d+ part >> Table "public.part" >> Column | Type | Modifiers | Description >> ++---+- >> id1| integer| not null | >> id2| integer| not null | >> id3| integer| not null | >> filler | character varying(200) | | >> >> C) >> >> postgres=# l\l >>List of databases >> Name| Owner | Encoding >> +---+--- >> db1| user1 | SQL_ASCII >> postgres | pg| UTF8 >> template0 | pg| UTF8 >> template1 | pg| UTF8 >> >> >> How to solve my problem ? > > You should insert only correct utf8 strings or set the client encoding > correctly: > SET client_encoding = 'LATIN1'; > or > SET client_encoding = 'LATIN9'; > > IIRC postgresql must be compiled with --enable-recode to support this. > > Regards, > -tb > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join two tables with sharing some columns between two
Hi, filippo wrote: > I have two tables like these: (this is an example, the actual tables > have diffferent fields and meanings) > > TABLE1 > id > person_name > date_arrival > date_departure > > TABLE2 > id > car_description > date_arrival > date_departure > > I'd like to make a query to have such resulting table > > RESULTING_TABLE > id > person_name > car_description > date_arrival > date_departure > > the id is the primary key for the three tables and it's unique for the > three (id in table1 cannot be in table2, I use the same counter to > generate the id for table1 and table2). SELECT id, person_name, NULL AS car_description, date_arrival, date_departure FROM TABLE1 UNION SELECT id, NULL AS person_name, car_description, date_arrival, date_departure FROM TABLE2 ORDER BY But be careful, w/o constraints there's no guarantee that the id's are really unique on both tables! HTH, -tb ---(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
