Wednesday, 14 September 2022

Set INSERT and UPDATE table alias in SQLAlchemy

How to set alias for the table in INSERT and UPDATE clauses in SQLAlchemy? Here is a PostgreSQL example that utilizes both.

CREATE TABLE person (
  id INT PRIMARY KEY,
  name CHAR(120)
);

INSERT INTO person (id, name) 
VALUES 
  (1, 'A'), 
  (2, 'B'), 
  (3, 'C'), 
  (4, 'D');

INSERT INTO person AS p (id, name) 
VALUES (1, 'A2')
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name || ' (previously ' || p.name || ')';

UPDATE person AS p 
SET name = 'B2' || ' (previously ' || p.name || ')'
WHERE p.id = 2;

SELECT * 
FROM person 
ORDER BY id;


from Set INSERT and UPDATE table alias in SQLAlchemy

No comments:

Post a Comment