命令行登陆
两步:
sudo -i -u postgres
psql
一步:
sudo -u postgres psql
使用 pgcli,登录方法也是类似
sudo -i -u pgadmin
pgcli
查看 Postgres 版本信息
命令:
psql --version
登陆数据库后,还可以使用查询:
SELECT version();
创建新角色(role)
初始时,只有一个角色:postgres
切换成 postgres 用户后,就可以用下面的命令创建新用户:
postgres@VM-0-9-ubuntu:~$ createuser --interactive
同样,在其他用户下,可以直接使用命令:
sudo -u postgres createuser --interactive
修改密码
登录数据库,修改密码
ALTER USER davide WITH PASSWORD 'hu8jmn3';
创建数据库
在Linux命令行下!使用命令创建数据库:
postgres@VM-0-9-ubuntu:~$ createdb mydb
PostgreSQL 默认是角色可以访问同名数据库!
Linux user
a Linux user with the same name as your Postgres role and database.
这三者同名:Linux 账户、Prostgres 角色、Postgres 数据库
创建 Linux 账户:
sudo adduser postg1
创建角色(用postgres角色登陆):
createuser postg1
创建数据库(用postgres角色登陆):
createdb postg1
这样,使用 postg1 用户登陆,就可以使用 psql 命令来访问数据库。
连接数据库
方法一:
psql -d postgres
方法二,启动 psql 后切换:
\c dbname
查看当前连接数据库的信息
使用命令:
postg1=> \conninfo
You are connected to database "postg1" as user "postg1" via socket in "/var/run/postgresql" at port "5432".``
数据库 URL
数据库 url:
postgres://{db_username}:{db_password}@{host}:{port}/{db_name}
例如:
postgres://olawalequest@127.0.0.1:5432/reflection_db
显示全部表
\dt
显示表结构 \d, \d+
\d:
postg1=> \d playg
Table "public.playg"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('playg_id_seq'::regclass)
color | character varying(25) | | not null |
\d+ 显示更多内容:
postg1=> \d+ playg
Table "public.playg"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('playg_id_seq'::regclass) | plain | |
color | character varying(25) | | not null | | extended | |
还可以使用 SQL 标准语句:
postg1=> select column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'playg';
column_name | data_type | character_maximum_length
-------------+-------------------+--------------------------
id | integer |
color | character varying | 25
(2 rows)
修改用户密码
登陆:
sudo -u user_name psql db_name
修改密码:
ALTER USER user_name WITH PASSWORD 'new_password';
另外一个修改密码的方法:
ALTER ROLE username WITH PASSWORD 'password';
删除表的一栏
ALTER TABLE table_name
DROP COLUMN column_name;
增加表的一栏
ALTER TABLE table_name
ADD COLUMN new_column_name data_type;
给表栏增加注释
comment on column TABLE.userid is 'The user ID';
修改栏的默认值
ALTER TABLE ONLY users ALTER COLUMN lang SET DEFAULT 'en_GB';
ONLY 的解释:
“If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered” postgresql.org/docs/9.3/static/sql-altertable.html
删除栏的默认值
ALTER TABLE <table> ALTER COLUMN <column> DROP DEFAULT;
时间值默认当前时间
ALTER TABLE mytable ADD COLUMN created_at TIMESTAMP;
ALTER TABLE mytable ALTER COLUMN created_at SET DEFAULT now();
显示注释
SELECT c.table_schema,c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables as st
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position
and c.table_schema=st.schemaname and c.table_name=st.relname);
输出结果:
table_schema | table_name | column_name | description
--------------+------------+-------------+----------------------
public | events | uid | id from wechat_users
public | events | status | 1.start 2.end
(1 row)
https://stackoverflow.com/a/4946306/3054511