Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

Create Table As Select (CTAS) is a method to create a table with a query. It's supported by multiple vendors including Windows Azure DB Warehouse, Oracle, and PostgreSQL. CTAS is a terse method to get a test-case.

http://www.orafaq.com/wiki/CTAS http://dev.mysql.com/doc/refman/8.0/en/create-table-select.html https://www.postgresql.org/docs/current/static/sql-createtableas.html

17 questions
30
votes
1 answer

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 instance, I can do this.. WITH w AS ( SELECT * …
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
29
votes
2 answers

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 computed by a SELECT command. The table columns have…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
17
votes
1 answer

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`.`firstname` as `firstname`, `user`.`lastname` as…
Arash Mousavi
  • 673
  • 2
  • 12
  • 21
11
votes
2 answers

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 takes 10 minutes to run. Trying to update values in…
clops
  • 347
  • 1
  • 5
  • 15
6
votes
1 answer

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 CREATE TABLE main (like other); INSERT INTO…
user2274074
  • 473
  • 3
  • 8
  • 14
4
votes
1 answer

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) ); Here is a few examples of SELECTing a…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
3
votes
0 answers

Difference between To_Number (Null) and Cast (Null as Number) in Oracle

There is a difference in behavior between these two variants to create a view with a null number column and then a table from that view: One fails and one succeeds. At the same time there is no visible difference in the data dictionary between these…
Juergen
  • 76
  • 1
  • 5
2
votes
2 answers

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 complete I do a RENAME OBJECT myDimension TO…
Tom
  • 21
  • 1
1
vote
1 answer

How to find error rows in bulk insert?

MySQL 5.6: I'm experimenting with copying data from one table to another in different ways. The source table is on a remote server and has about 500,000 rows - I use the federated engine to connect. I first tried this: mysql > create table tgt as…
j4nd3r53n
  • 231
  • 1
  • 11
1
vote
1 answer

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 this.. id | md5 |…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
1
vote
1 answer

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?
Boden Garman
  • 113
  • 1
  • 4
1
vote
1 answer

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 problems later when I try to update them. How can I…
alonk
  • 301
  • 1
  • 4
  • 11
0
votes
1 answer

Reducing CREATE TABLE overhead?

I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s. When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM table;, the query now takes 18s. I could not see…
Zeruno
  • 547
  • 1
  • 4
  • 15
0
votes
1 answer

Enforcing lowest transaction isolation level across concurrent sessions?

I am trying to learn transactions better in PostgreSQL and came across this observation. I have a situation where I am creating multiple tables using an SQL statement across multiple sessions. I am using CREATE TABLE *table_name* AS *sql_stmt* …
Zeruno
  • 547
  • 1
  • 4
  • 15
0
votes
1 answer

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.
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
1
2