syntax error at or near

1
2
3
4
5

-- NG
SELECT * FROM FROM users;

-- OK
SELECT * FROM users;

1
2
3
4
5

-- NG: 予約語をそのまま使用
SELECT user, order FROM table;

-- OK: ダブルクォートで囲む
SELECT "user", "order" FROM "table";

1
2
3
4
5

-- NG: ダブルクォートは識別子用
SELECT * FROM users WHERE name = "John";

-- OK: 文字列はシングルクォート
SELECT * FROM users WHERE name = 'John';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- NG: PostgreSQLはDELETE...LIMITをサポートしない
DELETE FROM users LIMIT 10;

-- OK: CTIDを使用
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM users LIMIT 10);

-- OK: サブクエリを使用
DELETE FROM users
WHERE id IN (SELECT id FROM users LIMIT 10);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- NG: 別テーブルとのJOIN
UPDATE orders SET status = 'completed'
FROM orders o
JOIN users u ON o.user_id = u.id;

-- OK: 正しい構文
UPDATE orders o
SET status = 'completed'
FROM users u
WHERE o.user_id = u.id;

1
2
3
4
5
6
7
8

-- NG
SELECT * FROM users WHERE id IN [1, 2, 3];

-- OK: PostgreSQL配列リテラル
SELECT * FROM users WHERE id = ANY(ARRAY[1, 2, 3]);

-- OK: INを使用
SELECT * FROM users WHERE id IN (1, 2, 3);

1
2
3
4
5
6

-- NG: MySQLスタイル
SELECT * FROM users; # コメント

-- OK: 標準SQL
SELECT * FROM users; -- コメント
SELECT * FROM users; /* コメント */

1
2
3
4
5
6
7

-- NG: MySQLスタイル
SELECT * FROM users WHERE active = 1;

-- OK: PostgreSQLスタイル
SELECT * FROM users WHERE active = true;
SELECT * FROM users WHERE active = 't';
SELECT * FROM users WHERE active IS TRUE;

1
2
3
4
5
6
7
8
9

-- クエリを整形して確認
SELECT
    id,
    name
FROM users
WHERE created_at > '2024-01-01';

-- EXPLAINでパースエラーを特定
EXPLAIN SELECT * FROM users WHERE id = 1;