Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2005
    Posts
    18

    Unanswered: syntax error at or near "TYPE"

    Code:
    ALTER TABLE public.postcodes
      ALTER COLUMN machi
      TYPE varchar(100);
    Error: ERROR: syntax error at or near "TYPE";
    Error while executing the query (State:42601, Native Code: 7)



    I am using WinSQL, Windows XP SP2 Japanese Version, PostgreSQL 8.x.
    I want to alter my machi field from varchar(30) to varchar(100) TYPE.

    This is my CREATE TABLE script:

    Code:
    -- Table: public.postcodes
    
    -- DROP TABLE public.postcodes;
    
    CREATE TABLE public.postcodes (
      postcode  varchar(8) NOT NULL,
      post      varchar(6) NOT NULL,
      kencd     varchar(3) NOT NULL,
      ken       varchar(30) NOT NULL,
      shicd     varchar(4) NOT NULL,
      shi       varchar(30) NOT NULL,
      ku        varchar(30),
      machi     varchar(30) NOT NULL,
      status    boolean,
      remarks   text,
      /* Keys */
      CONSTRAINT postcodes_pkey
        PRIMARY KEY (postcode)
    ) WITHOUT OIDS;
    
    ALTER TABLE public.postcodes
      OWNER TO postgres;
    bachie
    student
    php&postgresql
    Shizuoka-ken, Japan

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it again without the word TYPE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2005
    Posts
    18
    Quote Originally Posted by r937
    try it again without the word TYPE

    Code:
    ALTER TABLE public.postcodes
      ALTER COLUMN machi
      varchar(100);
    Error: ERROR: syntax error at or near "varchar";
    Error while executing the query (State:42601, Native Code: 7)
    bachie
    student
    php&postgresql
    Shizuoka-ken, Japan

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937
    try it again without the word TYPE
    According to the manual the type keyword is required.
    Code:
    ALTER TABLE public.postcodes
      ALTER COLUMN machi
      TYPE varchar(100);
    is working fine on my WinXP & PG 8.2 installation.

    Is that the only command you execute? How do you run it?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat
    According to the manual the type keyword is required.
    yeah, you're right, sorry for the misinformation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    try this...
    Code:
    ALTER TABLE postcodes ALTER machi TYPE character varying(100)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Jul 2005
    Posts
    18
    shammat
    is working fine on my WinXP & PG 8.2 installation.

    Is that the only command you execute? How do you run it?
    That is the only code.
    I copy and paste only the above code in WinSQL then run.. I think via Putty.exe will be the same error...
    bachie
    student
    php&postgresql
    Shizuoka-ken, Japan

  8. #8
    Join Date
    Jul 2005
    Posts
    18
    Quote Originally Posted by loquin
    try this...
    Code:
    ALTER TABLE postcodes ALTER machi TYPE character varying(100)
    Error: ERROR: syntax error at or near "TYPE";
    Error while executing the query (State:42601, Native Code: 7)


    Still error... its new to me.. varying(100)
    bachie
    student
    php&postgresql
    Shizuoka-ken, Japan

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by bachie
    That is the only code.
    I copy and paste only the above code in WinSQL then run.. I think via Putty.exe will be the same error...
    So... you are NOT running it on a lan server, using pgAdmin, but it resides on your web host? What are you using as the query tool?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Jul 2005
    Posts
    18
    Quote Originally Posted by loquin
    So... you are NOT running it on a lan server, using pgAdmin, but it resides on your web host? What are you using as the query tool?
    Here's my setup:
    Workstation: OS - Windows XP SP2 Japanese version
    Tool - WinSQL
    WEBServer - Apache
    Server: OS - Redhat
    DBServer - PostgreSQL

    I dont think so that it is related.
    bachie
    student
    php&postgresql
    Shizuoka-ken, Japan

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by bachie
    I dont think so that it is related.
    I tried it with two different tools. Postgres' own psql and another Java based GUI. Works in both tools.
    So maybe it is related to the tool. Did you try to run your statement using psql?
    Quote Originally Posted by bachie
    I think via Putty.exe will be the same error...
    Putty cannot run SQL statements...
    But if you mean you run psql in a putty session, then I wouldn't count on "I think it will not work", but simply try it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •