Create PostgreSQL Database with case-sensitive name using npgsql

Question

I'm trying to create a PostgreSQL database using the npgsql library, but the database name always ends up in lower case. Is it possible to preserve the case or should I just lower case everything? I want the database name to be MyDatabase, but it always ends up as mydatabase.

string server = "localhost";
string database = "MyDatabase";
string connectionString = @"Server=" + server + ";User Id=postgres";
var connection = new NpgsqlConnection(connectionString);
string commandText = "CREATE DATABASE " + database;
var command = new NpgsqlCommand(commandText);
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();

You need to quote the DB name.

Milen A. Radev
– Milen A. Radev

2014-02-16 08:06:49 +00:00
Commented Feb 16, 2014 at 8:06 — Milen A. Radev
– Milen A. Radev, Commented Feb 16, 2014 at 8:06

Christopher Tso · Accepted Answer · 2014-02-16 09:04:18Z

3

Quote the DB name as below:

string commandText = string.Format("CREATE DATABASE \"{0}\"", database);

answered Feb 16, 2014 at 9:04

Christopher Tso

3397 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AuthorProxy · Accepted Answer · 2018-03-26 10:10:14Z

Not the answer, but it works for EF Core 2.0

Current code convert tables, properties, keys and indexes to snake case for Postgre, you can use it as a base for your custom conventions:

using System;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Npgsql;

namespace Database.Customization
{
    public class PostgreDbContext : DbContext
    {
        private static readonly Regex _keysRegex = new Regex("^(PK|FK|IX)_", RegexOptions.Compiled);

        public PostgreDbContext(DbContextOptions options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            FixSnakeCaseNames(modelBuilder);
        }

        private void FixSnakeCaseNames(ModelBuilder modelBuilder)
        {
            var mapper = new NpgsqlSnakeCaseNameTranslator();
            foreach (var table in modelBuilder.Model.GetEntityTypes())
            {
                ConvertToSnake(mapper, table);
                foreach (var property in table.GetProperties())
                {
                    ConvertToSnake(mapper, property);
                }

                foreach (var primaryKey in table.GetKeys())
                {
                    ConvertToSnake(mapper, primaryKey);
                }

                foreach (var foreignKey in table.GetForeignKeys())
                {
                    ConvertToSnake(mapper, foreignKey);
                }

                foreach (var indexKey in table.GetIndexes())
                {
                    ConvertToSnake(mapper, indexKey);
                }
            }
        }

        private void ConvertToSnake(INpgsqlNameTranslator mapper, object entity)
        {
            switch (entity)
            {
                case IMutableEntityType table:
                    var relationalTable = table.Relational();
                    relationalTable.TableName = ConvertGeneralToSnake(mapper, relationalTable.TableName);
                    if (relationalTable.TableName.StartsWith("asp_net_"))
                    {
                        relationalTable.TableName = relationalTable.TableName.Replace("asp_net_", string.Empty);
                        relationalTable.Schema = "identity";
                    }

                    break;
                case IMutableProperty property:
                    property.Relational().ColumnName = ConvertGeneralToSnake(mapper, property.Relational().ColumnName);
                    break;
                case IMutableKey primaryKey:
                    primaryKey.Relational().Name = ConvertKeyToSnake(mapper, primaryKey.Relational().Name);
                    break;
                case IMutableForeignKey foreignKey:
                    foreignKey.Relational().Name = ConvertKeyToSnake(mapper, foreignKey.Relational().Name);
                    break;
                case IMutableIndex indexKey:
                    indexKey.Relational().Name = ConvertKeyToSnake(mapper, indexKey.Relational().Name);
                    break;
                default:
                    throw new NotImplementedException("Unexpected type was provided to snake case converter");
            }
        }

        private string ConvertKeyToSnake(INpgsqlNameTranslator mapper, string keyName) =>
            ConvertGeneralToSnake(mapper, _keysRegex.Replace(keyName, match => match.Value.ToLower()));

        private string ConvertGeneralToSnake(INpgsqlNameTranslator mapper, string entityName) =>
            mapper.TranslateMemberName(ModifyNameBeforeConvertion(mapper, entityName));

        protected virtual string ModifyNameBeforeConvertion(INpgsqlNameTranslator mapper, string entityName) => entityName;
    }
}

Collectives™ on Stack Overflow

Create PostgreSQL Database with case-sensitive name using npgsql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related