Обсуждение: Unable to add a new column to a table named "in" (look like a bug ...)
I all, Could you just have a look to the output below? I try to add a column to a table named "in" (I know "in" is a reserved keyword but the table exists and I cannot change it). Postgresql complains that the column already exist but it didn't. Am I doing something wrong ? Thanks to point me my error or to give me any advice to add the column I need. Regards David Welcome to psql 8.3.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. postgres=# \c reference You are now connected to database "reference". reference=# \d "in" Table "public.in" Column | Type | Modifiers ------------+-----------------------------+------------------------ innum | character varying(10) | not null indesc_pri | character varying(100) | indesc_sec | character varying(100) | ts | timestamp without time zone | not null default now() Indexes: "in_pk" PRIMARY KEY, btree (innum) reference=# alter table 'IN' add column INDESCS VARCHAR[]; ERROR: syntax error at or near "'IN'" LINE 1: alter table 'IN' add column INDESCS VARCHAR[]; ^ reference=# alter table "IN" add column INDESCS VARCHAR[]; ERROR: column "indescs" of relation "IN" already exists reference=# \d "in" Table "public.in" Column | Type | Modifiers ------------+-----------------------------+------------------------ innum | character varying(10) | not null indesc_pri | character varying(100) | indesc_sec | character varying(100) | ts | timestamp without time zone | not null default now() Indexes: "in_pk" PRIMARY KEY, btree (innum) reference=# alter table "IN" add column DESCS VARCHAR[]; ALTER TABLE reference=# \d "in" Table "public.in" Column | Type | Modifiers ------------+-----------------------------+------------------------ innum | character varying(10) | not null indesc_pri | character varying(100) | indesc_sec | character varying(100) | ts | timestamp without time zone | not null default now() Indexes: "in_pk" PRIMARY KEY, btree (innum) reference=#
> Could you just have a look to the output below? I try to add a column > to a > table named "in" (I know "in" is a reserved keyword but the table > exists and > I cannot change it). Postgresql complains that the column already > exist but > it didn't. Am I doing something wrong ? Try: ALTER TABLE "in" add column INDESCS VARCHAR[]; (note the double quotes and lower-case "in")
Hi Adam, It worked Thanks!! But there is definitly something wrong with the error message I got (right?): reference=# alter table "IN" add column INDESCS VARCHAR[]; ERROR: column "indescs" of relation "IN" already exists Anyway I can workaround with what you gave me. Thanks again David -----Message d'origine----- De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Adam Rich Envoyé : Thursday, April 17, 2008 10:15 AM À : 'David Gagnon'; pgsql-general@postgresql.org Objet : Re: [GENERAL] Unable to add a new column to a table named "in" (look like a bug ...) > Could you just have a look to the output below? I try to add a column > to a > table named "in" (I know "in" is a reserved keyword but the table > exists and > I cannot change it). Postgresql complains that the column already > exist but > it didn't. Am I doing something wrong ? Try: ALTER TABLE "in" add column INDESCS VARCHAR[]; (note the double quotes and lower-case "in") -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Unable to add a new column to a table named "in" (look like a bug ...)
От
Rodrigo Gonzalez
Дата:
Are you sure? \d "IN" Let us know if you have OTHER table called IN. When you use quotes you are making them case sensitive, so "in" != "IN" != "In" etc, etc David Gagnon escribió: > Hi Adam, > > It worked Thanks!! But there is definitly something wrong with the error > message I got (right?): > > reference=# alter table "IN" add column INDESCS VARCHAR[]; > ERROR: column "indescs" of relation "IN" already exists > > Anyway I can workaround with what you gave me. > > Thanks again > David > > > > -----Message d'origine----- > De : pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] De la part de Adam Rich > Envoyé : Thursday, April 17, 2008 10:15 AM > À : 'David Gagnon'; pgsql-general@postgresql.org > Objet : Re: [GENERAL] Unable to add a new column to a table named "in" (look > like a bug ...) > > >> Could you just have a look to the output below? I try to add a column >> to a >> table named "in" (I know "in" is a reserved keyword but the table >> exists and >> I cannot change it). Postgresql complains that the column already >> exist but >> it didn't. Am I doing something wrong ? >> > > Try: ALTER TABLE "in" add column INDESCS VARCHAR[]; > > (note the double quotes and lower-case "in") > > > >
Вложения
> > It worked Thanks!! But there is definitly something wrong with the > error > message I got (right?): > > reference=# alter table "IN" add column INDESCS VARCHAR[]; > ERROR: column "indescs" of relation "IN" already exists > I don't know, what do you see when you \d "IN" ? When you use double-quotes, capitalization is significant, So "in" and "IN" and "In" are all different tables. This error message means you already have an upper-case "IN" table and it already has a column INDESCS.
Hi Adam, Yes your right .. here is the output. The only thing is that in PGadminIII I just see the "in" table.... Thanks for your help anyway! Have a good day David Welcome to psql 8.3.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. postgres=# \? General \c[onnect] [DBNAME|- USER|- HOST|- PORT|-] connect to new database (currently "postgres") \cd [DIR] change the current working directory \copyright show PostgreSQL usage and distribution terms \encoding [ENCODING] show or set client encoding \h [NAME] help on syntax of SQL commands, * for all commands \q quit psql \set [NAME [VALUE]] set internal variable, or list all if no parameters \timing toggle timing of commands (currently off) \unset NAME unset (delete) internal variable \prompt [TEXT] NAME prompt user to set internal variable \! [COMMAND] execute command in shell or start interactive shell Query Buffer \e [FILE] edit the query buffer (or file) with external editor \g [FILE] send query buffer to server (and results to file or |pipe) \p show the contents of the query buffer \r reset (clear) the query buffer \w FILE write query buffer to file ^Cpostgres=# \c reference You are now connected to database "reference". reference=# \d "IN" Table "public.IN" Column | Type | Modifiers ---------+-----------------------------+------------------------ innum | character varying(10) | not null ts | timestamp without time zone | not null default now() indescs | character varying[] | descs | character varying[] | reference=# \d "in" Table "public.in" Column | Type | Modifiers ------------+-----------------------------+------------------------ innum | character varying(10) | not null indesc_pri | character varying(100) | indesc_sec | character varying(100) | ts | timestamp without time zone | not null default now() indescs | character varying[] | Indexes: "in_pk" PRIMARY KEY, btree (innum) reference=# -----Message d'origine----- De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Adam Rich Envoyé : Thursday, April 17, 2008 10:33 AM À : 'David Gagnon'; pgsql-general@postgresql.org Objet : Re: [GENERAL] Unable to add a new column to a table named "in" (look like a bug ...) > > It worked Thanks!! But there is definitly something wrong with the > error > message I got (right?): > > reference=# alter table "IN" add column INDESCS VARCHAR[]; > ERROR: column "indescs" of relation "IN" already exists > I don't know, what do you see when you \d "IN" ? When you use double-quotes, capitalization is significant, So "in" and "IN" and "In" are all different tables. This error message means you already have an upper-case "IN" table and it already has a column INDESCS. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Forget about what I said .. I can see the IN and in table in pgadmin III .. IN is the first one... sorry about that:-) David