i want get return count rows when execute select into table

when I run

select * into mobile_n from mobile where c_name='dic'

I want to get the reuslt of select count(1) from mobile_n

I tried

select count(1) 
from ( 
  select * into mobile_n from mobile where c_name='dic' 
  return * 
)

but it did not work

CodePudding user response：

You can try to use CTE for count result

WITH result AS (
    select * 
    into mobile_n 
    from mobile 
    where c_name='dic' 
    RETURNING 1
)
SELECT count(*) 
FROM result;

CodePudding user response：

You can't. https://www.postgresql.org/docs/current/sql-selectinto.html.

SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

emphasis/bold by me.

work around:

create table mobile_n as select  * from mobile limit  0;
with a as(
insert into  mobile_n select  * from mobile where c_name = 'dic' returning 1)
select count(*) from a;