snipt

Ctrl+h for KB shortcuts

SQL

Gettin next value from a sequence

nextval('sequence_name') 
https://snipt.net/embed/85ecf92a9cffb7487876a7ca360a2bc7/
/raw/85ecf92a9cffb7487876a7ca360a2bc7/
85ecf92a9cffb7487876a7ca360a2bc7
sql
SQL
1
2019-08-19T22:14:21
True
False
False
/api/public/snipt/9817/
gettin-next-value-from-a-sequence
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="n">nextval</span><span class="p">(</span><span class="s1">&#39;sequence_name&#39;</span><span class="p">)</span> </span></pre></div> </td></tr></table>
autoincrement, postgresql, sequence

SQL

Output query for updating all sequence of all schemas to lowest available sequence number.

select 'select setval(' || regexp_replace(column_default, E'^nextval\\((.*)\\).*', E'\\1') || ', max(' || column_name || ')) from ' || table_schema || '.' || table_name || ';' from information_schema.columns where column_default ~ '^nextval';
https://snipt.net/embed/7fe816dd1cddd2ae2ade01efd5248acc/
/raw/7fe816dd1cddd2ae2ade01efd5248acc/
7fe816dd1cddd2ae2ade01efd5248acc
sql
SQL
1
2019-08-21T20:05:27
True
False
False
/api/public/snipt/4537/
output-query-for-updating-all-sequence-of-all-schemas-to-lowest-available-sequence-number
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="s1">&#39;select setval(&#39;</span> <span class="o">||</span> <span class="n">regexp_replace</span><span class="p">(</span><span class="n">column_default</span><span class="p">,</span> <span class="n">E</span><span class="s1">&#39;^nextval\\((.*)\\).*&#39;</span><span class="p">,</span> <span class="n">E</span><span class="s1">&#39;\\1&#39;</span><span class="p">)</span> <span class="o">||</span> <span class="s1">&#39;, max(&#39;</span> <span class="o">||</span> <span class="k">column_name</span> <span class="o">||</span> <span class="s1">&#39;)) from &#39;</span> <span class="o">||</span> <span class="n">table_schema</span> <span class="o">||</span> <span class="s1">&#39;.&#39;</span> <span class="o">||</span> <span class="k">table_name</span> <span class="o">||</span> <span class="s1">&#39;;&#39;</span> <span class="k">from</span> <span class="n">information_schema</span><span class="p">.</span><span class="n">columns</span> <span class="k">where</span> <span class="n">column_default</span> <span class="o">~</span> <span class="s1">&#39;^nextval&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
"primary keys", postgres, postgresql, sequence