syntax error at or near "TYPE"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rozvinbm_jp
Forum Commoner
Posts: 43
Joined: Thu Jun 14, 2007 8:36 pm
Location: Fuji-shi, Shizuoka-ken, Japan

syntax error at or near "TYPE"

Post 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;
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Code: Select all

ALTER TABLE `tableName` MODIFY `columnName` columnType;
That's MySQL, so it may not work, but try it anyway.
rozvinbm_jp
Forum Commoner
Posts: 43
Joined: Thu Jun 14, 2007 8:36 pm
Location: Fuji-shi, Shizuoka-ken, Japan

Post 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 ;)
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post 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?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post by miro_igov »

Hmm right, http://www.postgresql.org/docs/8.2/inte ... table.html
ALTER [ COLUMN ] column TYPE type [ USING expression ]
Post Reply