PostgreSQL INSERT from SELECT limit

Asked Dec 14 '18 at 08:41

Active Dec 14 '18 at 08:50

Viewed 1,237 times

I have a INSERT query and expected 400k rows to be inserted.

But after execution I have 257k inserted.

My expectation from this query

SELECT count(*) FROM daily_checkups WHERE pig_migration_count = 0

result is 400k

Can somebody explain me, why after insert I have less rows?

insert(<<-SQL.squish)
  INSERT INTO pig_migrations (daily_checkup_id, created_at, updated_at, migration_type, amount)
  (
    SELECT id, created_at, updated_at, 4 as migration_type, 0 as amount
    FROM daily_checkups WHERE pig_migration_count = 0
  )
SQL

edited Dec 14 '18 at 08:50

asked Dec 14 '18 at 08:41

a.kozubenko

1,030
3
15
24

I wrote. Result - 400k – a.kozubenko Dec 14 '18 at 08:47
How many rows did pig_migrations have before the INSERT? – jarlh Dec 14 '18 at 08:51
19444, but I think it is not important – a.kozubenko Dec 14 '18 at 08:56
1

Is there any triggers on pig_migrations table? – Zeki Gumus Dec 14 '18 at 08:59
What do you mean? – a.kozubenko Dec 14 '18 at 09:00
Do you get any error mesages while exection the SQL like dupplicate key? – Jens Dec 14 '18 at 09:04
It would be best if you show the definition of the daily_checkups and pig_migrations tables (columns, constraints) – Dimitar Spasovski Dec 14 '18 at 09:12
1

The most likely cause is a trigger that rejects some of the rows. [How to check if trigger exists in PostgreSQL?](https://stackoverflow.com/a/33174794/1995738) – klin Dec 14 '18 at 09:27
1

Did you solved this issue? I would recommend finding one insert statement that does not get inserted. Than check the values against existing table and check the output messages when this insert is run... If nothing is shown and the row is not inserted go to DB logs and check it up... – Honza P. Dec 19 '18 at 09:54

PostgreSQL INSERT from SELECT limit

0 Answers0