postgresql duplicate multiple rows-CodePudding

There is an app that needs to be translateable by the user (admin) through the UI (contenteditable=true). for that i created a postgres (PostgreSQL Version 13.5) database with a table wich holds the following:

|lang|key |createdby|version|value|
|----| ---| ---  | --- | --- |
|en | key.to.value |someone| 4 | some english words|
|en | key.to.value |someone| 3 | some egnlish words|
|en | key.to.value |someone| 42 | some english words|
|de | key.to.value|someone|12|some german words|
|de | key.to.value|someone|23456|some german words|

lang key and version are the primary key.

the types of each columns are text. The table also holds all older versions of an entry to be able to switch to older versions if needed.

now I have to add the possibility to create a new language wich has the highest versions data of the english rows but 'new language' (spanish, portuguese, whatever) as lang and '1' as version. The user will be able to choose "add new language" in the UI and gets the english values to start the translation.

EDIT i need to duplicate every english row with the highest version and set lang to 'whateEverLanguage' and version to version '1'

that was my last try:

insert into strings 
(lang, key, createdby, version, value) 
values 
(
(select 'esp'),
(select key from strings where lang='en' and version = (select max(version) from strings where lang='en')), 
(select createdby from strings where lang='en' and version = (select max(version) from strings where lang='en')),
(select version from strings where lang='en' and version = (select max(version) from strings where lang='en')),
(select value from strings where lang='en' and version = (select max(version) from strings where lang='en')));

i just started with databases in general about 1 week ago. I'm depressed about struggling with that so please please help me :)

CodePudding user response：

If you want to retrieve the last version of the 'en' language for every key, you need to filter the rows where lang = 'en', group the rows by key, order the grouped rows by version DESC, select the first row of the group by using an aggregate function, for instance array_agg() :

SELECT key
     , (array_agg(value ORDER BY version DESC))[1]
  FROM strings
 WHERE lang = 'en'
 GROUP BY key

If you only want the 'en' value for one specific_key only, you can filter on that key in addition :

SELECT key
     , (array_agg(value ORDER BY version DESC))[1]
  FROM strings
 WHERE key = specific_key
   AND lang = 'en'
 GROUP BY key

If you want to insert new rows, then you can do :

insert into strings (lang, key, createdby, version, value) 
select 'spanish'
     , key
     , (array_agg(createdby order by version desc))[1]
     , '1'
     , (array_agg(value order by version desc))[1]
  from strings
  where lang = 'en' 
  group by key

see the test result in dbfiddle