How to fix 42P15: invalid_schema_definition in PostgreSQL

-- ❌ Invalid - uses reserved prefix
CREATE SCHEMA pg_myschema;

-- ✅ Valid - uses non-reserved name
CREATE SCHEMA myschema;

CREATE SCHEMA app_myschema;
CREATE SCHEMA data_myschema;

psql --version
# or within psql:
SELECT version();

CREATE SCHEMA IF NOT EXISTS myschema;

-- Check if schema exists first
SELECT schema_name FROM information_schema.schemata
WHERE schema_name = 'myschema';

-- Or use DO block (9.0+)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.schemata
        WHERE schema_name = 'myschema'
    ) THEN
        CREATE SCHEMA myschema;
    END IF;
END
$$;

-- ❌ Invalid - includes unsupported statements
CREATE SCHEMA myschema
  CREATE FUNCTION my_func() RETURNS void AS $$ ... $$  -- Not allowed
  CREATE TABLE users (id serial);

-- ✅ Valid - only allowed statements
CREATE SCHEMA myschema
  CREATE TABLE users (
    id serial PRIMARY KEY,
    name varchar(100)
  )
  CREATE VIEW active_users AS
    SELECT * FROM myschema.users WHERE active = true
  GRANT SELECT ON myschema.users TO readonly_role;

CREATE SCHEMA myschema;
CREATE FUNCTION myschema.my_func() RETURNS void AS $$ ... $$ LANGUAGE sql;

-- ❌ Invalid - cannot use parameter for identifier
PREPARE create_schema (text) AS CREATE SCHEMA $1;

-- ✅ Valid - build the SQL string dynamically if needed
DO $$
DECLARE
  schema_name text := 'myschema';
BEGIN
  EXECUTE format('CREATE SCHEMA %I', schema_name);
END
$$;

// Node.js with pg library
const schemaName = 'myschema';
await client.query(`CREATE SCHEMA ${schemaName}`); // Safe if you control the name

// Better: use format with identifier quoting
await client.query('SELECT format($1, $2)', ['CREATE SCHEMA %I', schemaName]);

-- ❌ Invalid - special characters without quotes
CREATE SCHEMA my-schema;
CREATE SCHEMA My Schema;

-- ✅ Valid - properly quoted
CREATE SCHEMA "my-schema";
CREATE SCHEMA "My Schema";
CREATE SCHEMA "MixedCase";

-- Recommended approach
CREATE SCHEMA my_schema;
CREATE SCHEMA data_warehouse;

-- Check if role exists
SELECT rolname FROM pg_roles WHERE rolname = 'myuser';

-- ✅ Valid AUTHORIZATION syntax
CREATE SCHEMA myschema AUTHORIZATION myuser;
CREATE SCHEMA AUTHORIZATION myuser;  -- Schema named after user
CREATE SCHEMA IF NOT EXISTS myschema AUTHORIZATION myuser;

-- ❌ Invalid - malformed syntax
CREATE SCHEMA myschema FOR myuser;  -- Wrong keyword

CREATE ROLE myuser LOGIN PASSWORD 'secure_password';
CREATE SCHEMA myschema AUTHORIZATION myuser;