Thread: Need another set of eyes on this

I am recreating a test database with data provided for a series of future
exercises.  Presently I am trying to understand why a simple insert statement
is not working.  The user invoking this insert is 'postgres' and has superuser
privileges.

The error is this:

postgres : STATEMENT:  INSERT INTO public.stock(
        stock_num, manu_code, description, unit_price, unit, unit_descr)
        VALUES (3, "SHM", "aluminum Bat", 180.00, "case", "12/case");
postgres : ERROR:  42703: column "SHM" does not exist at character 105
postgres : LOCATION:  errorMissingColumn, parse_relation.c:3194
postgres : STATEMENT:  INSERT INTO public.stock(
        stock_num, manu_code, description, unit_price, unit, unit_descr)
        VALUES (3, "SHM", "aluminum bat", 180.00, "case", "12/case");

The create script for this table is this:


-- Table: public.stock

-- DROP TABLE public.stock;

CREATE TABLE public.stock
(
    stock_num smallint NOT NULL,
    manu_code character(3) COLLATE pg_catalog."default",
    description character(20) COLLATE pg_catalog."default",
    unit_price money,
    unit character(4) COLLATE pg_catalog."default",
    unit_descr character(15) COLLATE pg_catalog."default"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.stock
    OWNER to hll_4gl_testing;

COMMENT ON TABLE public.stock
    IS 'The stock table is a catalog of the items sold by the distributor.';

COMMENT ON COLUMN public.stock.stock_num
    IS 'Stock number that identifies type of item';

COMMENT ON COLUMN public.stock.manu_code
    IS 'Manufacturer code (foreign key to manufact table)';

COMMENT ON COLUMN public.stock.description
    IS 'Description of item';

COMMENT ON COLUMN public.stock.unit_price
    IS 'Price per item';

COMMENT ON COLUMN public.stock.unit
    IS 'Unit by which item is ordered:
    Each
    Pair
    Case
    Box';

COMMENT ON COLUMN public.stock.unit_descr
    IS 'Description of unit';
-- Index: stock_manu_code_idx

-- DROP INDEX public.stock_manu_code_idx;

CREATE INDEX stock_manu_code_idx
    ON public.stock USING btree
    (manu_code COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: stock_stock_num_idx

-- DROP INDEX public.stock_stock_num_idx;

CREATE INDEX stock_stock_num_idx
    ON public.stock USING btree
    (stock_num ASC NULLS LAST)
    TABLESPACE pg_default;

COMMENT ON INDEX public.stock_stock_num_idx
    IS 'Index for stock numbers. Numbers may be duplicated by different
manufacturers.';
-- Index: stock_stock_num_manu_code_idx

-- DROP INDEX public.stock_stock_num_manu_code_idx;

CREATE UNIQUE INDEX stock_stock_num_manu_code_idx
    ON public.stock USING btree
    (stock_num ASC NULLS

There will be a foreign key constraint on manu_code to manufact.manu_code but
that is not yet implemented.

The reference to column "SHM" not existing has me confused.  I am probably
missing something obvious but I cannot see what the problem is.  Any help would
be most welcome.

Thanks,

-- 
***          e-Mail is NOT a SECURE channel          ***
        Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3

At 2021-01-25T14:17:42-05:00, James B. Byrne <byrnejb@harte-lyne.ca> sent:
> postgres : STATEMENT:  INSERT INTO public.stock(
>         stock_num, manu_code, description, unit_price, unit, unit_descr)
>         VALUES (3, "SHM", "aluminum Bat", 180.00, "case", "12/case");
> postgres : ERROR:  42703: column "SHM" does not exist at character 105
> postgres : LOCATION:  errorMissingColumn, parse_relation.c:3194

You're using double-quotes rather than single-quotes for string values.

"SHM" = column
'SHM' = string value

> On 25 Jan 2021, at 20:17, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
>
> I am recreating a test database with data provided for a series of future
> exercises.  Presently I am trying to understand why a simple insert statement
> is not working.  The user invoking this insert is 'postgres' and has superuser
> privileges.
>
> The error is this:
>
> postgres : STATEMENT:  INSERT INTO public.stock(
>         stock_num, manu_code, description, unit_price, unit, unit_descr)
>         VALUES (3, "SHM", "aluminum Bat", 180.00, "case", "12/case");
> postgres : ERROR:  42703: column "SHM" does not exist at character 105
> postgres : LOCATION:  errorMissingColumn, parse_relation.c:3194
> postgres : STATEMENT:  INSERT INTO public.stock(
>         stock_num, manu_code, description, unit_price, unit, unit_descr)
>         VALUES (3, "SHM", "aluminum bat", 180.00, "case", "12/case”)

> ;

Try single quotes

On Mon, January 25, 2021 14:19, aNullValue (Drew Stemen) wrote:

>
> You're using double-quotes rather than single-quotes for string values.
>
> "SHM" = column
> 'SHM' = string value
>


On Mon, January 25, 2021 14:19, Peter Coppens wrote:
>
> Try single quotes
>

Thank you both.

-- 
***          e-Mail is NOT a SECURE channel          ***
        Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3