`IN` as a filter option

[qwesda]

It would be nice to have an IN filter as an option when filtering table data.

• for number like columns an input of 1,2,3 should lead to "my_column" IN (1,2,3)
• for string like fields (including enums) an input of foo,bar,3,'hello, you' should lead to "my_column" IN ('foo', 'bar', '3', 'hello, you')

extra points:

• autocompletion would be nice for enum columns (or a drop down menu for filter that only take one argument)
• the little popover window for foreign keys could also be useful here when working with FK columns

[jopotts]

Jacob, please add an IN option! If there's just one thing in the next release, then let this be it. Awesome software. Thank you!

[esetnik]

I agree with this. I'm finding myself needing to use Custom SQL with IN more and more often. If you need any help developing or testing this, I'd be glad to contribute my time.

[nbcraft]

Would love this as well 🙏

[jheth]

I found myself wanting this today too. Would be very nice to have.

[daniel-refahi]

this is a 6-year-old request. why it hasn't been addressed yet.

[espen]

@daniel-refahi because software development is a difficult task of managing the importance of a myriads of feature improvements, bug fixes and other issues. If you want this feature then give it a thumbs up so the developer can see how many people are interested in it.

[barrywoolgar]

@espen A valid point, however, this simple issue has been number 5 on the README Top 10 priorities list since Feb 2020 (not updated since then). I can understand a little frustration that this issue doesn't appear to have had a developer response in all this time, especially given this is paid software with an upgrade on the horizon. Why should people think upvoting issues makes any difference at this point?

Edit: This issue is now number 2 on the current sorting: https://github.com/jakob/Postico/issues?q=is%3Aissue+is%3Aopen+sort%3Areactions-%2B1-desc

[sunildmonte]

I would love this as well!

[jakob]

Since this request is so popular, I've decided to take the time to add an "is in list" and "is not in list" filters to Postico 2. You can try them by downloading the latest development build: https://releases.eggerapps.at/postico2/changelog?update_channel=1

I think this filter is so useful that I've made it the default when selecting "Filter Rows by column name..." from the context menu.

As a little gimmick, Postico also offers autocomplete for ENUM fields when using the IN filter. After testing it a bit, I consider this feature a 1000% improvement and I concede I should have added it much sooner.

Please test it! I would love to hear your thoughts!

[qwesda]

very nice!

• the autocomplete is great
• the tokenised text input feels good to use, I was a bit sceptical at first, but it quickly feels very natural
• pasting comma separated values works and whitespace is trimmed correctly

nitpicks:

• pasting tab separated and newline separated values (and maybe ; separated) should also work – there could be an "undo-tokenising" button after pasting, that is hidden after a timeout
• I'm not sure if it's the best default as the filter option ... for enum columns it makes sense, but for generic text columns it doesn't – I would stick with contains for text columns
• the visual margins in the autocomplete popup are a bit off, and the whole text box feels a bit too cramped, a few pixels more height might be good

[espen]

Very useful with autocomplete for enums! And great to see IN as filter option. Thanks.

I like this as the default filter option. As with #824 I have found that LIKE being the default filter is problematic for performance (and also usability, I normally want to do an exact match).

[jakob]

pasting tab separated and newline separated values (and maybe ; separated) should also work

Good point, I'll see if I can change that

I would stick with contains for text columns

The reason why I changed it is because people are complaining about performance (accidentally applying a contains filter to a big table takes a long time). Do you think it makes sense to differentiate between TEXT and VARCHAR? I personally use VARCHAR for short strings (where "is in list" often makes more sense), and TEXT for long text (where "contains" would make more sense). Is that common usage, or just me.

the visual margins in the autocomplete popup are a bit off

I'm using NSTokenField, which does 99% of what I want, but customising it is unfortunately difficult.

[qwesda]

We'll my opinion about the default option is not too strong. I personally have gotten used to the default text filter being LIKE '%$term%' and use it quite often. But I also know about the performance implications of it and don't start queries that are too ambitious. My guess would be that you will get a different group of people complaining if you change the current behaviour. Some simply because you change something and some because the old behaviour better fits their use-case.

I think distinguishing between text and varchar would be hard to discover or make sense of for most users. I personally use text exclusively. I read about the differences of the text types in PG after switching from mysql and the general consensus seemed to be "just use text".

If NSTokenField is too stubborn then don't wast too much time on it ... the functionality is more important than some visual quirks.

[jakob]

In the latest dev build I've made the following changes:

• the token field now accepts more separators, so you can eg. paste tab or newline separated tokens
• the default filter when right-clicking a column heading and selecting "filter by column" is now "is in list"
• the default filter when opening the filter is still "any column contains"
  • when selecting a text column from the popup menu, it stays on "contains"
  • when selecting a numeric column that doesn't support "contains", it changes to "is in list"

I think this is a good balance. For small tables, the convenient "any column contains" filter is still the default, and for those who use Postico to search very large tables, it's less likely that they will accidentally use the "contains" filter.

[qwesda]

I will test the new release as soon as I have time ... in the meantime I stumbled across this issue: the contains filter seems to be completely gone for the uuid type – I kind of need it though ... I'm using uuidv7 meaning the beginning part of the uuids are all very similar and the end parts are very distinct. I'm using the last 12 chars of the uuid (i.e. the random part) to generate partition names (the default max table name length is 63 chars ...).

So sometimes I have a legitimate use case for contains for uuid fields. I also think begins/ends with could be useful to somebody. I doesn't need to be the default, but the options should be available.

[jakob]

@qwesda The contains filter on UUID has been gone for some time already (see #765 and #184). I guess the begins with case could be handled with a >= and a <= filter, which would have the advantage of being very fast (at least for BTREE indexed columns).

[qwesda]

Very possible that this is unrelated to the IN thing. But still sometimes it is useful. Should I open a new issue for this?

[jakob]

@qwesda Yes, please open a new issue. Your use case sounds useful, but it's a bit hard to implement at the moment.

[joeflack4]

+1