Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cannot drop column

Cannot drop column

From
Rich Shepard
Date:
I'm not seeing why postgres won't drop a table's column:

bustrac=# select * from statustypes order by stat_name;
      stat_name 
--------------------
  Client
  Lead
  No further contact
  Opportunity
  Proposal submitted
  Prospect
  Qualified
  Referral
  Suspect
(9 rows)

bustrac=# alter table statustypes drop column Suspect;
ERROR:  column "suspect" of relation "statustypes" does not exist
bustrac=#

What have I done incorrectly?

TIA,

Rich

Re: Cannot drop column

From
"David G. Johnston"
Date:
On Wed, Dec 18, 2024 at 11:47 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
I'm not seeing why postgres won't drop a table's column:

bustrac=# select * from statustypes order by stat_name;
      stat_name
--------------------
  Client
  Lead
  No further contact
  Opportunity
  Proposal submitted
  Prospect
  Qualified
  Referral
  Suspect
(9 rows)

bustrac=# alter table statustypes drop column Suspect;
ERROR:  column "suspect" of relation "statustypes" does not exist
bustrac=#

What have I done incorrectly?

Failed to double-quote your column name.

David J.

Re: Cannot drop column

From
Ron Johnson
Date:
On Wed, Dec 18, 2024 at 1:47 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
I'm not seeing why postgres won't drop a table's column:

bustrac=# select * from statustypes order by stat_name;
      stat_name
--------------------
  Client
  Lead
  No further contact
  Opportunity
  Proposal submitted
  Prospect
  Qualified
  Referral
  Suspect
(9 rows)

bustrac=# alter table statustypes drop column Suspect;
ERROR:  column "suspect" of relation "statustypes" does not exist
bustrac=#

What have I done incorrectly?

stat_name is the column.  "Suspect" is a row value of column stat_name.

DELETE FROM statustypes WHERE stat_name = 'Suspect';

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Re: Cannot drop column

From
Viral Shah
Date:
Since your column name has an Upper case character, you will have to use double quotes to drop it. Try alter table statustypes drop column "Suspect";

Thanks,
Viral



On Wed, Dec 18, 2024 at 1:47 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
I'm not seeing why postgres won't drop a table's column:

bustrac=# select * from statustypes order by stat_name;
      stat_name
--------------------
  Client
  Lead
  No further contact
  Opportunity
  Proposal submitted
  Prospect
  Qualified
  Referral
  Suspect
(9 rows)

bustrac=# alter table statustypes drop column Suspect;
ERROR:  column "suspect" of relation "statustypes" does not exist
bustrac=#

What have I done incorrectly?

TIA,

Rich


Re: Cannot drop column

From
Rich Shepard
Date:
On Wed, 18 Dec 2024, Viral Shah wrote:

> Since your column name has an Upper case character, you will have to use
> double quotes to drop it. Try alter table statustypes drop column "Suspect";

Viral,

Huh! I've not before encountered this in the 30+ years I've used postgres.

Thanks for the lesson.

Regards,

Rich

Re: Cannot drop column

From
Rich Shepard
Date:
On Wed, 18 Dec 2024, David G. Johnston wrote:

> Failed to double-quote your column name.

David,

Thanks. That's a new one on me.

Regards,

Rich

Re: Cannot drop column

From
Rich Shepard
Date:
On Wed, 18 Dec 2024, Rich Shepard wrote:

>> Since your column name has an Upper case character, you will have to use
>> double quotes to drop it. Try alter table statustypes drop column
>> "Suspect";

> Thanks for the lesson.

But,

bustrac=# alter table statustypes drop column "Suspect"; 
ERROR:  column "Suspect" of relation "statustypes" does not exist
bustrac=#

Double quoting didn't resolve the issue.

Rich

Re: Cannot drop column

From
"David G. Johnston"
Date:
On Wednesday, December 18, 2024, Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Wed, 18 Dec 2024, Rich Shepard wrote:

Since your column name has an Upper case character, you will have to use
double quotes to drop it. Try alter table statustypes drop column
"Suspect";

Thanks for the lesson.

But,

bustrac=# alter table statustypes drop column "Suspect"; ERROR:  column "Suspect" of relation "statustypes" does not exist
bustrac=#

Double quoting didn't resolve the issue.

Red Ron’s email…

David J. 

Re: Cannot drop column

From
Adrian Klaver
Date:

On 12/18/24 11:04 AM, Rich Shepard wrote:
> On Wed, 18 Dec 2024, Viral Shah wrote:
> 
>> Since your column name has an Upper case character, you will have to use
>> double quotes to drop it. Try alter table statustypes drop column 
>> "Suspect";
> 
> Viral,
> 
> Huh! I've not before encountered this in the 30+ years I've used postgres.

It has been that way for a long time:

https://www.postgresql.org/docs/7.0/syntax525.htm

Your issue though is as Ron pointed out:

alter table statustypes drop column Suspect

That is trying to drop a column named "Suspect" when in fact the column 
name is stat_name. 'Suspect' is a value in the column.

> 
> Thanks for the lesson.
> 
> Regards,
> 
> Rich
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com