current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 27 down vote favorite
7

I do not understand what is wrong with this query? Query tool does not want to create a table in PostgreSQL.

CREATE TABLE article (
article_id bigint(20) NOT NULL auto_increment,
article_name varchar(20) NOT NULL,
article_desc text NOT NULL,
date_added datetime default NULL,
PRIMARY KEY (article_id)
);
share|improve this question
1  
What error do you get? – Mark Mar 22 '12 at 16:51
    
ERROR: syntax error at or near "(" SQL state: 42601 Character: 41 – user721588 Mar 22 '12 at 16:51
1  
I also get ERROR: syntax error at or near "(" LINE 2: article_id bigint(20) NOT NULL auto_increment, – Mark Mar 22 '12 at 16:55
up vote 67 down vote accepted

First the bigint(20) not null auto_increment will not work, simply use bigserial primary key. Then datetime is timestamp in PostgreSQL. All in all:

CREATE TABLE article (
    article_id bigserial primary key,
    article_name varchar(20) NOT NULL,
    article_desc text NOT NULL,
    date_added timestamp default NULL
);
share|improve this answer
2  
On why, auto_increment is a MySQL feature. Postgres uses serial columns for the same purpose. – Brad Koch Mar 29 '13 at 22:01
2  
@BradKoch: Yes and no. PostgreSQL offers serial and bigserial. Since the Q contains bigint(20) I had chosen bigserial in my answer. This is a better match in this case. – A.H. Mar 30 '13 at 7:34
    
Given OP's apparent use case as a blog article engine, a maximum serial size of 2 billion entries (serial) should be sufficient unless he is extremely prolific. ;) But, still, bigserial is a closer match to bigint. – Jamieson Becker Jan 29 '15 at 1:16
up vote 2 down vote

Replace "bigint(20) not null auto_increment" by "bigserial not null" and "datetime" by timestamp

share|improve this answer
    
and what it will give? – user721588 Mar 22 '12 at 16:53
5  
Then create table will succeed. – sega_sai Mar 22 '12 at 16:56
up vote 1 down vote 
-- Table: "user"

-- DROP TABLE "user";

CREATE TABLE "user"
(
  id bigserial NOT NULL,
  name text NOT NULL,
  email character varying(20) NOT NULL,
  password text NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "user"
  OWNER TO postgres;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.