pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: DELETE FROM fails with error
3+ messages / 3 participants
[nested] [flat]

* Re: DELETE FROM fails with error
@ 2001-03-28 15:54 ` Josh Berkus <josh@agliodbs.com>
  2 siblings, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2001-03-28 15:54 UTC (permalink / raw)
  To: chris Günther <guenther@uscreen.de>; pgsql-sql

Chris,

> I have a reference between these two tables pointing from
> tblshop.ID_Country to 
> tblcountry.ID_Country
> 
> When I try to delete a row from tblshop I get the error that postgres
> can't find
> the attribute id_shop. My SQL-command looks like follows:
> 
> 	DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12
> 
> the same happens when I try to do:
> 
> 	DELETE FROM tblshop WHERE tblshop."ID_Country" = 3
> 
> I also tried this query without quotes, with simple quotes (')
> without the leading
> tablename and so on, but I always get the error-message:

Two possibilities:

1. You're using a couple of different cases in the above example.
PostgreSQL is case-sensitive.  What interface program are you using?
What OS?  From the error messages, it looks like your commands are being
lower-cased by the interface or data transport, which would cause
Postgres not to recognize the field names.
	a. Try you commands from PSQL on the Database server, making sure that
your case is the same as the table definition.
	b. If you can down the DB for an hour, try re-naming one of the ID
fields in lower case (one that isn't foriegn keyed, of course).

2. It's possible that defining these rows as type OID requires some
special reference syntax.  If so, hopefully someone on the list will
come forward.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: DELETE FROM fails with error
@ 2001-03-28 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2001-03-28 16:17 UTC (permalink / raw)
  To: chris G nther <guenther@uscreen.de>; +Cc: pgsql-sql

chris Günther <guenther@uscreen.de> writes:
> When I try to delete a row from tblshop I get the error that postgres
> can't find the attribute id_shop. My SQL-command looks like follows:

> 	DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12

That looks like the correct way of quoting a mixed-case field name.
I think the field is not named quite like you think it is.  Try doing

	pg_dump -s -t tblshop databasename

to see what the field names really are.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: DELETE FROM fails with error
@ 2001-03-28 20:00 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2 siblings, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-03-28 20:00 UTC (permalink / raw)
  To: chris Günther <guenther@uscreen.de>; +Cc: pgsql-sql


I was going to guess that it was something related to the
foreign key, but I can't imagine why that would be affected
by a delete on the referencing table (there shouldn't be
a trigger there anyway).

Can you send full schema with constraints for the tables?

On Wed, 28 Mar 2001, chris Günther wrote:

> Hi folks,
> 
> I have the problem that I can't delete datasets out of my tables. It's like that:
> I have a table:
> tblshop 
> 	ID_Shop	oid with sequence
> 	-------
> 	Sh_Name
> 	ID_Country
> 	...
> 
> there is an index on ID_Country
> 
> 
> I have a second table:
> tblcountry
> 	ID_Country	oid with sequence
> 	----------
> 	C_Name
> 	...
> 
> I have a reference between these two tables pointing from tblshop.ID_Country to 
> tblcountry.ID_Country
> 
> When I try to delete a row from tblshop I get the error that postgres can't find
> the attribute id_shop. My SQL-command looks like follows:
> 
> 	DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12
> 
> the same happens when I try to do:
> 
> 	DELETE FROM tblshop WHERE tblshop."ID_Country" = 3
> 
> I also tried this query without quotes, with simple quotes (') without the leading
> tablename and so on, but I always get the error-message:
> 
> 	ERROR:  Attribute 'id_shop' not found
> 
> Please, can anyone help me out? It is really anoying when you can't delete datasets
> especially because my application is already online (I use postgres with PHP) and 
> there are 20 tables with alltogether 120 MB of data in it




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-03-28 20:00 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-28 15:54 ` Josh Berkus <josh@agliodbs.com>
2001-03-28 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-28 20:00 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox