【postgresql】pg的基本使用

最新推荐文章于 2024-10-03 00:08:07 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-10-03 00:08:07 发布

阅读量336

点赞数 1

分类专栏：数据库运维-PostgreSQL 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/141722658

版权

数据库运维-PostgreSQL 专栏收录该内容

49 篇文章 5 订阅

订阅专栏

1.创建用户和数据库

postgres=# create user sspu with password 'sspu' nocreatedb;
CREATE ROLE
postgres=# create database sspudb with owner=sspu template=template0 encoding='UTF8' lc_collate='C'
postgres-# lc_ctype='C' connection limit =-1;
CREATE DATABASE
postgres=#

2.创建表

[pgsql@oracle1:/home/pgsql]$psql -h 192.168.1.10 -p 5432 -U sspu -D sspudb
psql: invalid option -- 'D'
psql: hint: Try "psql --help" for more information.
[pgsql@oracle1:/home/pgsql]$psql -h 192.168.1.10 -p 5432 -U sspu -d sspudb
Password for user sspu: 
psql (15.3)
Type "help" for help.

sspudb=> create table my_tab1 (id int,name varchar(20));
CREATE TABLE
sspudb=> insert into my_tab1 values(1,'xsq1'),(2,'xsq2');
INSERT 0 2
sspudb=> select * from my_tab1; 
 id | name 
----+------
  1 | xsq1
  2 | xsq2
(2 rows)

3.查看数据库其他信息

sspudb=> \conninfo
You are connected to database "sspudb" as user "sspu" on host "192.168.1.10" at port "5432".
sspudb=> 
sspudb=> \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 sspu      |                                                            | {}        | 

sspudb=> select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
     5 | postgres
 16420 | sspudb
     1 | template1
     4 | template0
(4 rows)

sspudb=> select pg_database_size('sspudb');
 pg_database_size 
------------------
          7787311
(1 row)
sspudb=> select spcname from pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
(2 rows)
sspudb=> \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | my_tab1 | table | sspu
(1 row)

sspudb=> \dt
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | my_tab1 | table | sspu
(1 row)
--表大小 
sspudb=> select pg_relation_size('my_tab1');
 pg_relation_size 
------------------
             8192
(1 row)
--查看客户端字符集和归档。
sspudb=> show client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

sspudb=> show archive_command;
 archive_command 
-----------------
 (disabled)
(1 row)