In postgres how to find the shortest string an array
I need to find the shortest string in postgres. Searched around could not find any answers. My use case started from a aggregate function in SQL:
select key_col, strarr_shortest(text_col) as text_col
from (
select key_col, array_agg(tex_col::text) as text_col
from mytable group by key_col
) foo;
It turn out I have to write a pl/pgsql to solve this problem.
sql postgresql
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
add a comment |
I need to find the shortest string in postgres. Searched around could not find any answers. My use case started from a aggregate function in SQL:
select key_col, strarr_shortest(text_col) as text_col
from (
select key_col, array_agg(tex_col::text) as text_col
from mytable group by key_col
) foo;
It turn out I have to write a pl/pgsql to solve this problem.
sql postgresql
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
Does the inner part of the query be like that (e.g. because you cannot control that) are can the whole query be changed?
– sticky bit
Jan 19 at 18:39
add a comment |
I need to find the shortest string in postgres. Searched around could not find any answers. My use case started from a aggregate function in SQL:
select key_col, strarr_shortest(text_col) as text_col
from (
select key_col, array_agg(tex_col::text) as text_col
from mytable group by key_col
) foo;
It turn out I have to write a pl/pgsql to solve this problem.
sql postgresql
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
I need to find the shortest string in postgres. Searched around could not find any answers. My use case started from a aggregate function in SQL:
select key_col, strarr_shortest(text_col) as text_col
from (
select key_col, array_agg(tex_col::text) as text_col
from mytable group by key_col
) foo;
It turn out I have to write a pl/pgsql to solve this problem.
sql postgresql
sql postgresql
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
edited Jan 19 at 18:43
a_horse_with_no_name
296k46451546
296k46451546
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
asked Jan 19 at 18:20
Kemin ZhouKemin Zhou
2,0411730
2,0411730
Does the inner part of the query be like that (e.g. because you cannot control that) are can the whole query be changed?
– sticky bit
Jan 19 at 18:39
add a comment |
Does the inner part of the query be like that (e.g. because you cannot control that) are can the whole query be changed?
– sticky bit
Jan 19 at 18:39
Does the inner part of the query be like that (e.g. because you cannot control that) are can the whole query be changed?
– sticky bit
Jan 19 at 18:39
Does the inner part of the query be like that (e.g. because you cannot control that) are can the whole query be changed?
– sticky bit
Jan 19 at 18:39
add a comment |
2 Answers
2
active
oldest
votes
There is no need to aggregate the rows and search in arrays. Use distinct on, example:
with mytable (key_col, text_col) as (
values
(1, 'asd'),
(1, 'a'),
(2, 'asd'),
(2, 'asdfg')
)
select distinct on (key_col) key_col, text_col as shortest
from mytable
order by key_col, length(text_col)
key_col | shortest
---------+----------
1 | a
2 | asd
(2 rows)
If you really need the function (to use in other circumstances), it may be a simple query wrapped in an SQL function:
create or replace function shortest(text)
returns text language sql as $$
select elem
from unnest($1) as elem
order by length(elem)
limit 1;
$$;
answered Jan 19 at 18:49
klinklin
58.3k65181
No need to add a function is a plus for this solution.
– Kemin Zhou
Jan 19 at 20:43
add a comment |
My solution is to create a new function; not sure this is the best answer or not. There might be solution in pure SQL.
CREATE OR REPLACE FUNCTION strarr_shortest(arr text) RETURNS text AS $$
DECLARE
res text;
minlen int := 2147483647; -- largest singed int in postgres
el text;
BEGIN
FOREACH el IN ARRAY arr
LOOP
if char_length(el) < minlen then
res=el;
minlen=char_length(el);
end if;
END LOOP;
RETURN res;
END;
$$ LANGUAGE plpgsql;
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54270061%2fin-postgres-how-to-find-the-shortest-string-an-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
There is no need to aggregate the rows and search in arrays. Use distinct on, example:
with mytable (key_col, text_col) as (
values
(1, 'asd'),
(1, 'a'),
(2, 'asd'),
(2, 'asdfg')
)
select distinct on (key_col) key_col, text_col as shortest
from mytable
order by key_col, length(text_col)
key_col | shortest
---------+----------
1 | a
2 | asd
(2 rows)
If you really need the function (to use in other circumstances), it may be a simple query wrapped in an SQL function:
create or replace function shortest(text)
returns text language sql as $$
select elem
from unnest($1) as elem
order by length(elem)
limit 1;
$$;
answered Jan 19 at 18:49
klinklin
58.3k65181
No need to add a function is a plus for this solution.
– Kemin Zhou
Jan 19 at 20:43
add a comment |
There is no need to aggregate the rows and search in arrays. Use distinct on, example:
with mytable (key_col, text_col) as (
values
(1, 'asd'),
(1, 'a'),
(2, 'asd'),
(2, 'asdfg')
)
select distinct on (key_col) key_col, text_col as shortest
from mytable
order by key_col, length(text_col)
key_col | shortest
---------+----------
1 | a
2 | asd
(2 rows)
If you really need the function (to use in other circumstances), it may be a simple query wrapped in an SQL function:
create or replace function shortest(text)
returns text language sql as $$
select elem
from unnest($1) as elem
order by length(elem)
limit 1;
$$;
answered Jan 19 at 18:49
klinklin
58.3k65181
No need to add a function is a plus for this solution.
– Kemin Zhou
Jan 19 at 20:43
add a comment |
There is no need to aggregate the rows and search in arrays. Use distinct on, example:
with mytable (key_col, text_col) as (
values
(1, 'asd'),
(1, 'a'),
(2, 'asd'),
(2, 'asdfg')
)
select distinct on (key_col) key_col, text_col as shortest
from mytable
order by key_col, length(text_col)
key_col | shortest
---------+----------
1 | a
2 | asd
(2 rows)
If you really need the function (to use in other circumstances), it may be a simple query wrapped in an SQL function:
create or replace function shortest(text)
returns text language sql as $$
select elem
from unnest($1) as elem
order by length(elem)
limit 1;
$$;
answered Jan 19 at 18:49
klinklin
58.3k65181
There is no need to aggregate the rows and search in arrays. Use distinct on, example:
with mytable (key_col, text_col) as (
values
(1, 'asd'),
(1, 'a'),
(2, 'asd'),
(2, 'asdfg')
)
select distinct on (key_col) key_col, text_col as shortest
from mytable
order by key_col, length(text_col)
key_col | shortest
---------+----------
1 | a
2 | asd
(2 rows)
If you really need the function (to use in other circumstances), it may be a simple query wrapped in an SQL function:
create or replace function shortest(text)
returns text language sql as $$
select elem
from unnest($1) as elem
order by length(elem)
limit 1;
$$;
answered Jan 19 at 18:49
klinklin
58.3k65181
edited Jan 20 at 0:50
answered Jan 19 at 18:49
klinklin
58.3k65181
answered Jan 19 at 18:49
klinklin
58.3k65181
answered Jan 19 at 18:49
klinklin
58.3k65181
58.3k65181
No need to add a function is a plus for this solution.
– Kemin Zhou
Jan 19 at 20:43
add a comment |
No need to add a function is a plus for this solution.
– Kemin Zhou
Jan 19 at 20:43
No need to add a function is a plus for this solution.
– Kemin Zhou
Jan 19 at 20:43
No need to add a function is a plus for this solution.
– Kemin Zhou
Jan 19 at 20:43
add a comment |
My solution is to create a new function; not sure this is the best answer or not. There might be solution in pure SQL.
CREATE OR REPLACE FUNCTION strarr_shortest(arr text) RETURNS text AS $$
DECLARE
res text;
minlen int := 2147483647; -- largest singed int in postgres
el text;
BEGIN
FOREACH el IN ARRAY arr
LOOP
if char_length(el) < minlen then
res=el;
minlen=char_length(el);
end if;
END LOOP;
RETURN res;
END;
$$ LANGUAGE plpgsql;
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
add a comment |
My solution is to create a new function; not sure this is the best answer or not. There might be solution in pure SQL.
CREATE OR REPLACE FUNCTION strarr_shortest(arr text) RETURNS text AS $$
DECLARE
res text;
minlen int := 2147483647; -- largest singed int in postgres
el text;
BEGIN
FOREACH el IN ARRAY arr
LOOP
if char_length(el) < minlen then
res=el;
minlen=char_length(el);
end if;
END LOOP;
RETURN res;
END;
$$ LANGUAGE plpgsql;
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
add a comment |
My solution is to create a new function; not sure this is the best answer or not. There might be solution in pure SQL.
CREATE OR REPLACE FUNCTION strarr_shortest(arr text) RETURNS text AS $$
DECLARE
res text;
minlen int := 2147483647; -- largest singed int in postgres
el text;
BEGIN
FOREACH el IN ARRAY arr
LOOP
if char_length(el) < minlen then
res=el;
minlen=char_length(el);
end if;
END LOOP;
RETURN res;
END;
$$ LANGUAGE plpgsql;
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
My solution is to create a new function; not sure this is the best answer or not. There might be solution in pure SQL.
CREATE OR REPLACE FUNCTION strarr_shortest(arr text) RETURNS text AS $$
DECLARE
res text;
minlen int := 2147483647; -- largest singed int in postgres
el text;
BEGIN
FOREACH el IN ARRAY arr
LOOP
if char_length(el) < minlen then
res=el;
minlen=char_length(el);
end if;
END LOOP;
RETURN res;
END;
$$ LANGUAGE plpgsql;
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
answered Jan 19 at 18:24
Kemin ZhouKemin Zhou
2,0411730
2,0411730
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54270061%2fin-postgres-how-to-find-the-shortest-string-an-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Does the inner part of the query be like that (e.g. because you cannot control that) are can the whole query be changed?
– sticky bit
Jan 19 at 18:39