postgres2. Use a separate ALTER TABLE statement 3. Use Table

postgresql中类似IFNULL用法

COALESCE('字段','字段')

SELECT FName,FBirthDay,FRegDay, 
COALESCE(FBirthDay,FRegDay,'2008-08-08')  AS ImportDay  
FROM T_Person

值类型转换

两种方式

CAST('1' as int)

'1'::integer	('data':datatype datatype为你想要的数据类型 1::character varying)

本人项目中用到的 order_num 数据属性为integer,实体类为String 然后报错改为 #{orderNum}::integer 解决

postgresql 类似 find_in_set

mysql: select * from treenodes where FIND_IN_SET(id, '1,2,3,4,5'); 
postgresql: select * from treenodes where id = ANY(STRING_TO_ARRAY('1,2,3,4,5',','));

date 字符串转换为当天时间起点和当天时间终点

'2021-01=07'::timestamp + time '23:59:59'

怎样给postgres添加外键(PostgreSQL - How to add a foreign key)

创建表

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    full_name TEXT
);

CREATE TABLE orders (
    order_id SERIAL,
    dish_name TEXT,
    customer_id INTEGER
);

And you want to make customer_id from orders refer to id from customer

There are a couple ways to do that

1.Define the foreign key inside the CREATE TABLE statement

CREATE TABLE orders (
    order_id SERIAL,
    dish_name TEXT,
    customer_id INTEGER REFERENCES customers (id)
);

2. Use a separate ALTER TABLE statement

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers (id);

3. Use TablePlus GUI tool for Postgres

You can create a foreign key directly from the GUI Tool.

Select to view table orders from the right sidebar
Switch to structure tab at the bottom bar
Choose customer_id column and add a foreign key in the foreign_key field. Remember to press Cmd + S to commit the changes to the server

参考