在 postgreSQL 中创建表

小开

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

小开

最佳答案

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

小开

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

小开

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

小开

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