From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Forward declaration of table |
Date: | 2016-08-24 20:27:27 |
Message-ID: | CAADeyWhcjDTu7v=q3hRog7ge0OpyAeb9-+-mR1gB9i6N7+3FAA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello again,
I have went the ALTER TABLE route to add my 2 "cyclic" FKs:
https://gist.github.com/afarber/c40b9fc5447335db7d24
And now I have these 2 tables in my 9.5.3 database:
#TABLE words_moves;
mid | action | gid | uid | played |
tiles
| score
-----+--------+-----+-----+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------
2 | play | 1 | 1 | 2016-08-24 20:36:39.888224+02 | [{"col": 7,
"row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1,
"letter": "Е"}, {"col": 7, "row": 6, "value": 10, "letter": "Ф"}]
| 13
3 | play | 2 | 1 | 2016-08-24 21:48:14.448361+02 | [{"col": 7,
"row": 12, "value": 5, "letter": "Ь"}, {"col": 7, "row": 10, "value": 1,
"letter": "Е"}, {"col": 7, "row": 9, "value": 1, "letter": "О"}, {"col": 7,
"row": 11, "value": 10, "letter": "Ш"}, {"col": 7, "row": 8, "value": 2,
"letter": "Р"}, {"col": 7, "row": 7, "value": 2, "letter": "П"}] | 31
4 | play | 1 | 2 | 2016-08-24 21:50:55.231266+02 | [{"col": 8,
"row": 8, "value": 2, "letter": "Й"}, {"col": 8, "row": 7, "value": 1,
"letter": "А"}, {"col": 8, "row": 6, "value": 2, "letter": "Р"}, {"col": 8,
"row": 5, "value": 2, "letter": "С"}]
| 33
(3 rows)
# SELECT
gid,
EXTRACT(EPOCH FROM
created)::int AS created,
player1,
COALESCE(player2, 0) AS player2,
COALESCE(EXTRACT(EPOCH FROM
played1)::int, 0) AS played1,
COALESCE(EXTRACT(EPOCH FROM played2)::int, 0) AS played2,
ARRAY_TO_STRING(hand1, '') AS hand1,
ARRAY_TO_STRING(hand2,
'') AS hand2,
bid
FROM words_games
WHERE player1 = 1
OR player2 = 1;
gid | created | player1 | player2 | played1 | played2 | hand1 |
hand2 | bid
-----+------------+---------+---------+------------+------------+---------+---------+-----
2 | 1472068074 | 1 | 0 | 1472068094 | 0 | ЫТОВЕРЛ |
ЕНХЯЭАК | 1
1 | 1472063658 | 1 | 2 | 1472063800 | 1472068255 | ВГЦЕСИУ |
ННДНСВТ | 1
(2 rows)
Then I am trying to perform the LEFT JOIN to return active games and recent
moves for player 1, but for some reason the first two columns are empty:
# SELECT
m.tiles,
m.score,
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
g.player1,
COALESCE(g.player2, 0) AS player2,
COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played1,
COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS
played2, ARRAY_TO_STRING(g.hand1, '') AS
hand1,
ARRAY_TO_STRING(g.hand2, '') AS hand2,
g.bid
FROM words_games g LEFT JOIN words_moves m
ON (g.mid1 = m.mid OR g.mid2 = m.mid)
WHERE g.player1 = 1
OR g.player2 = 1;
tiles | score | gid | created | player1 | player2 | played1 |
played2 | hand1 | hand2 | bid
-------+-------+-----+------------+---------+---------+------------+------------+---------+---------+-----
| | 2 | 1472068074 | 1 | 0 | 1472068094 |
0 | ЫТОВЕРЛ | ЕНХЯЭАК | 1
| | 1 | 1472063658 | 1 | 2 | 1472063800 |
1472068255 | ВГЦЕСИУ | ННДНСВТ | 1
(2 rows)
Why aren't m.tiles and m.score returned please?
Regards
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-08-24 20:38:44 | Re: Forward declaration of table |
Previous Message | Andreas Joseph Krogh | 2016-08-24 19:22:48 | Re: ON CONFLICT does not support deferrable unique constraints |