Question 227424 in PostgreSQL Question posted in
The official documentation can be found here.

Insert elements postgresql with maximum row

I am trying to create a insert script with postgres,

To insert into a table a maximum 1 element per person,

For example, I got this query down, but I would like to create a logic that if I already inserted a file to a person, the next file should go to the next person of that country

I’m so stucked with this situation, I’ve never seen something like this before, a script that insert just one row and then inserts to the next "person".

What is happening now is that I’m inserting 3 files to a person, and the other person from the same country doenst receive a file.

WITH get_files AS (
    SELECT
        type,
        country
    FROM files
    WHERE title IS NOT NULL
)
INSERT INTO history_person_file (
    name,
    document_type,
    age
)
SELECT
    p.name,
    f.type,
    p.age
FROM person p
LEFT JOIN get_files f
ON p.country = f.country
ON conflict (name) do nothing;
Tags:

2

Answers


  1. 0

    Not sure but maybe this will work

    WITH RankedPersons AS (
SELECT
    p.name,
    p.age,
    p.country,
    ROW_NUMBER() OVER (PARTITION BY p.country ORDER BY f.title IS NULL) AS 
row_num
FROM person p
LEFT JOIN files f ON p.country = f.country AND f.title IS NOT NULL
),
NextAvailableFile AS (
SELECT
    f.type,
    f.country
FROM files f
LEFT JOIN RankedPersons p ON f.country = p.country AND p.row_num = 1
WHERE p.row_num IS NULL
LIMIT 1
)
INSERT INTO person_file (name, document_type, age)
SELECT
p.name,
COALESCE(naf.type, 'default_document_type'),
p.age
FROM RankedPersons p
LEFT JOIN NextAvailableFile naf ON p.country = naf.country
WHERE p.row_num = 1
ON CONFLICT (name) DO NOTHING;
    Login or Signup to reply.
  2. 0

    Perform the restriction logic in your query. Once you have that right, insert the result into your history table:

    with num_docs as (
 select type, country, 
        row_number() over (partition by country 
                               order by title) as rn
   from files
  where title is not null
), num_person as (
 select name, age, country,
        row_number() over (partition by country
                               order by name) as rn
   from person
)
select p.country, p.name, d.country, d.type, d.age
  from num_docs d
       full join num_person p 
         on (p.country, p.rn) = (d.country, d.rn);

    I used a full join here so you can review the results before loading to your history table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search