DEV Community

Conway's game of life...in pure SQL [Postgres]! 💪🏼🤯

GrahamTheDev on August 27, 2023

OK, for those of you who are new here, I like to do things that have never been done (and should never be done again!)! The stupid idea I had this...

Read full post

Jane Ori • Aug 28 '23

Oooh! Kindred soul making GoL in unexpected languages! This was fun to read!

I did it in 100% CSS not long ago :)

codepen.io/propjockey/pen/NWEYdjY?...

GrahamTheDev • Aug 28 '23

I saw this and loved it...in fact I was originally going to do it in CSS until I saw this, and so I chose SQL instead!

Amazing work! 💪🏼💗

Vincent A. Cicirello • Aug 27 '23 • Edited

Very cool. If you really want to drive yourself nuts with this, try doing the same without using PL/pgSQL. Using common table expressions, you can use recursion, which makes PostgresSQL a Turing Complete language without PL/pgSQL.

Example demonstrating Turing Completeness: wiki.postgresql.org/wiki/Cyclic_Ta...

Related Stackoverflow answer that happens to include a link to the Mandelbrot set in PostgresSQL: stackoverflow.com/a/7580013

Direct link to the Mandelbrot example: wiki.postgresql.org/wiki/Mandelbro...

mmetc • Aug 28 '23 • Edited

This reminded me of something I wrote 12 years go. It should still work, no procedural code. I went and found the source:

-- schema.sql

DROP TABLE IF EXISTS cells;

CREATE TABLE cells (
    x INTEGER,
    y INTEGER,
    state BOOLEAN NOT NULL,
    PRIMARY KEY (x, y)
);

DROP AGGREGATE IF EXISTS concat (text);

CREATE AGGREGATE concat (
    BASETYPE = text,
    SFUNC = textcat,
    STYPE = text,
    INITCOND = ''
);

-- soup.sql

DELETE FROM cells;

INSERT INTO cells
     SELECT x, y, random()<.375
       FROM generate_series(0, 20) x
 CROSS JOIN generate_series(0, 20) y;

-- evolve.sql

UPDATE cells
   SET state = (
                 state,
                 (SELECT SUM(state::INT)
                    FROM cells nb
                   WHERE (ABS(nb.x-cells.x), ABS(nb.y-cells.y)) IN ((0, 1), (1, 0), (1, 1)))
               ) IN ((true, 2), (true, 3), (false, 3));

-- watch.sql

SELECT y, concat(CASE WHEN state THEN 'O' ELSE '.' END)
  FROM (SELECT * FROM cells ORDER BY x) foo
 GROUP BY y
 ORDER BY y;

These days I don't do much SQL anymore, but I used to treat every query as a puzzle :)

GrahamTheDev • Aug 28 '23

Nice, few tricks in there for me to learn from! 💗

GrahamTheDev • Aug 27 '23

Ooohhh, perhaps not this, but I can think of some fun things to try using Recursion! Thanks! 💗

Vincent A. Cicirello • Aug 27 '23

You're welcome

Vivek Kumar • Aug 28 '23

It's So Cool, I have also done something similar unique I've created the Convoy's game of life in command line. Medium

Nicola Migone • Aug 29 '23

Just FYI, PostgreSQL supports points as datatypes, which might be more efficient for this :) Read more on their documentation at here

This aside, interesting article. Great job!

GrahamTheDev • Aug 29 '23

Do you have any examples of usage as just by definition alone I am not seeing how points vs 2 columns for x and y save any operations.

The inefficiency in my code is from loops and lots of UPDATES and my WHERE query? Is there any specific "function" that I can use (like SERIES) with these.

As I said, just learning so I am far from understanding all of the parts!

Thanks in advance! 💗💗

Nicola Migone • Aug 29 '23 • Edited

I modified the schema a bit, assuming that all points in the table are assumed to be alive, and if a point isn't there, then it is dead.
New schema:

CREATE TABLE points (
    id SERIAL PRIMARY KEY, 
    p POINT NOT NULL
);

and now the power of it.. To get all adjacent points that are alive, I just need to run this:

SELECT p FROM points WHERE p <-> POINT '(0, 0)' = 1

the <-> operator returns the distance of two geometric objects, points in this case.
You can probably figure out how to go on :)

Relevant documentation: postgresql.org/docs/current/functi...

TechThatConnect • Aug 27 '23

Its very nice to see others learning this way. I love to come up with silly ideas and then learn by implimenting them. I am currently making a turn based castle builder game that runs in a browser using pure vanilla js.