Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You have a precedence issue. This:</p> <pre><code>d.context-&gt;'hostname' ~ r.context-&gt;'Name' </code></pre> <p>is being parsed like this:</p> <pre><code>d.context -&gt; ('hostname' ~ r.context) -&gt; 'Name' </code></pre> <p>so the <code>~</code> is trying to match the <code>'hostname'</code> TEXT value with the <code>r.context</code> HSTORE. Add some parentheses to force the issue:</p> <pre><code>(d.context-&gt;'hostname') ~ (r.context-&gt;'Name') </code></pre> <p>If we have a look at the <a href="http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-PRECEDENCE" rel="nofollow">operator precedence table</a>, you'll see this:</p> <blockquote> <ul> <li>stuff that doesn't contain <code>~</code> or <code>-&gt;</code></li> <li><code>(any other)</code>: all other native and user-defined operators</li> <li>more stuff that doesn't contain <code>~</code> or <code>-&gt;</code></li> </ul> </blockquote> <p>So both <code>~</code> and <code>-&gt;</code> fall into the "other" category. I'd guess that <code>~</code> is added to the operator list before <code>-&gt;</code> since <code>~</code> is a native operator whereas <code>-&gt;</code> is added by the hstore extension.</p> <hr> <p>Interestingly enough, the <em>more stuff that doesn't contain <code>~</code> or <code>-&gt;</code></em> list does contain LIKE and this:</p> <pre><code>hstore1 -&gt; k1 like hstore2 -&gt; k2 </code></pre> <p>works as expected so in here:</p> <pre><code>select 'a=&gt;b'::hstore -&gt; 'a' like 'a=&gt;b'::hstore -&gt; 'a'; select 'a=&gt;b'::hstore -&gt; 'a' ~ 'a=&gt;b'::hstore -&gt; 'a'; </code></pre> <p>the first query will be <code>'t'</code> whereas the second will produce your "operator does not exist" error. I only mention this because I would have expected the <code>LIKE</code>, <code>SIMILAR</code>, and <code>~</code> operators to have the same precedence since they're all variations on the same theme.</p>
    singulars
    1. This table or related slice is empty.
    1. POpostgres left join using regular expression on a hstore?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmu is too short
    1. USmu is too short
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthanks for the response: so you're correct that using `LIKE` works as expected, however, i still cannot get the `~` operator to work. when i use `( d.context->'hostname' ) ~ ( r.context->'Name' )`, it comes back with `ERROR: invalid regular expression: quantifier operand invalid`
      singulars
      1. PO
      1. USyee379
    2. CO@yee379: What does `r.context->'Name'` look like in those cases? And a regex might not be the right solution at all, if you just want `r.context->'Name'` to be at the end of `d.context->'hostname'` then the [`position`](http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-SQL) string function might be a better idea than a regex.
      singulars
      1. PO
      1. USmu is too short
    3. CO`r.context->'Name'` is normally a non-fqdn'd hostname, so yes, using `LIKE (r.context->'Name' || '%') would work as it just compares to a fdqn'd hostname (not really sure position would be a great fit here). however, i was just confused as to why the `~` still wouldn't work - even when they are wrapped in parenthesis. cheers!
      singulars
      1. PO
      1. USyee379
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload