Postgresql 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 postgresql create table example of this site About Us Learn more about Stack Overflow the company postgres create table auto_increment primary key Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges postgres create table primary key Ask Question 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; it only takes a postgresql insert into table minute: Sign up Postgresql - CREATE TABLE SYNTAX ERROR USING LIKE - up vote 19 down vote favorite 2 i'm trying to create a temp table from a parent table: This is the code that i execute with pgAdmin III ( or by JDBC in Java ): CREATE TEMP TABLE table1_tmp LIKE table1 INCLUDING DEFAULTS; And the error i recieved is: [WARNING
Postgres Create Table Foreign Key
] CREATE TEMP TABLE table1_tmp LIKE table1 INCLUDING DEFAULTS ERROR: syntax error at or near «LIKE» LÍNEA 1: CREATE TEMP TABLE table1_tmp LIKE table1 INCLUDING DEFAULTS ^ Reading postgresql 8.4 documentation, create tables using this, its very easy, but i don't understand where is the syntax problem. postgresql table share|improve this question edited Jan 12 '14 at 16:24 a_horse_with_no_name 187k24236312 asked Feb 2 '11 at 12:43 jzafrilla 73121133 add a comment| 2 Answers 2 active oldest votes up vote 36 down vote accepted You need to put the like in to parens like CREATE TEMP TABLE table1_tmp ( LIKE table1 INCLUDING DEFAULTS ) ; This is not obvious from the docs if you don't count parens 1:1 share|improve this answer answered Feb 2 '11 at 13:01 Heiko Rupp 18.9k104998 add a comment| up vote 20 down vote I'm not a Postgresql user but the manual say that there is ( ) around the like setence. CREATE TEMP TABLE table1_tmp (LIKE table1 INCLUDING DEFAULTS); share|improve this answer edited Feb 2 '11 at 15:32 Frank Heikens 48.8k128191 answered Feb 2 '11 at 12:52 fluminis 2,00931327 ad
8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.4.9 Documentation Prev Up Next CREATE TABLE NameCREATE TABLE--define a new table Synopsis CREATE [ [ GLOBAL |
Create Table In Postgresql Pgadmin
LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT create table if not exists postgres EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | create table like redshift LIKE source_table [ like_option ... ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | http://stackoverflow.com/questions/4874607/postgresql-create-table-syntax-error-using-like WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name OF type_name [ ( { column_name WITH OPTIONS [ column_constraint [ ... ] ] | table_constraint } [, ... ] ) ] [ WITH ( storage_parameter [= value] [, https://www.postgresql.org/docs/9.4/static/sql-createtable.html ... ] ) | 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 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_opti
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and http://dba.stackexchange.com/questions/86724/how-to-create-a-temporary-table-using-values-in-postgresql policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered https://www.drupal.org/node/271646 Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. create table Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How to create a temporary table using VALUES in PostgreSQL up vote 8 down vote favorite 2 I am learning PostgreSQL and trying to figure out how to create a postgres create table temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes. I looked at the documentation for CREATE TABLE and it says VALUES can be used as a query but gives no example; the documentation for the VALUES clause linked therein does not have an example either? So, I wrote a simple test as follows: DROP TABLE IF EXISTS lookup; CREATE TEMP TABLE lookup ( key integer, val numeric ) AS VALUES (0,-99999), (1,100); But PostgreSQL (9.3) is complaining about syntax error at or near "AS" My questions are: How can I fix the statement above? How can I adapt it to be used in a WITH block? Thanks in advance. postgresql syntax share|improve this question asked Dec 21 '14 at 19:44 tinlyx 202239 Thanks everyone. Both solutions worked. –tinlyx Dec 21 '14 at 22:32 add a comment| 2 Answers 2 active oldest votes up vote 12 down vote accepted If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like: W
that make connections all over the world. Join today Download & Extend Drupal Core Distributions Modules Themes DependentIssues PostgreSQL syntax is wrong ActiveProject:DependentVersion:5.x-1.4-betaComponent:CodePriority:CriticalCategory:Bug reportAssigned:UnassignedReporter:phayesCreated:June 17, 2008 - 19:06Updated:June 22, 2008 - 13:00 Log in or register to update this issue This module will not install on a postgresql server. * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "not" LINE 1: CREATE TABLE if not exists field_dependencies ( ^ in C:\.........\includes\database.pgsql.inc on line 125. * user warning: query: CREATE TABLE if not exists field_dependencies ( parent_field_name varchar(32) NOT NULL, child_field_name varchar(32) NOT NULL, parent_node_type_name varchar(32) NOT NULL, child_node_type_name varchar(32) NOT NULL, option_text varchar(255), put_after_parent boolean default 0, is_required boolean default 0, PRIMARY KEY(parent_field_name,child_field_name,parent_node_type_name,child_node_type_name) ) TYPE=MyISAM /*!40100 DEFAULT CHARACTER SET utf8 */; in C:\......\includes\database.pgsql.inc on line 144. Comments Comment #1 phayes CreditAttribution: phayes commented June 17, 2008 at 7:15pm The correct code for postgres is as follows: db_query( "CREATE TABLE {field_dependencies} ( parent_field_name varchar(32) NOT NULL, child_field_name varchar(32) NOT NULL, parent_node_type_name varchar(32) NOT NULL, child_node_type_name varchar(32) NOT NULL, option_text varchar(255), put_after_parent int default 0, is_required int default 0, PRIMARY KEY(parent_field_name,child_field_name,parent_node_type_name,child_node_type_name) ) "); put_after_parent and is_required need to be int, not bool, since you cannot assign a integer to a boolean. Log in or register to post comments Comment #2 phayes CreditAttribution: phayes commented June 17, 2008 at 7:29pm More problems. SQL should never use double quotes when referencing strings - this works in MySQL but is non-standard and breaks more strict SQL databases like postgreSQL. The code as it currently stands tells postgresql to check for a column named "options_onoff" etc - which doesnt exist and so returns an error. The following lines of code need to be changed as follows to replace all double-quotes as single quotes. This should make both postgreSQL and MySQL happy. lines 165-173 if (isset($field_name)){ $onoff_field_instances=db_query("SELECT