Common syntax for PostgreSQL

Time:2021-9-23

Common syntax for PostgreSQL

  • SQL statement for creating database table:

    CREATE TABLE “public”.”tb_test”
    (

      "id" int8 NOT NULL,
      "title"     varchar(50) COLLATE "pg_catalog"."default",
      "ip"        varchar(50) COLLATE "pg_catalog"."default",
      "param"     varchar(500) COLLATE "pg_catalog"."default",
      "result"    varchar(500) COLLATE "pg_catalog"."default",
      "type"      varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
      "create_by" varchar(30) COLLATE "pg_catalog"."default",
      "create_time" timestamptz(6),
      "update_by" varchar(30) COLLATE "pg_catalog"."default",
      "update_time" timestamptz(6),
      "remark"    varchar(500) COLLATE "pg_catalog"."default",
      CONSTRAINT "cgm_third_docking_log_pkey" PRIMARY KEY ("id")

    )
    ;

    Comment on column “public”. Tb_test. “Id” is’ primary key ID ‘;
    Comment on column “public”. Tb_test. “Title” is’ module title ‘;
    Comment on column “public”. Tb_test. “IP” is’ IP address’;
    Comment on column “public”. Tb_test. “Param” is’ request parameter ‘;
    Comment on column “public”. Tb_test. “Result” is’ return result ‘;
    COMMENT ON COLUMN “public”.”tb_test”.”type” IS ‘type’;
    Comment on column “public”. Tb_test. “Create_by” is’ creator ‘;
    Comment on column “public”. Tb_test. “Create_time” is’ creation time ‘;
    Comment on column “public”. Tb_test. “Update_by” is’ updater ‘;
    Comment on column “public”. Tb_test. “Update_time” is’ update time ‘;
    Comment on column “public”. Tb_test. “Remark” is’ remarks’;
    Comment on table “public”. Tb_test “is’ test log table ‘;

  • SQL statement for adding table fields:

    ALTER TABLE tb_test ADD COLUMN dealer_code VARCHAR(20)   COLLATE "pg_catalog"."default";
    Comment on column "tb_test". Dealer_code "is' invitation code ';
  • Create a unique index SQL for the table

    CREATE UNIQUE INDEX uk_tb_test_type ON tb_test(type);
    
  • Create a normal index of a table

    create index idx_tb_test_id_ip on tb_test (id desc, ip desc);

  • Modify the name of the table field

    alter table tb_test rename column "result" to "Result";
    
    Or (result field is required)
    
    alter table tb_test rename column "Result" to "result";
  • Modify table field type SQL

    alter table tb_test alter column id type int8;
    alter table tb_test alter id set not null;
    alter table tb_test alter column pid type int8;
    
  • Add jsonb fields to SQL table

    ALTER TABLE tb_test ADD COLUMN followed_other_info jsonb DEFAULT '{}';
    Comment on column "tb_test". Followed_other_info "is' XXXX other information ';
  • Giving users SQL table permissions

    alter table tb_test owner to xxx_user;

  • Insert SQL statement

    Insert into “public”. Tb_region_code “values (100000, ‘China’, 0, ‘China’, 0, ‘China’, 116.368, 39.9151, ‘China’);

  • Clear all the data of the table and keep the table structure

    truncate table “public”.”tb_test”;

  • SQL to modify the length of a column

    ALTER TABLE “public”.”tb_test” ALTER COLUMN “remark” type varchar(300);

  • Update the content of the corresponding column of the SQL table record

    UPDATE tb_test SET title = ‘xxx’ where id = xxxxx

  • Delete the SQL that created the sequence

    DROP SEQUENCE IF EXISTS “public”.”gen_table_column_id_seq”;

  • SQL to create sequence

    DROP SEQUENCE IF EXISTS “public”.”gen_table_id_seq”;
    CREATE SEQUENCE “public”.”gen_table_id_seq”
    INCREMENT 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1;

  • Delete SQL of table structure (including content)

    DROP TABLE IF EXISTS “public”.”gen_table_column”;

  • Add SQL for primary key

    ALTER TABLE "public"."tb_test" ADD CONSTRAINT "tb_test_id_pkey" PRIMARY KEY ("id");
    
  • Query the data with the latest XXX value less than or equal to 2H from the current time

    SELECT
        user_id,
        device_id,
        latest_time,
        CURRENT_TIMESTAMP current_value
    FROM
        tb_device  where latest_time between (now()::timestamp + '-2 hour') and now();
    

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]