CODEWITHSUNDEEP
postgresqlcreate-table
user721588
user721588

Reputation:

create table in postgreSQL

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

Upvotes: 81

Views: 211920

Answers (6)

Andriy Balitskyy
Andriy Balitskyy

Reputation: 56

For PostgreSQL 10 and above (please avoid using serial)

CREATE TABLE article (
  article_id bigint generated always as identity primary key,
  article_name varchar(20) NOT NULL,
  article_desc text NOT NULL,
  date_added timestamp default NULL
);

Upvotes: 0

Deepakshi Mittal
Deepakshi Mittal

Reputation: 27

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

Upvotes: 0

segasai
segasai

Reputation: 8548

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

Upvotes: 4

skay
skay

Reputation: 1

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

Upvotes: -6

tree em
tree em

Reputation: 21771

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

Upvotes: 8

A.H.
A.H.

Reputation: 66283

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

Upvotes: 153

Related Questions