Hot answers tagged

15

Without the explanation, always use CREATE TABLE AS without exception. At the bottom of each under NOTES this is cleared up, Notes for SELECT INTO, CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause ...


11

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) 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`.`...


10

I see this query in your SHOW INNODB STATUS\G CREATE TABLE 1_temp_foo AS SELECT SQL_NO_CACHE a.* FROM crm_companies AS a LEFT JOIN users b ON a.zipcode = b.uid LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id ...


5

It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would use this syntax. CREATE TABLE foo AS WITH w AS ( SELECT * FROM ( VALUES (1) ) AS t(x) ) SELECT * FROM w; Also worth noting that it's not explicit in the ...


3

CREATE TABLE AS has some advantages over the other form, namely in the reduction or elimination of WAL.. Certainly optiimizations can be applied to a few commands (viz. CREATE TABLE AS, CREATE INDEX, CLUSTER, COPY into tables that were created or truncated in the same transaction) in certain wal-reduction modes (minimal). In minimal level, WAL-logging of ...


3

Table OIDs are assigned quite early in table creation, and are definitely present by the time the CREATE TABLE finishes (before xact commit), since the row is visible in pg_class by then. The new pg_class row (and its oid) are not visible to concurrent transactions until commit, though. It's not clear why you care, though. It shouldn't matter.


2

Answer inspired from this A few methods, all test with PostgreSQL 9.5. CROSS JOIN LATERAL ... VALUES This is actually slower, but it seems good for a first attempt.. SELECT id, x FROM foo CROSS JOIN LATERAL (VALUES (md5),(trunc::text)) AS t(x); QUERY PLAN ...


2

In addition to setting the transaction isolation level to READ COMMITTED (or READ UNCOMMITTED), you have to also have your binary log format set to MIXED or ROW. STATEMENT based replication locks this type of statement to make sure everything is "safe." You could also set innodb_locks_unsafe_for_binlog=1 temporarily, but you could end up with a slave that ...


2

The long and the short answer is no! This is yet another "non-feature" of MySQL. This construct is known more commonly as CTAS (CREATE TABLE AS.... SELECT) - the CREATE TABLE LIKE syntax is non-standard - like so much of MySQL! MySQL's documentation argues here - that this lacuna is to ensure "flexibility" - you'd laugh if it wasn't so sad! CREATE TABLE ....


2

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement: create table blah2 ( ctascolumn1 not null, ctascolumn2 null ) as select col1, col2 from blah;


2

The user A must be running out of quota(or you might have forgotten to assign quota) on assigned tablespace. 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. Error is self-describing. It's ...


1

I don't think you can do it the way you are trying to... if my understanding of "A pseudo-type cannot be used as a column data type" is correct. However, you can do something similar. First, you define your composite type (for instance, we have a "point in two dimensions", with fields x and y): -- Need to define the composite type CREATE TYPE point_2d AS ...


1

There's one other thing I noticed that's missing from the accepted answer. Using CREATE TABLE AS preserves the nullable attribute of each column which seems to be ignored by SELECT INTO. Just on this basis alone, I'd recommend CREATE TABLE AS. A common use case for both statements is to load data from a long running query into a table without locking that ...


1

Sounds like you have neither index: On calls an INDEX or PRIMARY KEY starting with first_id Ditto for planned.another_id You need one or the other to keep from doing 60k times 80k operations. With an index, it will be only 60k plus 80k operations. Please provide SHOW CREATE TABLE to confirm, and to let us check for other issues such as dissimilar ...


1

You are doing DDL along with the SELECT Doing CREATE TABLE AS SELECT mechanically does two commands CREATE TABLE INSERT INTO ... SELECT This will produce locks on both calls and planned I have written about this behavior in some of my older posts Mar 23, 2012 : MySQL Locks while CREATE TABLE AS SELECT Aug 08, 2014 : MySQL consistent nonlocking reads vs. ...


Only top voted, non community-wiki answers of a minimum length are eligible