i can't add column from query to table as new column (postgresql)

volinader 0
Viewed 25

I selected only digit from column and want to add this new column to my table with selected digits

SELECT
 left(orgi.dt_new, strpos(dt, ' ') - 1) AS day_
FROM
    (SELECT *,
        CASE
         WHEN dt !~ '^[0-9]'
         THEN right(dt, strpos(dt, ',') + 12)
         ELSE dt
         END AS dt_new
    FROM "CUSTOM".org) AS orgi

final query

After that i create one more column for this query:

ALTER TABLE "CUSTOM".org 
ADD COLUMN date_day text ;

my table with empty column

I want to add this query to table, i tried INSERT INTO but it's starting to add my query from last row with context, i tried UPDATE table_name SET column_name, but it doesn't work

INSERT INTO "CUSTOM".org (date_day) 
SELECT
 left(orgi.dt_new, strpos(dt, ' ') - 1) AS day_
FROM
    (SELECT *,
        CASE
        WHEN dt !~ '^[0-9]'
        THEN right(dt, strpos(dt, ',') + 12)
        ELSE
        dt
        END AS dt_new
    FROM "CUSTOM".org) AS orgi

result of INSERT

UPDATE "CUSTOM".org SET date_day = 
(SELECT
 left(orgi.dt_new, strpos(dt, ' ') - 1) AS day_
FROM
    (SELECT *,
        CASE
        WHEN dt !~ '^[0-9]'
        THEN right(dt, strpos(dt, ',') + 12)
        ELSE
        dt
        END AS dt_new
    FROM "CUSTOM".org) AS orgi )


ERROR: subquery returns more than 1 row
insert-update sql postgresql
1 Answers

I found the way to solve my problem, maybe it will help someone

UPDATE "CUSTOM".org SET date_day = subquery.day_
FROM
    (SELECT
     "id",
     left(orgi.dt_new, strpos(dt, ' ') - 1) AS day_
    FROM
        (SELECT *,
            CASE
            WHEN dt !~ '^[0-9]'
            THEN right(dt, strpos(dt, ',') + 12)
            ELSE
            dt
            END AS dt_new
        FROM "CUSTOM".org) AS orgi) AS subquery
WHERE "CUSTOM".org.id = subquery.id
volinader0
Related