Skip to main content
3 votes
Accepted

Convert PostgreSQL array type

To convert an existing text[] array to the enum type letter[] (like you ask) simply cast: SELECT '{b,c,a}'::text[]::letter[]; To actually convert the table column (like you probably wanted to ask): ...
Erwin Brandstetter's user avatar
1 vote
Accepted

Replacing substrings in array elements

To only replace the leading hostname - and only update rows where anything changes to begin with: UPDATE tbl t SET urls = t1.urls FROM ( SELECT t.id, u.* FROM tbl t LEFT JOIN LATERAL (...
Erwin Brandstetter's user avatar
1 vote
Accepted

Performance issues with @> operator in postgres 14

articles that have a tag which is in a large list This should translate to the overlap operator &&, not the containment operator @>: ... WHERE tags && ARRAY['my_extra_long_tag_1', '...
Erwin Brandstetter's user avatar
1 vote

How to pass an array to trigger function in TG_ARGV?

@bjmc this is what you should have done: CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER AS $$ DECLARE special_names text[]; special_users text[]; BEGIN special_names := ...
xpusostomos's user avatar
1 vote

How to change array_agg() to return empty array on empty input in PostgreSQL?

Interesting question. I wrote a custom array_agg a number of weeks ago, and I can boil out what you seem to need. First, we will put together an accumulator function, like so: CREATE OR REPLACE ...
Jeff Vermeer's user avatar
1 vote

Combining array_agg and unnest

This also works: SELECT array_agg(DISTINCT all_values) as uniq_values FROM (SELECT key, unnest(values) as all_values FROM data) q1 GROUP BY key
Alina Evgenevna's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible