Postgresql - Using Subqueries with Alter Sequence Expressions

Postgresql - Using subqueries with alter sequence expressions

I don't believe you can do it like that but you should be able to use the setval function direction which is what the alter does.

select setval('sequenceX', (select max(table_id)+1 from table), false)

The false will make it return the next sequence number as exactly what is given.

Postgres manually alter sequence

The parentheses are misplaced:

SELECT setval('payments_id_seq', 21, true);  -- next value will be 22

Otherwise you're calling setval with a single argument, while it requires two or three.

This is the same as SELECT setval('payments_id_seq', 21)

Using result of select inside alter table statement in postgres

Here is one way to do it:

select setval(pg_get_serial_sequence('Table_1', 'id'), coalesce(max(id),0) + 1, false) 
from Table_1;

Rationale:

• pg_get_serial_sequence() returns the name of the sequence for the given table and column
• set_val() can be used to reset the sequence
• this can be wrapped in a select that gives you the current maximum value of id in the table (or 1 if the table is empty)

How to reset Postgres' primary key sequence when it falls out of sync?

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

Source - Ruby Forum

PostgreSQL volatile expressions and subqueries

Well, random() itself is volatile, hence you don't get strings with the same character repeated to the end.

If you look at the plans for the queries with and without b*0 you will see:

With b*0:

 Function Scan on generate_series a  (cost=0.00..37530.00 rows=1000 width=4)
   SubPlan 1
     ->  Aggregate  (cost=37.51..37.52 rows=1 width=32)
           ->  Function Scan on generate_series  (cost=0.01..25.01 rows=1000 width=0)

Without b*0:

 Function Scan on generate_series a  (cost=37.52..47.52 rows=1000 width=0)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=37.50..37.51 rows=1 width=32)
           ->  Function Scan on generate_series  (cost=0.00..25.00 rows=1000 width=0)

If PostgreSQL determines that the inner aggregation is not dependent on a, then it is evaluated once as an InitPlan, and the volatility or not of the expressions within are irrelevant. By introducing the dependency of the subquery on a, i.e. making it a correlated subquery, the evaluation must be redone for each row of a.

How to understand the use of the ... Select 1 from ... an expression in SQL

There are two things you need to understand:

1. What is a co-related subquery
2. How does the (NOT) EXISTS operator work.

A co-related subquery is run once for each row that is returned from the outer query. You can imagine this as a kind of nested loop, where for each row returned by the outer query, the sub-query is executed by using the values of the columns from the outer query.

So if the outer query processes the row for 'ken Adams' it will take the value 1 from dep_id_fk and the value 70000 of the column salary and essentially run:

select 1 
from employee e2
where e2.dep_id_fk = 1 --<< this value is from the outer query
  and e2.salary > 70000 --<< this value if from the outer query

If that query returns no rows, the row from the outer query is included in the result. Then the database proceeds with the next row in the outer query and does the same again until all rows from the outer query are processed and either included in or excluded from the result.

However the NOT EXISTS and EXISTS operators check only check for the presence of rows from the sub-query. The actual value(s) returned from the sub-query are completely irrelevant.

A lot of people incorrectly assume that select 1 is somehow cheaper than select * in the sub-query - but this is a totally wrong assumption. The expression is never even evaluated, so it's completely irrelevant what is selected there. If you think select * is more logical than select 1 than use that.

To prove that the expression is never evaluated (or even looked at), you can use one that would otherwise throw an exception.

select * 
from employee e
where not exists (select 1/0
                  from employee e2
                  where e2.dep_id_fk = e.dep_id_fk
                    and e2.salary > e.salary);

If you run select 1/0 outside of a sub-query used for an EXISTS or NOT EXISTS condition, it would result in an error. But the EXISTS operator never even looks at the expressions in the SELECT list.

---

Related Topics