Hey everyone, I need some help. I'm quite new to SQL and while I get the gist of SELECT statements and stuff I'm trying to bring things all together. I want to create a table it it hasn't already been created, but the answers I'm getting online are a little confusing. This is advised: https://stackoverflow.com/questions/5952006/how-to-check-if-table-exist-and-if-it-doesnt-exist-create-table-in-sql-server-2

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[YourTable](
    ....
    ....
    ....
) 

END

@ashley no this will not work with postgres

ah so when I search for answers I can't just look at SQL responses it needs to be postgres responses?

what I do is do it with pgadmin, inspect how it does things, then apply to my code

also you don't ever want to be in a position wher eyou don't know if a table exists or not

hmm, so how would you go about the automation of setting up a server? I want to basically say 'when you boot up, use this table. If this is your first time, create it' kind of thing

i guess you will need this for the very first table is you're using a variables table, though postgres has database variables for this iirc

if you create a new database, you know it's empty

if it exists, you already have a database version somewhere in there because you added it directly

I have docker create the database for me, but obviously when the server restarts the database still exists

I was initially going to have docker create the tables and stuff too but I wasn't sure about how to go about that so thought about doing it on the server anyway

in your database creation step you set it up so that you directly have a database version, so you know the state of the database

yeah that would make sense, then the database is ready to use by my api straight away

that's how i do it, i guess there are other means, but i never had to blindly create a table

I need to learn how to do that I guess

i have a startup backup of a database i created with pgadmin

i create the database using it

in there is a variables table with a db_version

then i have some code to bring it to current version

I've got a lot of research to do, thank you

I need to try and get pgadmin working first and foremost

I've been using shell so far

you can use psql for command line and scripting

@ashley Pretty much all databases support CREATE my_table IF NOT EXISTS ( ... ) so you can run that at startup if you want to conditionally create tables.

If you need to build a database schema up over time, you'll want to look at libraries like Migratus (for SQL migrations to apply changes to the database schema).

i'm a proponent of do it yourself, get the exact result you want

@seancorfield I got it working 🙂 I discovered the entrypoint folder

so I'm currently writing an sql file called init.sql that I'll use to set up my tables and initial data