Page 1 of 1

syntax error at or near "TYPE"

Posted: Wed Jul 25, 2007 8:22 pm
by rozvinbm_jp

Code: Select all

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: Select all

-- 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;

Posted: Wed Jul 25, 2007 8:48 pm
by superdezign

Code: Select all

ALTER TABLE `tableName` MODIFY `columnName` columnType;
That's MySQL, so it may not work, but try it anyway.

Posted: Wed Jul 25, 2007 11:31 pm
by rozvinbm_jp
superdezign wrote:

Code: Select all

ALTER TABLE `tableName` MODIFY `columnName` columnType;
That's MySQL, so it may not work, but try it anyway.

Code: Select all

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


Definitely not. Thanks ;)

Posted: Thu Jul 26, 2007 7:56 am
by miro_igov

Code: Select all

ALTER TABLE `tableName` MODIFY `columnName` columnType;
Any difference between this and your query? Why are these ` symbols surrounding the table names and column names?


What is this public. ? the database name?

Posted: Thu Jul 26, 2007 8:32 am
by Begby
miro_igov wrote:

Code: Select all

ALTER TABLE `tableName` MODIFY `columnName` columnType;
Any difference between this and your query? Why are these ` symbols surrounding the table names and column names?


What is this public. ? the database name?

This is postgres so the queries are different.

The ` symbols are qualifiers for table/column names. These are not required but a lot of query editors will put them in by default. You need them if you have spaces in your table names or if you have a table or column with the same name as a keyword (like 'desc' or 'order').

I think that public is the database name, but am not sure. Check a postgres manual.

Posted: Thu Jul 26, 2007 8:45 am
by miro_igov
Hmm right, http://www.postgresql.org/docs/8.2/inte ... table.html
ALTER [ COLUMN ] column TYPE type [ USING expression ]