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)
);
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
);
auto_increment is a MySQL feature. Postgres uses serial columns for the same purpose.
serial and bigserial. Since the Q contains bigint(20) I had chosen bigserial in my answer. This is a better match in this case.
-- 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;
Replace bigint(20) not null auto_increment by bigserial not null and
datetime by timestamp
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
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
);
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)
);
