Greenplum（postgresql）常用操作记录，命令行连接数据库，增删查改。

1

create database [数据库名];

1

drop database [数据库名];

1

create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);

1

insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);

1

select * from [表名];

1

alter table [表名A] rename to [表名B];

1

drop table [表名];

1

alter table [表名] add column [字段名] [类型];

1

alter table [表名] drop column [字段名];

1

alter table [表名] rename column [字段名A] to [字段名B];

1

alter table [表名] alter column [字段名] set default [新的默认值];

1

alter table [表名] alter column [字段名] drop default;

1

update [表名] set [目标字段名]=[目标值] where [该行特征];

1

2

delete from [表名] where [该行特征];

delete from [表名]; // 删空整个表

1

2

3

4

SELECT tablename FROM pg_tables

WHERE tablename NOT LIKE 'pg%'

AND tablename NOT LIKE 'sql_%'

ORDER BY tablename;

1

postgres= # \i /root/db. sql         \i 命令用于执行一个外部的sql脚本文件。

1

2

#/opt/PostgreSQL/8. 3/bin/ pg_dump - Upostgres - C - fdb. sql database

-C create -f 是导出后的文件名

1

INSERT INTO tableAAA VALUES( x'0001f' : : integer, '鉴权' , 'Authority' )

1

2

Perhaps you will get some idea if you read the document:

37. 6. 4. Executing Dynamic Commands

1

EXECUTE 'INSERT INTO TG_RELNAME VALUES (NEW.start_time , NEW.id , NEW.end_time)';

1

2

3

delete from deltest where rownum not in(

select max(rownum) from deltest

);