3 ms·

One thing I don't understand in SQL is why creating a tmp table is so verbose, why we can't use type inference. There is an internal software where I work wher

by cm2187 5y ago

One thing I don't understand in SQL is why creating a tmp table is so verbose, why we can't use type inference. There is an internal software where I work where to create a tmp table you just assign the result of the query to a variable. It is so much nicer. So for instance creating a tmp table becomes as simple as the below, no need to declare each columns, to do an insert, to drop the table in the end: @t = select colA, colB from tbl select top 10 * from @t order by colB

bvrmn 5y ago
CREATE TABLE AS can help. As well as CTEs.
saurik 5y ago
You don't have to do all of those things though? Just create table as select (or sometimes select into).
- cm2187 5y ago
  Thanks. I didn't know this syntax. You still have to drop it in the end but somehow I never ran into this syntax.
  - croes 5y ago
    You don't have to drop temp tables unless you want to use a second select into in the same session. Otherwise temp tables are automatically dropped if the session ends.
  - zbuf 5y ago
    Even better, look at WITH, or CREATE TEMPORARY VIEW AS. Then the planner can make optimisations to the overall query. That is assuming you use your "temporary" only once.
  - deleted 5y ago
    [deleted]
lelanthran 5y ago
> One thing I don't understand in SQL is why creating a tmp table is so verbose, why we can't use type inference. > There is an internal software where I work where to create a tmp table you just assign the result of the query to a variable. It is so much nicer. So for instance creating a tmp table becomes as simple as the below, no need to declare each columns, to do an insert, to drop the table in the end: > @t = select colA, colB from tbl > select top 10 * from @t order by colB Unless I am misunderstanding what you are looking for, 'SELECT INTO' works the way you want: https://www.postgresql.org/docs/9.1/sql-selectinto.html https://www.postgresql.org/docs/9.1/sql-selectinto.html It's on every RDBMS I've used, IIRC.

by cm2187 5y ago

bvrmn 5y ago

CREATE TABLE AS can help. As well as CTEs.

saurik 5y ago

You don't have to do all of those things though? Just create table as select (or sometimes select into).

cm2187 5y ago
Thanks. I didn't know this syntax. You still have to drop it in the end but somehow I never ran into this syntax.
- croes 5y ago
  You don't have to drop temp tables unless you want to use a second select into in the same session. Otherwise temp tables are automatically dropped if the session ends.
- zbuf 5y ago
  Even better, look at WITH, or CREATE TEMPORARY VIEW AS. Then the planner can make optimisations to the overall query. That is assuming you use your "temporary" only once.
- deleted 5y ago
  [deleted]

lelanthran 5y ago

> One thing I don't understand in SQL is why creating a tmp table is so verbose, why we can't use type inference. > There is an internal software where I work where to create a tmp table you just assign the result of the query to a variable. It is so much nicer. So for instance creating a tmp table becomes as simple as the below, no need to declare each columns, to do an insert, to drop the table in the end: > @t = select colA, colB from tbl > select top 10 * from @t order by colB Unless I am misunderstanding what you are looking for, 'SELECT INTO' works the way you want: https://www.postgresql.org/docs/9.1/sql-selectinto.html https://www.postgresql.org/docs/9.1/sql-selectinto.html It's on every RDBMS I've used, IIRC.