create table in postgreSQL

Question

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

ERROR: syntax error at or near "(" SQL state: 42601 Character: 41 — , Mar 22 '12 at 16:51
I also get ERROR: syntax error at or near "(" LINE 2: article_id bigint(20) NOT NULL auto_increment, — mmmmmm, Mar 22 '12 at 16:55

score 150 · Accepted Answer · answered Mar 22 '12 at 16:54

150

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

answered Mar 22 '12 at 16:54

A.H.

63,967
15
92
126

4

On why, `auto_increment` is a MySQL feature. Postgres uses `serial` columns for the same purpose. – Brad Koch Mar 29 '13 at 22:01
4

@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 07:34
2

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. – fatal_error Jan 29 '15 at 01:16

score 8 · Answer 2 · answered Dec 16 '13 at 10:41

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

score 4 · Answer 3 · edited Jul 30 '18 at 14:17

4

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

edited Jul 30 '18 at 14:17

Al-Mothafar

7,949
7
68
102

answered Mar 22 '12 at 16:52

sega_sai

8,328
1
29
38

9

Then create table will succeed. – sega_sai Mar 22 '12 at 16:56

score 0 · Answer 4 · answered Jul 22 '22 at 09:11

To create N tables with a prefix use this script. This code uses a for loop and variable to creates 10 table starting with prefix 'sbtest' namely sbtest1, sbtest2 ... sbtest10

create_table.sql

do $$
    DECLARE myvar integer;
begin
    for myvar in 1..10 loop
        EXECUTE format('CREATE TABLE sbtest%s (
        id SERIAL NOT NULL,
        k INTEGER NOT NULL,
        c CHAR(120) NOT NULL,
        pad CHAR(60) NOT NULL,
        PRIMARY KEY (id))', myvar);
    end loop;
end; $$

Command to run: psql -U user_name -d database_name -f create_table.sql

Command: \d+ sbtest

id | k | c | pad
----+---+---+-----
(0 rows)

                                                   Table "public.sbtest1"
 Column |      Type      | Collation | Nullable |               Default               | Storage  | Stats
 target | Description
--------+----------------+-----------+----------+-------------------------------------+----------+------
--------+-------------
 id     | integer        |           | not null | nextval('sbtest1_id_seq'::regclass) | plain    |
        |
 k      | integer        |           | not null |                                     | plain    |
        |
 c      | character(120) |           | not null |                                     | extended |
        |
 pad    | character(60)  |           | not null |                                     | extended |
        |
Indexes:
    "sbtest1_pkey" PRIMARY KEY, btree (id)
Access method: heap

score -5 · Answer 5 · edited Feb 27 '17 at 09:32

-5

Please try this:

CREATE TABLE article (
  article_id bigint(20) NOT NULL serial,
  article_name varchar(20) NOT NULL,
  article_desc text NOT NULL,
  date_added datetime default NULL,
  PRIMARY KEY (article_id)
);

edited Feb 27 '17 at 09:32

jww

97,681
90
411
885

answered Feb 27 '17 at 09:26

skay

1

`bigint(20) ` is invalid in Postgres. Additionally you can't specify `bigint` **and** `serial` at the same time. See the accepted answer for the correct syntax – Feb 27 '17 at 09:30

create table in postgreSQL

5 Answers5

Linked