[BUG]: Inserts do not work, using the example with postgres.

[jonasfroeller]

orange Version

4.3.0

Database

Postgres

Database Version

16.2

Operating System

Windows 11

Description

Postgres tables and columns are automatically stored in lowercase if they are not surrounded by quotes.

ERROR  [nuxt] [request error] [unhandled] [500] column "isActive" of relation "customer" does not exist

Expected Behavior

I am not sure, because if you made it case insensitive it would break CREATE STATEMENTs where quotes are applied.

[lroal]

Are you using the sql example in the docs ? That is sqlite - not Postgres. You need to quote all columns and tables in the create schema.

[lroal]

I guess I could add sql for Postgres in the docs as well

[lroal]

Try this , you can find more examples in the tests https://github.com/alfateam/orange-orm/edit/master/tests/initPg.js

CREATE TABLE customer (
    id SERIAL    PRIMARY KEY,
    name TEXT,
    balance NUMERIC,
    "isActive" BOOLEAN,
);

CREATE TABLE "_order" (
    id SERIAL PRIMARY KEY,
    "orderDate" TIMESTAMP,
    "customerId" INTEGER REFERENCES customer
);

CREATE TABLE "orderLine" (
    id SERIAL PRIMARY KEY,
    "orderId" INTEGER REFERENCES "_order",
    product TEXT,
    amount NUMERIC(10,2) NULL
);

CREATE TABLE package (
    "packageId" SERIAL PRIMARY KEY,
    "lineId" INTEGER REFERENCES "orderLine",
    sscc TEXT
);

CREATE TABLE "deliveryAddress" (
    id SERIAL PRIMARY KEY,
    "orderId" INTEGER REFERENCES "_order",
    name TEXT, 
    street TEXT,
    "postalCode" TEXT,
    "postalPlace" TEXT,
    "countryCode" TEXT
)

[jonasfroeller]

Try this , you can find more examples in the tests https://github.com/alfateam/orange-orm/edit/master/tests/initPg.js

CREATE TABLE customer (
    id SERIAL  PRIMARY KEY,
    name TEXT,
    balance NUMERIC,
    "isActive" BOOLEAN,
);

CREATE TABLE "_order" (
    id SERIAL PRIMARY KEY,
    "orderDate" TIMESTAMP,
    "customerId" INTEGER REFERENCES customer
);

CREATE TABLE "orderLine" (
    id SERIAL PRIMARY KEY,
    "orderId" INTEGER REFERENCES "_order",
    product TEXT,
    amount NUMERIC(10,2) NULL
);

CREATE TABLE package (
    "packageId" SERIAL PRIMARY KEY,
    "lineId" INTEGER REFERENCES "orderLine",
    sscc TEXT
);

CREATE TABLE "deliveryAddress" (
    id SERIAL PRIMARY KEY,
    "orderId" INTEGER REFERENCES "_order",
    name TEXT, 
    street TEXT,
    "postalCode" TEXT,
    "postalPlace" TEXT,
    "countryCode" TEXT
)

Thank you for your quick responsese! I know that the SQL was for sqlite. I updated it to fit the postgres dialect and ended up with the same statements (almost). Just forgot to also add quotes to the table names, because I normally do not name tables in camelCase (I normally use SCREAMING_SNAKE_CASE for everything in databases, so that there are no case issues). Massive skill issue on my side. Sorry for stealing your time like that. I wish orange-orm would let me define the actual tables created in the database and not just a type map. That way, I wouldn't have had that issue.

[lroal]

No problem. I am glad to help. Yes, I will create cli command for generating sql from the map. Also, the other way around.