Hot answers tagged

18

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`.`...


8

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

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 ...


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 ...


1

To locate the rows you want to see, you must do two things: change the way you create the table change the way you load data into the table Why Change the Way You Create the Table ??? When you did this create table tgt as select * from src; You create the tgt table without any indexes. You can verify this by running show create table src \G show create ...


1

There is no danger of locking with your scenario. The only lock that is taken on facttable is the ACCESS SHARE lock that prevents others from dropping or altering the table while it is being read. Since ACCESS SHARE doesn't conflict with itself, that is no problem (two people can read the same poster). The only consideration is the I/O load: if many ...


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