How to translate syntax from SQL Server to Postgresql?

Question

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?

Possible duplicate of http://stackoverflow.com/questions/766657/how-do-you-use-variables-in-a-simple-postgresql-script — etsa, Apr 18 '17 at 12:52

score 1 · Answer 1 · answered Apr 18 '17 at 12:52

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.

score 1 · Answer 2 · edited Apr 18 '17 at 13:02

1

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

edited Apr 18 '17 at 13:02

user247702

23,641
15
110
157

answered Apr 18 '17 at 12:55

xQbert

34,733
2
41
62

The Postgres optimizer will not run the scalar sub-select once for each row. It is smart enough to do that only once. – Apr 18 '17 at 13:16
I generalize too much. I know on some engines they aren't smart enough to do that. Good to know. Just force of habit to do it in the "FROM" for those engines that aren't smart enough ! – xQbert Apr 18 '17 at 13:19

How to translate syntax from SQL Server to Postgresql?

2 Answers2