Search
Search

INSERT based on a SELECT not inserting correctly with PostGreSQL

debugcn Published at Dev
10
user2474622

I'm using PostGreSQL 9.3 and the latest PostGIS to store shapefiles.

I have a table, named zonages_region, that contains 76 polygons.
I added a new column in order to store the superficy of the polygons.
The geometry of the polygons is stored in the "geom" column.

I have this request :

INSERT INTO zonages_region(superficie)
SELECT ST_AREA(geom::geography)/1000000 FROM zonages_region

The SELECT is stored in the table, but it creates new rows instead of starting at the first one.
As I have already 76 rows for my polygons, the INSERT starts to insert at the row 77.

How can I do the INSERT to start at the first row, so it will match the polygons rows already existing ?

Erwin Brandstetter

That should be an UPDATE. Like this:

UPDATE zonages_region
SET    superficie = ST_AREA(geom::geography)/1000000

Without WHERE condition, every row in the table is updated.
Details about UPDATE in the manual.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

PostgreSQL INSERT based on SELECT results using Python

From Dev

MySQL INSERT SELECT not Inserting

From Dev

Binary Search/Insert Methods not inserting correctly

From Dev

postgresql insert into from select

From Dev

Inserting values based on results of another insert

From Dev

SQL - Inserting loop based on select rows

From Dev

Insert into … values ( SELECT … FROM … ) in postgresql?

From Dev

Postgresql: INSERT INTO using SELECT and values

From Dev

postgresql: INSERT INTO … (SELECT * …,"fixed value")

From Dev

Postgresql Insert select with multiple rows

From Dev

PostgreSQL - insert rows based on select from another table, and update an FK in that table with the newly inserted rows

From Dev

SELECT LAST_INSERT_ID() not generating correctly

From Dev

Insert into Select does not insert the same data order correctly

From Dev

Insert into Select does not insert the same data order correctly

From Dev

PostgreSql INSERT FROM SELECT RETURNING ID

From Dev

Insert from Select Postgresql with primary key constraint

From Dev

postgresql insert values from a select query

From Dev

PostgreSQL INSERT FROM SELECT with additional column

From Dev

PostgreSQL insert select multiple column values

From Dev

How to include the values of a select statement in an insert? (PostgreSQL)

From Dev

INSERT INTO MySQL table SELECT FROM PostgreSQL table

From Dev

PostgreSQL INSERT FROM SELECT with additional column

From Dev

PostgreSQL; Using a SELECT sub-query in an INSERT

From Dev

PostgreSQL: How to insert, select, and update values with underscores

From Dev

Postgresql - Insert when select return something

From Dev

Android - Firebase not inserting correctly

From Dev

INSERT multiple rows based on SELECT statement

From Dev

PostgreSQL Select rows based on combination of array values

From Dev

PostgreSQL Select rows based on combination of array values

Related Related

  1. 1

    PostgreSQL INSERT based on SELECT results using Python

  2. 2

    MySQL INSERT SELECT not Inserting

  3. 3

    Binary Search/Insert Methods not inserting correctly

  4. 4

    postgresql insert into from select

  5. 5

    Inserting values based on results of another insert

  6. 6

    SQL - Inserting loop based on select rows

  7. 7

    Insert into … values ( SELECT … FROM … ) in postgresql?

  8. 8

    Postgresql: INSERT INTO using SELECT and values

  9. 9

    postgresql: INSERT INTO … (SELECT * …,"fixed value")

  10. 10

    Postgresql Insert select with multiple rows

  11. 11

    PostgreSQL - insert rows based on select from another table, and update an FK in that table with the newly inserted rows

  12. 12

    SELECT LAST_INSERT_ID() not generating correctly

  13. 13

    Insert into Select does not insert the same data order correctly

  14. 14

    Insert into Select does not insert the same data order correctly

  15. 15

    PostgreSql INSERT FROM SELECT RETURNING ID

  16. 16

    Insert from Select Postgresql with primary key constraint

  17. 17

    postgresql insert values from a select query

  18. 18

    PostgreSQL INSERT FROM SELECT with additional column

  19. 19

    PostgreSQL insert select multiple column values

  20. 20

    How to include the values of a select statement in an insert? (PostgreSQL)

  21. 21

    INSERT INTO MySQL table SELECT FROM PostgreSQL table

  22. 22

    PostgreSQL INSERT FROM SELECT with additional column

  23. 23

    PostgreSQL; Using a SELECT sub-query in an INSERT

  24. 24

    PostgreSQL: How to insert, select, and update values with underscores

  25. 25

    Postgresql - Insert when select return something

  26. 26

    Android - Firebase not inserting correctly

  27. 27

    INSERT multiple rows based on SELECT statement

  28. 28

    PostgreSQL Select rows based on combination of array values

  29. 29

    PostgreSQL Select rows based on combination of array values

HotTag

Archive