DEV Community

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

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
Collapse
 
janeori profile image
Jane Ori •

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

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

100% CSS GoL Demo
codepen.io/propjockey/pen/NWEYdjY?...

Collapse
 
grahamthedev profile image
GrahamTheDev •

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! 💪🏼💗

Collapse
 
cicirello profile image
Vincent A. Cicirello • • 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...

Collapse
 
mmetc profile image
mmetc • • 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;
Enter fullscreen mode Exit fullscreen mode

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

Collapse
 
grahamthedev profile image
GrahamTheDev •

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

Collapse
 
grahamthedev profile image
GrahamTheDev •

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

Collapse
 
cicirello profile image
Vincent A. Cicirello •

You're welcome

Collapse
 
techthatconnect profile image
TechThatConnect •

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.

Collapse
 
grahamthedev profile image
GrahamTheDev •

Nice, I hope to see you post it here when it is done! 💪🏼💗

Collapse
 
techthatconnect profile image
TechThatConnect •

Thats the plan!

Collapse
 
zeal2end profile image
Vivek Kumar •

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

Collapse
 
nicolello profile image
Nicola Migone •

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!

Collapse
 
grahamthedev profile image
GrahamTheDev •

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! 💗💗

Collapse
 
nicolello profile image
Nicola Migone • • 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
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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...

Collapse
 
karishmashukla profile image
Karishma Shukla •

This is cool. I am going to try this for fun

Collapse
 
grahamthedev profile image
GrahamTheDev •

Haha, let me know how you get on! 😂💗

Collapse
 
chiragagg5k profile image
Chirag Aggarwal •

Using tools to make applications it was never intended for. Funny and really interesting at the same time. Amazing post ❤️

Collapse
 
joao9aulo profile image
João Paulo Martins Silva •

Very cool and creative, congrats

Collapse
 
baptistsec profile image
William Baptist •

Impressive article, lots of detail here for people to get their teeth into. Thanks for sharing!

Collapse
 
grahamthedev profile image
GrahamTheDev •

On a scale from silly to spectacular...how do you rate this one then? 😂💗