Postgres Create Table Like Syntax Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring postgres create table example developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question
Postgresql Create Table Primary Key
x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; postgresql create temporary table it only takes a minute: Sign up Postgres 9.4 Create Table if not exists like up vote 0 down vote favorite This statement is throwing syntax errors: CREATE TABLE IF NOT EXISTS "weChat_data_abc" LIKE "weChat_data_sample" The table weChat_data_sample exists
Create Table If Not Exists Postgres
and is empty. I get the error: Error : ERROR: syntax error at or near "LIKE" Output of SELECT VERSION(): PostgreSQL 9.4.4 on x86_64-apple-darwin14.4.0, compiled by Apple LLVM version 6.1.0 (clang-602.0.53) (based on LLVM 3.6.0svn), 64-bit sql postgresql postgresql-9.4 share|improve this question asked Sep 10 '15 at 18:18 Jimmy Scray 549 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote Aha! I found the answer. Looks like I need to wrap the LIKE clause create table like redshift in parenthesis. CREATE TABLE IF NOT EXISTS "weChat_data_abc"(LIKE "weChat_data_sample") Documentation Here: http://www.postgresql.org/docs/9.4/static/sql-createtable.html share|improve this answer edited Sep 10 '15 at 18:23 answered Sep 10 '15 at 18:22 Jimmy Scray 549 3 Using quoted identifiers is almost always a bad choice. You should really avoid quoting identifiers, they are much more trouble in the long run than they are worth it –a_horse_with_no_name Sep 10 '15 at 18:22 @a_horse_with_no_name I need quoted identifiers since my tables are written in semi camel case. –Jimmy Scray Sep 10 '15 at 18:27 3 As I said: don't do that, it's really not worth all the trouble (and the typing) –a_horse_with_no_name Sep 10 '15 at 18:28 @a_horse_with_no_name Can you give me an example of when this would be a problem? –Jimmy Scray Sep 10 '15 at 19:22 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged sql postgresql postgresql-9.4 or ask your own question. asked 1 year ago viewed 154 times active 1 year ago Related 1Can someone give examples of use case of the ordered-set functions in Post
8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.3.14 Documentation Prev Up Next CREATE TABLE NameCREATE TABLE--define a new table Synopsis CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ]
Postgres Select Into Temp Table
table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint
Postgres Drop Table If Exists
| LIKE source_table [ like_option ... ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH ( storage_parameter postgres drop temp table [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | http://stackoverflow.com/questions/32509052/postgres-9-4-create-table-if-not-exists-like UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name OF type_name [ ( { column_name WITH OPTIONS [ column_constraint [ ... ] ] | table_constraint } [, ... ] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT https://www.postgresql.org/docs/9.3/static/sql-createtable.html default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] and like_option is: { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are: [ WITH ( storage_parameter [= value] [, ... ] ) ] [ USING INDEX TABLESPACE tablespace_name ] exclude_element in an EXCLUDE constraint is: { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] Description CREATE TABLE will create a new, initially empty table in the current database. The table will be owned by the user issuing the command
8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.2.18 Documentation Prev Up Next CREATE TABLE AS NameCREATE TABLE AS--define a new table https://www.postgresql.org/docs/9.2/static/sql-createtableas.html from the results of a query Synopsis CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name [ (column_name [, ...] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] AS query create table [ WITH [ NO ] DATA ] Description 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 postgres create table 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. Parameters GLOBAL or LOCAL Ignored for compatibility. Use of these keywords is deprecated; refer to CREATE TABLE for details. TEMPORARY or TEMP If specified, the table is created as a temporary table. Refer to CREATE TABLE for details. UNLOGGED If specified, the table is created as an unlogged table. Refer to CREATE TABLE for details. table_name The name (optionally schema-qualified) of the table to be created. column_name The name of a column in the new table. If column names are not provided, they are taken from the output column names of the query. WITH ( storage_parameter [= value] [, ... ] ) This clause specifies optional storage parameters for the new table; see Storage Parameters for more information. The WITH clause can also include OIDS=TRUE (or just OIDS) to specify that rows of the new table should have OIDs (object identifiers) assigned to them, or OI