• Postgresql is similar to IFNULL
COALESCE(' 名 ',' 名 ') SELECT FName,FBirthDay,FRegDay, COALESCE(FBirthDay,FRegDay,'2008-08-08') AS ImportDay FROM T_PersonCopy the code
  • Value type conversion
CAST('1' as int) '1':: INTEGER ('data':datatype datatype is the datatype you want. 1::character VARYING)Copy the code

#{orderNum}:: INTEGER

  • Postgresql similar 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',','));Copy the code
  • The date string is converted to the start time and end time of the day
'2021-01=07'::timestamp + time '23:59:59'
Copy the code

  • Postgresql-how to Add a foreign key to postgres

Create a table

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

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

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)
);
Copy the code

2. Use a separate ALTER TABLE statement

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

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

reference