Thread: Forward declaration of table

On 08/23/2016 10:10 AM, Alexander Farber wrote:
> Good evening,
>
> with PostgreSQL 9.5.3 I am using the following table to store 2-player
> games:
>
>     DROP TABLE IF EXISTS words_games;
>     CREATE TABLE words_games (
>             gid SERIAL PRIMARY KEY,
>             created timestamptz NOT NULL,
>             player1 integer REFERENCES words_users(uid) ON DELETE
>     CASCADE NOT NULL,
>             player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
>             played1 timestamptz,
>             played2 timestamptz,
>             -- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
>             -- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
>             score1 integer NOT NULL CHECK(score1 >= 0),
>             score2 integer NOT NULL CHECK(score2 >= 0),
>             hand1 varchar[7] NOT NULL,
>             hand2 varchar[7] NOT NULL,
>             pile  varchar[116] NOT NULL,
>             letters varchar[15][15] NOT NULL,
>             values integer[15][15] NOT NULL,
>             bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
>     );
>
>
> This has worked well for me (when a user connects to the game server, I
> send her all games she is taking part in), but then I have decided to
> add another table to act as a "logging journal" for player moves:
>
> DROP TABLE IF EXISTS words_moves;
>
> DROP TYPE IF EXISTS words_action;
>
> CREATE TABLE words_moves (
>         mid SERIAL PRIMARY KEY,
>         action words_action NOT NULL,
>         gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
>         uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
>         played timestamptz NOT NULL,
>         tiles jsonb,
>         score integer CHECK(score > 0)
> );
>
> Also, in the former table words_games I wanted to add references to the
> latest moves performed by players:
>
>         -- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
>         -- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
>
> The intention is: whenever a player connects to the server, sent her all
> active games and status updates on the recent opponent moves.
>
> However the 2 added columns do not work:
>
>     ERROR:  relation "words_moves" does not exist
>     ERROR:  relation "words_games" does not exist
>     ERROR:  relation "words_moves" does not exist
>
>
> So my question is if I can somehow "forward declare" the words_moves table?

Off the top of my head:

Change this:
--mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
--mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,

to

mid1 integer
mid2 integer

and then after

CREATE TABLE words_moves ...

use ALTER TABLE ADD table_constraint :

https://www.postgresql.org/docs/9.5/static/sql-altertable.html

to add the FK references to word_games.

>
> Here are all tables of my game for more context:
> https://gist.github.com/afarber/c40b9fc5447335db7d24
>
> Thank you
> Alex
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/23/2016 10:29 AM, David G. Johnston wrote:
> On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:
>
>
>     use ALTER TABLE ADD table_constraint :
>
>     https://www.postgresql.org/docs/9.5/static/sql-altertable.html
>     <https://www.postgresql.org/docs/9.5/static/sql-altertable.html>
>
>     to add the FK references to word_games.
>
>
> ​Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore
> hazards here.  Maybe pg_dump is smart enough to handle this correctly,
> though - maybe by adding constraint definitions after all tables and
> columns are present.

It does. Though the usual caveats about doing partial dumps apply, eg if
I had only specified -t fk_child below I would not get fk_parent
automatically:

postgres@test=# create table fk_child(id int, fk_id int);
CREATE TABLE
postgres@test=# create table fk_parent(id int, some_id int UNIQUE);
CREATE TABLE
postgres@test=# alter table fk_child ADD CONSTRAINT fk_constraint
FOREIGN KEY (fk_id) REFERENCES fk_parent(some_id);
ALTER TABLE

pg_dump -d test -U postgres  -t fk_parent -t fk_child -f test.sql


--
-- Name: fk_child; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE fk_child (
     id integer,
     fk_id integer
);


ALTER TABLE fk_child OWNER TO postgres;

--
-- Name: fk_parent; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE fk_parent (
     id integer,
     some_id integer
);


ALTER TABLE fk_parent OWNER TO postgres;

--
-- Data for Name: fk_child; Type: TABLE DATA; Schema: public; Owner:
postgres
--

COPY fk_child (id, fk_id) FROM stdin;
\.


--
-- Data for Name: fk_parent; Type: TABLE DATA; Schema: public; Owner:
postgres
--

COPY fk_parent (id, some_id) FROM stdin;
\.


--
-- Name: fk_parent_some_id_key; Type: CONSTRAINT; Schema: public; Owner:
postgres
--

ALTER TABLE ONLY fk_parent
     ADD CONSTRAINT fk_parent_some_id_key UNIQUE (some_id);


--
-- Name: fk_constraint; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY fk_child
     ADD CONSTRAINT fk_constraint FOREIGN KEY (fk_id) REFERENCES
fk_parent(some_id);


>
> David J.​
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/24/2016 01:27 PM, Alexander Farber wrote:
> 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:


>
> Why aren't m.tiles and m.score returned please?

Reformatted your LEFT JOIN query(courtesy of http://sqlformat.darold.net/) :

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;

Looking at your tables I would start with something like:

SELECT
      wm.tiles, wm.score
FROM
    word_games AS wg
JOIN
    word_moves AS wm
ON
    wg.gid = wm.gid
WHERE
    (wg.player1 = 1
    OR
    wg.player2 = 1)

>
> Regards
> Alex


--
Adrian Klaver
adrian.klaver@aklaver.com