Good afternoon, I'm new to design! The name of the base Shop, table users. Faced with complexity, I can’t figure out what script is needed to check if the users table exists in the database, if there is no such table, I’ll create it. I’m trying to check it like this:

' $table = pg_query($db, "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='users') CREATE TABLE [users] ( id serial, fio VARCHAR, mail VARCHAR, password VARCHAR ); ");'

Tell me what am I doing wrong?

1 answer 1

if there is no such table, then create it

In this case, you don’t need to check the existence of the table at all: since the already unsupported postgresql 9.1 version of the create table you can specify the if not exists option, which is the desired behavior and provides.

 create table if not exists users (...

It will create a table if it hasn’t been like this or will not do anything.

If verification is needed for some other purpose, then the request for information_schema is quite a normal way:

 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='users'

With one really important clarification : it also checks if your user has access rights to this tablet. In the case of deploying your own application, this is usually not a problem.

Or, in fact, it is more often encountered - a request to the system catalog:

 SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'schema_name' AND c.relname = 'table_name' AND c.relkind = 'r' -- only tables

But it still has to be exactly a separate query, the branch statement must be on the application or in the stored procedure. SQL itself does not provide a declarative language and branch operator.