[BUGS] BUG #2100: CREATE TABLE AS - may not supply table specification
The following bug has been logged online: Bug reference: 2100 Logged by: Robert Bengtsson Email address: [EMAIL PROTECTED] PostgreSQL version: PostgreSQL Data Operating system: Windows Server 2003, Web Edition Description:CREATE TABLE AS - may not supply table specification Details: CREATE TABLE AS seems to be broken. The following syntax works: CREATE TABLE l_modeltext WITHOUT OIDS AS (SELECT distinct modeltext as text, make, model, make_id, model_id from l_modelcode); while the following generates an error: CREATE TABLE l_modeltext ( id serial NOT NULL, text varchar(60), make varchar(30), model varchar(30), make_id int4, model_id int4 ) WITHOUT OIDS AS (SELECT distinct modeltext as text, make, model, make_id, model_id from l_modelcode); with the following errorcode: ERROR: syntax error at or near "AS" at character 94 However, the errorcode is, strangely enough, somewhat dependent on where the edit-cursor is placed inside pgAdmin. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2099: too many private dirs ?
Hi, I've found the following additional lines in postmaster.log 2005-12-08 15:42:02 ERROR: too many private dirs demanded 2005-12-08 15:42:03 ERROR: too many private dirs demanded 2005-12-08 15:42:04 PANIC: too many private files demanded 2005-12-08 15:42:04 CONTEXT: writing block 236 of relation 1663/19946/20844 This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2005-12-08 15:42:04 LOG: background writer process (PID 5748) was terminated by signal 3 2005-12-08 15:42:04 LOG: terminating any other active server processes 2005-12-08 15:42:04 WARNING: terminating connection because of crash of another server process 2005-12-08 15:42:04 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2005-12-08 15:42:04 HINT: In a moment you should be able to reconnect to the database and repeat your command. Hope this helps regards Tom Tom Lane <[EMAIL PROTECTED]> 08.12.2005 16:57 An: "Tom" <[EMAIL PROTECTED]> Kopie: pgsql-bugs@postgresql.org Thema: Re: [BUGS] BUG #2099: too many private dirs ? "Tom" <[EMAIL PROTECTED]> writes: > we got the following error in our environment: > 2005-12-08 13:43:04 ERROR: too many private dirs demanded > 2005-12-08 13:43:04 ERROR: checkpoint request failed > 2005-12-08 13:43:04 HINT: Consult the server log for details. I can see a possible mechanism for this, but it requires assuming that there have been repeated prior failures in the bgwriter/checkpointer. What else is in the postmaster log? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2104: pg_xlog/ trace files not reclaimed by server
The following bug has been logged online: Bug reference: 2104 Logged by: Reuben Pasquini Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: linux redhat-4.1 Description:pg_xlog/ trace files not reclaimed by server Details: Hello! It appears that when my 8.1 database is forced to generate more than 2*checkpoint_segments + 1 trace files under pg_xlog/, that the database becomes confused and stops recycling the trace files. Unfortunately - I haven't been able to catch this condition until after it filled up the disk (hundreds of trace files) and things crashed. I did not have this problem running postgres-7.4, but performance was not nearly so good as 8.1. I'm running a high-transaction postgres-8.1 database on a 2-proc linux box - looks like 4 procs due to hyperthreading: Main memory size: 3931 Mbytes 4 GenuineIntel Intel(R) Xeon(TM) CPU 2.80GHz processors Attached below is the postgresql.conf file I was running with at the time of the last crash. I've had a few crashes like this, but only diagnosed the problem recently. I increased the checkpoint-segments to 30 after the last crash early thismorning to see if that works around the problem. Thanks for the help! Reuben --- # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload". Some settings, such as listen_address, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 100 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 2 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #bonjour_name = '' # defaults to the computer name # - Security & Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry #krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 22 # min 16 or max_
[BUGS] BUG #2103: System column oid is not unique
The following bug has been logged online:
Bug reference: 2103
Logged by: wangshj
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.0
Operating system: CentOS 4
Description:System column oid is not unique
Details:
The following is my test.
oidtest=# CREATE TABLE oidtest( name text ) WITH OIDS;
CREATE TABLE
oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name1' ) ;
INSERT 54512 1
oidtest=# COPY oidtest (name) WITH OIDS FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
54513copyname
\.
oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name2' ) ;
INSERT 54513 1
oidtest=# SELECT oid,name from oidtest ;
oid | name
---+--
54512 | name1
54513 | copyname
54513 | name2
(3 rows)
oidtest=#
Then I got two records with same oid(54513).
In the second test, I created two tables and added primary key (oid) to
them. As below:
oidtest=# CREATE TABLE oidtestpk (name text, PRIMARY KEY (oid) ) WITH OIDS
;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"oidtestpk_pkey" for table "oidtestpk"
CREATE TABLE
oidtest=# CREATE TABLE oidtestpk_another (name text, PRIMARY KEY (oid) )
WITH OIDS ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"oidtestpk_another_pkey" for table "oidtestpk_another"
CREATE TABLE
oidtest=# INSERT INTO oidtestpk(name) VALUES ('name0');
INSERT 54562 1
oidtest=# COPY oidtestpk(name) WITH OIDS FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
54564copyname64
\.
oidtest=# INSERT INTO oidtestpk_another(name) VALUES ('name63');
INSERT 54563 1
oidtest=# INSERT INTO oidtestpk_another(name) VALUES ('name64');
INSERT 54564 1
oidtest=# SELECT oid,name FROM oidtestpk where oid=54564;
oid |name
---+
54564 | copyname64
(1 row)
oidtest=# SELECT oid,name FROM oidtestpk_another where oid=54564;
oid | name
---+
54564 | name64
(1 row)
And then I got records with same oid(54564).
Is it a bug?
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[BUGS] BUG #2101: Postmaster Crash Using PL/PgSQL With %TYPE on Non-Exisiting Column
The following bug has been logged online: Bug reference: 2101 Logged by: Will Szopko Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.0 Operating system: Debain Linux (Sarge)/Mac OS X 10.3.9 (Panther) Description:Postmaster Crash Using PL/PgSQL With %TYPE on Non-Exisiting Column Details: The postmaster in 8.1.0 crashes when creating or replacing a PL/PgSQL function containing an invalid variable declaration. The invalid declaration triggering the bug combines a schema with a %TYPE applied to a non-existing column. When a schema is not involved an appropriate error message is given. I was able to reproduce the problem on both Mac OS X 10.3.9 and Debian Sarge on x86. The following illustrates the bug. [EMAIL PROTECTED]:~$ createdb trash CREATE DATABASE [EMAIL PROTECTED]:~$ cat test.sql CREATE SCHEMA junk; SELECT version(); CREATE TABLE junk.my_table ( my_table_id SERIAL NOT NULL PRIMARY KEY, my_column INTEGER NOT NULL DEFAULT 0); CREATE OR REPLACE FUNCTION show_bug() RETURNS BOOLEAN AS $PROC$ DECLARE myColumn junk.my_table.bad_column%TYPE; BEGIN RETURN true; END; $PROC$ LANGUAGE 'plpgsql'; [EMAIL PROTECTED]:~$ psql -f ./test.sql trash CREATE SCHEMA version PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13) (1 row) psql:./test.sql:7: NOTICE: CREATE TABLE will create implicit sequence "my_table_my_table_id_seq" for serial column "my_table.my_table_id" psql:./test.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "my_table_pkey" for table "my_table" CREATE TABLE psql:./test.sql:17: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:./test.sql:17: connection to server was lost [EMAIL PROTECTED]:~$ dropdb trash DROP DATABASE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] pgsql8.1: About COPY Command and system clomn oid
The following is my test.
oidtest=# CREATE TABLE oidtest( name text ) WITH OIDS;
CREATE TABLE
oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name1' ) ;
INSERT 54512 1
oidtest=# COPY oidtest (name) WITH OIDS FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 54513copyname
>> \.
oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name2' ) ;
INSERT 54513 1
oidtest=# SELECT oid,name from oidtest ;
oid | name
---+--
54512 | name1
54513 | copyname
54513 | name2
(3 rows)
oidtest=#
Then I got two records with same oid(54513).
In the second test, I created two tables and added primary key (oid) to
them. As below:
oidtest=# CREATE TABLE oidtestpk (name text, PRIMARY KEY (oid) ) WITH OIDS ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"oidtestpk_pkey" for table "oidtestpk"
CREATE TABLE
oidtest=# CREATE TABLE oidtestpk_another (name text, PRIMARY KEY (oid) )
WITH OIDS ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"oidtestpk_another_pkey" for table "oidtestpk_another"
CREATE TABLE
oidtest=# INSERT INTO oidtestpk(name) VALUES ('name0');
INSERT 54562 1
oidtest=# COPY oidtestpk(name) WITH OIDS FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 54564copyname64
>> \.
oidtest=# INSERT INTO oidtestpk_another(name) VALUES ('name63');
INSERT 54563 1
oidtest=# INSERT INTO oidtestpk_another(name) VALUES ('name64');
INSERT 54564 1
oidtest=# SELECT oid,name FROM oidtestpk where oid=54564;
oid |name
---+
54564 | copyname64
(1 row)
oidtest=# SELECT oid,name FROM oidtestpk_another where oid=54564;
oid | name
---+
54564 | name64
(1 row)
And then I got records with same oid(54564).
Is it a bug?
...
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] There is a doubt of fatal bug on PostgreSQL 8.0.4.
What locale is the database using? Strange comparison behavior is fairly common if the locale expects a different encoding than you are using... locale is 'C'. client_encoding is 'SJIS'. Locale 'C' is the same settings when I used version 8.0.3. I think this is right..Or should I appoint "no locale" when I initialize database? regards, Kenichi Kasahara _ 無料で250MBの受信ボックスが使える「MSN Hotmail」 http://promotion.msn.co.jp/hotmail/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2102: Backend reports wrong number of affected rows for a table that uses rules
The following bug has been logged online: Bug reference: 2102 Logged by: Brent Reid Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.2 Operating system: Linux dl380 2.6.14.2 #2 SMP Wed Nov 16 09:51:56 MST 2005 i686 i686 i386 GNU/Linux Description:Backend reports wrong number of affected rows for a table that uses rules Details: Our Java application depends upon the return values from the various JDBC insert, update, and delete routines. We noticed that the value is always zero when the table that is referenced has rules associated with it. In particular, we do an update and if the return value is zero, we then insert the row, generally the row already exists. We are getting duplicate key errors on the insert. See this link http://archives.postgresql.org/pgsql-jdbc/2005-12/msg00020.php The following demonstrates the issue: c60=# begin work; BEGIN c60=# create table foo( col1 int primary key, col2 text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE c60=# CREATE TABLE foo1 () INHERITS (foo) WITHOUT OIDS; CREATE TABLE c60=# ALTER TABLE foo1 ADD PRIMARY KEY (col1); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo1_pkey" for table "foo1" ALTER TABLE c60=# CREATE TABLE foo2 () INHERITS (foo) WITHOUT OIDS; CREATE TABLE c60=# ALTER TABLE foo2 ADD PRIMARY KEY (col1); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo2_pkey" for table "foo2" ALTER TABLE c60=#-- Perform DML on foo without any rules c60=# insert into foo values ( 1, 'abc'); INSERT 24731 1 -- The value returned from the insert is the number of rows inserted c60=# select * from foo; col1 | col2 --+-- 1 | abc (1 row) c60=# update foo set col2 = 'xyz' where col1 = 1; UPDATE 1 -- Notice the 1 following UPDATE above c60=# select * from foo; col1 | col2 --+-- 1 | xyz (1 row) c60=# delete from foo where col1 = 1; DELETE 1 -- Notice the 1 following DELETE above c60=# select * from foo; col1 | col2 --+-- (0 rows) c60=#-- Now add some rules c60=# CREATE OR REPLACE RULE insrule1 AS ON INSERT TO foo WHERE ((new.col1::bigint & 4096::bigint) = 0) DO INSTEAD INSERT INTO foo1 VALUES(new.*); CREATE RULE c60=# CREATE OR REPLACE RULE insrule2 AS ON INSERT TO foo WHERE ((new.col1::bigint & 4096::bigint) = 4096) DO INSTEAD INSERT INTO foo2 VALUES(new.*); CREATE RULE c60=# CREATE OR REPLACE RULE updrule1 AS ON UPDATE TO foo WHERE ((old.col1::bigint & 4096::bigint) = 0) DO INSTEAD UPDATE foo1 SET col1 = new.col1, col2 = new.col2 WHERE col1 = old.col1; CREATE RULE c60=# CREATE OR REPLACE RULE updrule2 AS ON UPDATE TO foo WHERE ((old.col1::bigint & 4096::bigint) = 4096) DO INSTEAD UPDATE foo2 SET col1 = new.col1, col2 = new.col2 WHERE col1 = old.col1; CREATE RULE c60=# CREATE OR REPLACE RULE delrule1 AS ON DELETE TO foo WHERE ((old.col1::bigint & 4096::bigint) = 0) DO INSTEAD DELETE FROM foo1 WHERE col1 = old.col1; CREATE RULE c60=# CREATE OR REPLACE RULE delrule2 AS ON DELETE TO foo WHERE ((old.col1::bigint & 4096::bigint) = 4096) DO INSTEAD DELETE FROM foo2 WHERE col1 = old.col1; CREATE RULE c60=# c60=# insert into foo values ( 1, 'abc'); INSERT 0 0 -- Notice the zeros c60=# select * from foo; col1 | col2 --+-- 1 | abc (1 row) c60=# update foo set col2 = 'xyz' where col1 = 1; UPDATE 0 -- Notice the zeros c60=# select * from foo; col1 | col2 --+-- 1 | xyz (1 row) c60=# delete from foo where col1 = 1; DELETE 0 -- Notice the zeros c60=# select * from foo; col1 | col2 --+-- (0 rows) c60=#-- Now lets try one of the inherited tables c60=# insert into foo1 values ( 1, 'abc'); INSERT 24739 1 -- Notice the lack of zeros c60=# select * from foo1; col1 | col2 --+-- 1 | abc (1 row) c60=# update foo1 set col2 = 'xyz' where col1 = 1; UPDATE 1 -- Notice the lack of zeros c60=# select * from foo1; col1 | col2 --+-- 1 | xyz (1 row) c60=# delete from foo1 where col1 = 1; DELETE 1 -- Notice the lack of zeros c60=# select * from foo1; col1 | col2 --+-- (0 rows) c60=# c60=# rollback work; ROLLBACK c60=# c60=# ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2100: CREATE TABLE AS - may not supply table specification
"Robert Bengtsson" <[EMAIL PROTECTED]> writes: > while the following generates an error: > CREATE TABLE l_modeltext > ( > id serial NOT NULL, > text varchar(60), > make varchar(30), > model varchar(30), > make_id int4, > model_id int4 > ) WITHOUT OIDS AS (SELECT distinct modeltext as text, make, model, make_id, > model_id from l_modelcode); That is not the syntax for CREATE TABLE AS, and I don't see anything in the reference page for CREATE TABLE AS that would suggest that it is. I'd suggest creating the table with the columns you want and then doing INSERT ... SELECT to fill it. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2104: pg_xlog/ trace files not reclaimed by server
"Reuben Pasquini" <[EMAIL PROTECTED]> writes: > It appears that when my 8.1 database is forced > to generate more than > 2*checkpoint_segments + 1 > trace files under pg_xlog/, that the database > becomes confused and stops recycling the trace files. That's fairly hard to believe, especially since you haven't presented any actual evidence of it. What might have happened is that checkpoints were failing for some reason and so recycling of WAL segments couldn't be performed. Was there anything in the postmaster log about write failures? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2103: System column oid is not unique
"wangshj" <[EMAIL PROTECTED]> writes: > Description:System column oid is not unique There isn't, and never has been nor will be, any promise that it is, except to the extent that uniqueness is enforced by a unique index on a particular table. OID is just a kind of serial sequence generator. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2101: Postmaster Crash Using PL/PgSQL With %TYPE on Non-Exisiting Column
"Will Szopko" <[EMAIL PROTECTED]> writes: > The postmaster in 8.1.0 crashes when creating or replacing a PL/PgSQL > function containing an invalid variable declaration. Reproduced here --- thanks for the test case! regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2102: Backend reports wrong number of affected rows for a table that uses rules
"Brent Reid" <[EMAIL PROTECTED]> writes: > Our Java application depends upon the return values from the various JDBC > insert, update, and delete routines. We noticed that the value is always > zero when the table that is referenced has rules associated with it. Have you read http://www.postgresql.org/docs/8.0/static/rules-status.html regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2101: Postmaster Crash Using PL/PgSQL With %TYPE on Non-Exisiting Column
"Will Szopko" <[EMAIL PROTECTED]> writes: > The postmaster in 8.1.0 crashes when creating or replacing a PL/PgSQL > function containing an invalid variable declaration. Looks like a copy-and-paste mistake... Index: pl_comp.c === RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/pl_comp.c,v retrieving revision 1.94.2.1 diff -c -r1.94.2.1 pl_comp.c *** pl_comp.c 22 Nov 2005 18:23:30 - 1.94.2.1 --- pl_comp.c 9 Dec 2005 16:56:38 - *** *** 1457,1463 done: if (HeapTupleIsValid(classtup)) ReleaseSysCache(classtup); ! if (HeapTupleIsValid(classtup)) ReleaseSysCache(attrtup); if (HeapTupleIsValid(typetup)) ReleaseSysCache(typetup); --- 1457,1463 done: if (HeapTupleIsValid(classtup)) ReleaseSysCache(classtup); ! if (HeapTupleIsValid(attrtup)) ReleaseSysCache(attrtup); if (HeapTupleIsValid(typetup)) ReleaseSysCache(typetup); regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2100: CREATE TABLE AS - may not supply table specification
Robert Bengtsson wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2100
> Logged by: Robert Bengtsson
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: PostgreSQL Data
> Operating system: Windows Server 2003, Web Edition
> Description:CREATE TABLE AS - may not supply table specification
> Details:
>
> CREATE TABLE AS seems to be broken.
>
> The following syntax works:
>
> CREATE TABLE l_modeltext WITHOUT OIDS AS (SELECT distinct modeltext as text,
> make, model, make_id, model_id from l_modelcode);
>
> while the following generates an error:
>
> CREATE TABLE l_modeltext
> (
> id serial NOT NULL,
> text varchar(60),
> make varchar(30),
> model varchar(30),
> make_id int4,
> model_id int4
> ) WITHOUT OIDS AS (SELECT distinct modeltext as text, make, model, make_id,
> model_id from l_modelcode);
>
> with the following errorcode:
>
> ERROR: syntax error at or near "AS" at character 94
While you can specify the column names of the new table in CREATE TABLE
AS, you can not specify the column _types_ of the new columns:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query
The column types must match the column types of the original table. The
fact that "AS" it targeted as the error location is expected, because it
is the place where the command is switched from CREATE TABLE to CREATE
TABLE AS. See this:
CREATE TABLE l_modeltext
(
id serial NOT NULL,
text varchar(60),
make varchar(30),
model varchar(30),
make_id int4,
model_id int4
) AS SELECT 1;
"AS" is where I get the error message.
> However, the errorcode is, strangely enough, somewhat dependent on where the
> edit-cursor is placed inside pgAdmin.
That is strange.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] There is a doubt of fatal bug on PostgreSQL 8.0.4.
"Kasahara Kenichi" <[EMAIL PROTECTED]> writes: >> What locale is the database using? Strange comparison behavior is >> fairly common if the locale expects a different encoding than you >> are using... > locale is 'C'. Hm, it should work OK with C locale. I get apparently-correct output on my 8.0 installation: CREATE TABLE INSERT 155246 1 INSERT 155247 1 INSERT 155248 1 id | name +-- 1 | $B$"$"$"(B (1 row) id | name +-- (0 rows) > client_encoding is 'SJIS'. Um ... you said EUC_JP before. But I get the same result either way. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] [PATCHES] Patch to allow contrib/pgbench files to have blank lines
On Wed, Nov 23, 2005 at 10:24:00PM +0900, Tatsuo Ishii wrote:
> > Having blank lines in -f scripts was causing silent failures.
> > This fixes it, for some value of "fixes." If it's OK, please
> > apply to 8.1 CURRENT and CVS TIP :)
>
> Thanks. I have committed your patches to current and 8.1 stable.
Please apply the following patch that removes an unneeded (and broken)
counter increment.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
Index: contrib/pgbench/pgbench.c
===
RCS file: /projects/cvsroot/pgsql/contrib/pgbench/pgbench.c,v
retrieving revision 1.48
diff -c -r1.48 pgbench.c
*** contrib/pgbench/pgbench.c 23 Nov 2005 12:19:12 - 1.48
--- contrib/pgbench/pgbench.c 9 Dec 2005 23:25:47 -
***
*** 895,901
return false;
}
} else {
- lineno++;
continue;
}
--- 895,900
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] [PATCHES] Patch to allow contrib/pgbench files to have blank lines
David Fetter <[EMAIL PROTECTED]> writes: > Please apply the following patch that removes an unneeded (and broken) > counter increment. Done. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
