I am moving from using SQL Server to Postgresql and I am having trouble delcaring a variable.
In SQL Server I wrote something like this, which works just fine:
--Compute the probabilities for unique users
declare @users_total decimal(18,2)
select @users_total=count(*) from unique_users_table
select column1, uvs, uvs/@users_totalm as puvs
into probabilities_table
from column_uniquevisits_table
I attempted to translate it to Postgresql like so but got errors:
--Compute the probabilities for unique users
declare users_total numeric(18,2);
select users_total=count(*)
from unique_users_table
select coumn1, uvs, uvs/users_total as puvs
insert into probabilitiestable
from column_uniquevisits_table
What is the proper way of writing what I originally had in SQL Server in Postgresql?
If you want to create a table based on a select, use create table as select.
There is no need for a declare (which can't be used outside of a PL/pgSQL function anyway)
create table probabilitiestable
as
select column1,
uvs,
uvs::numeric/(select count(*) from count(*) from unique_users_table) as puvs
from column_uniquevisits_table;
The cast ::numeric is necessary because otherwise the division would be done with integer arithmetic which means you would not get any fractional valuues. If uvs is already defined as numeric (or decimal or float) then you don't need that.
I'd use a cross join to create the count to avoid the context switches and avoid multiple queries to the unique_users_table. The reason we can use a cross join is because we know the result of the query will always be 1 record. Thus it doesn't have a multiplication effect on the results.
CREATE table probabilitiestable as
SELECT column1, uvs, uvs/ut.users_total as puvs
FROM column_uniquevisits_table
CROSS JOIN (SELECT count(*) as users_total
FROM unique_users_Table) ut