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 any notable difference between the plan. What are the steps I can take to minimize this 8s overhead? I have already tried using UNLOGGED keyword.

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 two variants. Tested in Oracle 19, but this issue has been present since the introduction of the Cast operator long before, maybe since Oracle 8: Variant 1 using To_Number (Null) produces ORA-01723: Create or Replace View JV as Select To_Number (Null) NullColumn From Dual Where 7=8; Select * From JV; -- no rows selected Create Table JT as Select * From JV; -- ORA-01723: zero-length columns are not allowed Select Data_Length, Char_Length From User_Tab_Columns Where Column_Name=Upper ('NullColumn'); Len CHAR_LENGTH --- ----------- 22 0 Variant 2 using Cast (Null as Number) works: Create or Replace View JV as Select Cast (Null as Number) NullColumn From Dual Where 7=8; Select * From JV; -- no rows selected Create Table JT as Select * From JV; -- Table created. Select Data_Length, Char_Length From User_Tab_Columns Where Column_Name=Upper ('NullColumn'); -- same as above (and no difference in the other columns not shown here for clarity) So where is the difference in the data dictionary that produces the error ? (Because there are no rows returned, the difference cannot be in the data itself as there is none, so it must be in the metadata inside the data dictionary.)

There is an old and deprecated command in PostgreSQL that predates [CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM](https://dba.stackexchange.com/q/156105/2639) , it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this.. WITH w AS ( SELECT * FROM ( VALUES (1) ) AS t(x) ) SELECT * INTO foo FROM w; But, I can't do this.. WITH w AS ( SELECT * FROM ( VALUES (1) ) AS t(x) ) CREATE TABLE foo AS SELECT * FROM w; Or, I get ERROR: syntax error at or near "CREATE" LINE 5: CREATE TABLE foo AS How would I do that using the standardized CTAS syntax.

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 myDimension_old; RENAME OBJECT myDimension_tmp TO myDimension; DROP TABLE myDimension_old; Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete? If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?

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 select * from src;

This is very fast, takes only a few seconds, but it gives warnings:

...
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265975 |
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265976 |
...
64 rows in set (0.00 sec)

I tried instead to do it with a stored procedure, opening a cursor, fetching rows and inserting them, but it takes forever; I canceled after 10 min. So, is there a way to locate the rows that cause the problem? I tried select ... limit #first_row,#last_row;, but it doesn't appear to work, and I'm not sure if it is entirely reliable any way.

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* (CTAS) syntax. I would consider this a safe operation to execute concurrently (across my multiple sessions) because it is not updating any data, thus I would want to ensure that I have the least restrictive lock possible at all times. That being said, multiple sessions are still creating data (the table) so I am quite worried that if the SQL statements contain references to the same table, I might be invoking a lock, even if I know that it is safe for the operations I have in mind. In other words, I know that all my operations are read-only (so can use a lock that is not restrictive) and that no table will have data which is updated, with the simple caveat that I am also saving the result as a table (so this is no longer 100% read-only; might invoke some restrictive lock at some point?). Thus, my question is as follows: how can I ensure the lowest lock restriction across all concurrent sessions while I do my CTAS workload? For an example, let us say I have two sessions. One is issuing the following command: CREATE TABLE t1 AS SELECT * FROM facttable; The other, concurrently issues: CREATE TABLE t2 AS SELECT * FROM facttable; How can I ensure that such operations do not invoke a lock since they are referring to the same table?

PostgreSQL supports [CREATE TABLE AS](https://www.postgresql.org/docs/current/static/sql-createtableas.html) and [SELECT INTO](https://www.postgresql.org/docs/current/static/sql-selectinto.html) 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 the names and data types associated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column names). > > CREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried. And, then. > **SELECT INTO -- define a new table from the results of a query** > > SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

Below are the two different syntaxs for the same thing. 1. With a COPY TABLE AS SELECT (CTAS). CREATE TABLE main AS SELECT * FROM other; 2. As separate statements with CREATE TABLE and INSERT INTO CREATE TABLE main (like other); INSERT INTO main SELECT * FROM other; I have observed that the CTAS is faster than distinct CREATE TABLE .. INSERT. The first takes 20 seconds to complete execution. The second syntax two takes 1 min 15 secs to complete execution. What could be the reason for difference?

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 tables a, b or c while it is running will wait for the above query to finish first. I want to avoid this lock (data consistency is not of interest). How can I achieve that? Using: MySQL 5.1.41 and InnoDB Tables p.s. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; yields no change in behavior **Update** While the query is being executed, the output of SHOW ENGINE INNODB STATUS is the following (I have made a very slow query here in purpose) ===================================== 120323 15:26:29 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 8 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1470, signal count 1468 Mutex spin waits 0, rounds 7525, OS waits 112 RW-shared spins 803, OS waits 364; RW-excl spins 1300, OS waits 959 ------------ TRANSACTIONS ------------ Trx id counter 0 3145870 Purge done for trx's n:o = 0 3145998, sees < 0 3145998 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 54447 OS file reads, 1335 OS file writes, 509 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 584 inserts, 584 merged recs, 4 merges Hash table size 34679, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2060137545 Log flushed up to 1 2060137545 Last checkpoint at 1 2060137545 0 pending log writes, 0 pending chkp writes 338 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 20799534; in additional pool allocated 1047808 Dictionary memory allocated 2897304 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 70769, created 661, written 3156 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread id 2957578240, state: waiting for server activity Number of rows inserted 2022, updated 66643, deleted 13, read 626517 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 7.59 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ And there is the actual open process list

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.

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 lastname, user.lang as lang, user.name as user_name, group.name as group_name from user inner join user_groups on (user.user_id=user_groups.user_id) left join group on (group.group_id=user_groups.group_id) where user.lang=group.lang This query creates a table that contains firstname, lastname, lang, username, group_name columns. I want it to also have an id column that is an autoincrement primary key. Is there any way to do this by changing this query? I know I can do that by altering table after executing this query, but if there is any way to do this directly in the create table statement, I'd like to know how to do that.

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 prevent this behaviour in Oracle and make resulting columns nullable?

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 | trunc ----+----------------------------------+-------- 1 | c4ca4238a0b923820dcc509a6f75849b | 159632 2 | c81e728d9d4c2f636f067f89cc14862c | 182952 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 438287 4 | a87ff679a2f3e71d9181a67b7542122c | 78240 5 | e4da3b7fbbce2345d7772b0674a318d5 | 20293 6 | 1679091c5a880faf6fb5e6087eb1b2dc | 909742 7 | 8f14e45fceea167a5a36dedd4bea2543 | 926496 8 | c9f0f895fb98ab9159f51fd0297e236d | 463718 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 65842 10 | d3d9446802a44259755d38e6d163e820 | 81791 How can I then generate a table with one scan that resembles this.. SELECT id, md5::text AS x FROM foo UNION ALL SELECT id, trunc::text FROM foo; id | x ----+---------------------------------- 1 | c4ca4238a0b923820dcc509a6f75849b 1 | 961453 2 | c81e728d9d4c2f636f067f89cc14862c 2 | 842364 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 3 | 784693 4 | a87ff679a2f3e71d9181a67b7542122c 4 | 602039 5 | e4da3b7fbbce2345d7772b0674a318d5 5 | 176938 ... But that generates a query plan like this, QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..33832.52 rows=1514052 width=64) (actual time=0.025..1034.740 rows=2000000 loops=1) -> Seq Scan on foo (cost=0.00..16916.26 rows=757026 width=64) (actual time=0.025..173.272 rows=1000000 loops=1) -> Seq Scan on foo foo_1 (cost=0.00..16916.26 rows=757026 width=64) (actual time=0.016..715.279 rows=1000000 loops=1) Planning time: 0.128 ms Execution time: 1103.499 ms (5 rows) What would it look like to have one sec scan, and would it be faster if the table was only read once? [*Question inspired from this conversation*](http://chat.stackexchange.com/transcript/message/35488343#35488343)

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 B.users); This gives the error: > SQL Error: ORA-01536: space quota exceeded for tablespace 'A' > 01536. 00000 - "space quota exceeded for tablespace '%s'" > *Cause: The space quota for the segment owner in the tablespace has > been exhausted and the operation attempted the creation of a > new segment extent in the tablespace. > *Action: Either drop unnecessary objects in the tablespace to reclaim > space or have a privileged user increase the quota on this > tablespace for the segment owner. The error seems to be misleading and I suspect something else is the problem here, because the users table has only some 1000 users or so and the tablespace in question has 2 GB allocated to it, all of it free. Furthermore trying to copy just a single row also gives the same error: CREATE TABLE users AS (SELECT * FROM B.users where rownum < 2); However, copying schema without any data succeeds: CREATE TABLE users AS (SELECT * FROM B.users where rownum = -1); /* This works */ What am I missing here?

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 = planned.another_id); The two tables have around 60k and 80k rows. One table has 5 columns and the other around 15. I'm using AWS RDS db.t2.medium instance. I've let this query run for as long as 10 minutes and it's still going. Should this be taking so long? I'm wondering what the performance issue is. I would have thought it would be fairly quick.

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?

PostgreSQL supports [CREATE TABLE AS SELECT](https://www.postgresql.org/docs/current/static/sql-createtableas.html) (CTAS). It also supports [composite types that can represent whole rows](https://www.postgresql.org/docs/current/static/rowtypes.html) . Here is an example of a CTAS, CREATE TABLE foo AS SELECT * FROM ( VALUES (1), (2) ); Here is a few examples of SELECTing a row. SELECT (1,2); SELECT ROW(1,2); SELECT t FROM ( VALUES (1,2), (2,100) ) AS t; SELECT x FROM ( VALUES ((1,2)), ((2,100)) ) AS f(x); SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS t(x,y); However, none of them seem to work with CTAS. CREATE TABLE foo AS SELECT f FROM ( VALUES (1,2), (2,100) ) AS f(x,y); ERROR: column "f" has pseudo-type record CREATE TABLE foo AS SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS f(x,y); ERROR: column "row" has pseudo-type record I get that this is a typing-error, but what syntax does CTAS want?