Add a new table column with data extracted from existing column

Question

To preface, I'm using PostGIS and pgAdmin4 to do all of this work.

So I've imported a set of rasters into a database with the "rid", "raster" and "filename" columns. I used this query to add the column, where I just as easily could have used the GUI in pgAdmin to achieve the same.

ALTER TABLE IF EXISTS ch12.prec
ADD COLUMN "Month" integer;

I need to extract the month from the filename and insert it into this new column but am at a loss on how to do so. The file name structure is "prec1_16.bil", where the 1 is the month and 16 the year. I tried using an INSERT statement with a CASE expression like:

INSERT INTO ch12.prec (Month)
SELECT
 CASE
    WHEN filename = 'prec1_16.bil' OR filename = 'prec1_17.bil' THEN '1'
    WHEN filename = 'prec2_16.bil' OR filename = 'prec2_17.bil' THEN '2'
    WHEN filename = 'prec3_16.bil' OR filename = 'prec3_17.bil' THEN '3'
    WHEN filename = 'prec4_16.bil' OR filename = 'prec4_17.bil' THEN '4'
    WHEN filename = 'prec5_16.bil' OR filename = 'prec5_17.bil' THEN '5'
    WHEN filename = 'prec6_16.bil' OR filename = 'prec6_17.bil' THEN '6'
    WHEN filename = 'prec7_16.bil' OR filename = 'prec7_17.bil' THEN '7'
    WHEN filename = 'prec8_16.bil' OR filename = 'prec8_17.bil' THEN '8'
    WHEN filename = 'prec9_16.bil' OR filename = 'prec9_17.bil' THEN '9'
    WHEN filename = 'prec10_16.bil' OR filename = 'prec10_17.bil' THEN '10'
    WHEN filename = 'prec11_16.bil' OR filename = 'prec11_17.bil' THEN '11'
    WHEN filename = 'prec12_16.bil' OR filename = 'prec12_17.bil' THEN '12'
 END filename
FROM ch12.prec;

All I get is a column doesn't exist error, despite being able to see it and query it. Any thoughts? Would something like UPDATE be more appropriate?

Not an answer, but if the `Month` column is integer type, then your `CASE` expression should be producing integers, not strings. Make that change, and then go from there. — Tim Biegeleisen, May 04 '22 at 02:13

Erwin Brandstetter · Answer 1 · 2022-05-04T04:44:38.877

Here you double-quoted the column name "Month", thereby preserving mixed-case spelling:

ALTER TABLE IF EXISTS ch12.prec
ADD COLUMN "Month" integer;

Here you didn't, thereby lower-casing the identifier:

INSERT INTO ch12.prec (Month) ...

See:

Are PostgreSQL column names case-sensitive?

And yes, you are looking for UPDATE, not INSERT:

UPDATE ch12.prec
SET   "Month" = substring(filename, '^prec(1?\d)_1[67].bil$')::int;

Replacing your lengthy CASE expression with a shorter regular expression (100 % equivalent). And making it an actual integer.

Doesn't mean I would do that. I don't use mixed-case identifiers if I can avoid it. And I don't add columns with redundant information (except for special cases).
Consider not adding a column and operating with the expression I gave you instead - or possibly an even simpler one if assumptions can be made.

Add a new table column with data extracted from existing column

1 Answers1