As shown below:
-- Create table CREATE TABLE if not exists ( id character varying(32) NOT NULL DEFAULT sys_guid(), name character varying(100) NOT NULL, gender character varying(50) NOT NULL, age character varying(10) NOT NULL, id_no character varying(50) NOT NULL, created_date timestamp without time zone DEFAULT now(), created_by character varying(100) DEFAULT 'system', updated_date timestamp without time zone DEFAULT now(), update_by character varying(100) DEFAULT 'system', CONSTRAINT user_pkey PRIMARY KEY (id) )with (oids = false); -- Comments COMMENT ON TABLE IS 'User Table'; COMMENT ON COLUMN IS 'Primary Key'; COMMENT ON COLUMN IS 'Name'; COMMENT ON COLUMN IS 'gender'; COMMENT ON COLUMN IS 'age'; COMMENT ON COLUMN .id_no IS 'Identity card number'; COMMENT ON COLUMN .created_date IS 'Create time'; COMMENT ON COLUMN .created_by IS 'Create person'; COMMENT ON COLUMN .updated_date IS 'Update time'; COMMENT ON COLUMN .update_by IS 'Update person'; -- Primary key （如果Create table语句里面没添加Primary key就执行该语句） alter table add constraint user_pkey primary key (id); -- Index or unique index drop index if exists user_name; CREATE INDEX user_name ON user (name); drop index if exists user_id_no; CREATE UNIQUE INDEX user_id_no ON user (id_no); -- Authorization GRANT ALL ON TABLE TO mydata; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE TO mydata_dml; GRANT SELECT ON TABLE TO mydata_qry;
Supplementary: The basic functions of postgresql: create tables, add new columns, modify column field names, increase the value of a certain column itself or increase the cycle itself,
Create a new table from an existing table:
CREATE TABLE "test04" AS ( select * from testdemo);
Modify the data table name:
alter table table_name (table name) rename to new_table_name (new table name)
Added column fields:
ALTER TABLE test04 ADD gid1_type integer;
Delete column fields:
ALTER TABLE test04 DROP COLUMN gid1_type;
Modify the column field name:
alter table test05 RENAME "gid" TO "id";
Modify column field type:
ALTER TABLE test05 ALTER COLUMN "gid" TYPE datatype;
Special modification to integer:
alter table table_name (table name) alter column field name type new field type using to_number (field name, '9')
Update field data:
update test05 set "gid1_type" = 0 where ("Component Type 1" = 'Fire Hydrant') or ("Component Type 1" = 'Water Meter') or ("Component Type 1" = 'Node');
In postgresql, set the existing column (num) value to increase by itself:
//Sort the table tb by name, use row_number() over() to query the sequence number and name the column rownum, create a new table tb1 and save the result to the tablecreate table tb1 as (select *, row_number() over(order by name) as rownum from tb); //According to the field name of the two tables, update the corresponding value of rownum in tb1 to the num in tb.update tb set num=(select from tb1 where = ); //Judge the existence of table tb1 and delete the tabledrop table if exists tb1;
In postgresql, loop to set the existing column (num) value to 0-9:
//Sort the table tb by name, use row_number() over() to query the sequence number and name the column rownum, create a new table tb1 and save the result to the tablecreate table tb1 as (select *, row_number() over(order by name) as rownum from tb); //According to the field name of the two tables, update the corresponding value of rownum in tb1 to num in tb. Since it is 0-9, the cycle will increase by itself, then %10update tb set num=(select from tb1 where = ) % 10; //Judge the existence of table tb1 and delete the tabledrop table if exists tb1;
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.