CODEWITHSUNDEEP
sqlsql-serverdatabasepostgresql
Vindication09
Vindication09

Reputation: 45

How to translate syntax from SQL Server to Postgresql?

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?

Upvotes: 0

Views: 282

Answers (2)

xQbert
xQbert

Reputation: 35333

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

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions