Skip to main content

One post tagged with "PostgreSQL"

View All Tags

数据库篇​

  • 查看已有数据库
  • \l

recipes=# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+------------+------------+-----------------------

postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |

recipes | postgres | UTF8 | en_US.utf8 | en_US.utf8 |

template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +

| | | | | postgres=CTc/postgres

(4 rows)

  • 选择数据库
  • \c + 数据库名称，例：\c recipes

表相关命令​

  • 查看所有表
  • \dt
  • \d
  • select * from pg_tables;这个命令会列出所有的，包含postgresql自己的
  • select tablename from pg_tables where schemaname='public'

List of relations

Schema | Name | Type | Owner

--------+-------+-------+----------

public | image | table | postgres

(1 row)

  • 查看表结构
  • \d + 表名，例如：\d image
  • SQL 方式

SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull

FROM pg_class c,pg_attribute a,pg_type t

WHERE c.relname = 'cms_qq' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid

ORDER BY a.attnum;


Table "public.image"

Column | Type | Collation | Nullable | Default

--------------+-----------------------------+-----------+----------+---------

orignal_path | character varying(1000) | | not null |

image_path | character varying(1000) | | not null |

dir | character varying(100) | | not null |

has_niaowo | boolean | | |

has_detected | boolean | | not null | false

created_at | timestamp without time zone | | not null | now()

deleted | boolean | | not null | false

version | integer | | not null | 0

PostgreSQL2 min read