Questions tagged [ctas]

`CREATE TABLE AS Select` (CTAS) and its associated syntax creates a new table by performing the following - Run a CREATE TABLE using the table definition of every column in the SELECT - Populating the new table with the result set of the SELECT NOTE : The table created has no indexes or constraints.

Filter by
Sorted by
Tagged with
16
votes
2answers
6k views

CREATE TABLE AS vs SELECT INTO

PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both? CREATE TABLE AS -- define a new table from the results of a query CREATE TABLE AS creates a table and fills it with data ...
12
votes
1answer
17k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select `user`.`...
10
votes
2answers
7k views

MySQL Locks while CREATE TABLE AS SELECT

I am running the following (dummy) query CREATE TABLE large_temp_table AS SELECT a.*, b.*, c.* FROM a LEFT JOIN b ON a.foo = b.foo LEFT JOIN c ON a.bar = c.bar Suppose the query ...
5
votes
1answer
3k views

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for ...
3
votes
1answer
2k views

PostgreSQL: Why is CREATE TABLE AS faster than CREATE … INSERT INTO?

Below are the two different syntaxs for the same thing. With a COPY TABLE AS SELECT (CTAS). CREATE TABLE main AS SELECT * FROM other; As separate statements with CREATE TABLE and INSERT INTO ...
2
votes
1answer
319 views

How do you CREATE TABLE AS SELECT (CTAS) with a composite type?

PostgreSQL supports CREATE TABLE AS SELECT (CTAS). It also supports composite types that can represent whole rows. Here is an example of a CTAS, CREATE TABLE foo AS SELECT * FROM ( VALUES (1), (2) );...
1
vote
1answer
590 views

How do I eliminate a second seq scan over a table when deriving a new table?

Let's say I have some sample data, 100 million rows. CREATE TEMP TABLE foo AS SELECT id, md5(id::text), trunc(random()*1e6) FROM generate_series(1,1e6) AS t(id); This will generate a table like ...
1
vote
1answer
1k views

MySQL “CREATE TABLE LIKE” include triggers

I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster) When I issue a CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?
1
vote
1answer
1k views

CREATE TABLE AS SELECT creates non-nullable columns

I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link. Oracle creates all these tables with non-nullable columns and that causes me ...
1
vote
1answer
372 views

Lock a table for a CTAS upsert in Azure SQL DataWarehouse

I have a type 2 dimension in Azure SQL Data Warehouse. Essentially I am creating an interim table with CREATE TABLE myDimension_temp AS SELECT ... FROM myStagingTable; etc After the CTAS is ...
0
votes
1answer
58 views

When does a table get an oid?

I'm wondering when a table acquires an oid. Is it only when the transaction commits? Or does it have a hidden oid prior to commit? Especially in the context of a CREATE TABLE AS SELECT.
0
votes
2answers
473 views

Table JOIN and CREATE new table extremely slow

I'm using something like this to create a new table: CREATE TABLE result AS (SELECT calls.*, targ_with_planned_calls.* FROM calls INNER JOIN planned ON calls.first_id = ...
-1
votes
1answer
395 views

ORA-01536: Out of space error when doing CTAS

I have two schemas A and B. I'm trying to copy over table B.users to A. I've followed these steps: Run in schema B: GRANT ALL ON users TO A; Run in schema A: CREATE TABLE users AS (SELECT * FROM ...