Hi all,

I think there's an error on pg_dump, 
my environment is:
         Lynux 2.0.33
	 PostgreSQL 6.3

1) ----VARCHAR(-50)------------------------------------------

I created a table as:
CREATE TABLE utente (
	intestazione_azienda 	varchar,
	indirizzo 		varchar
	);

using pg_dump -d mydatabase > file

file is like:
\connect - postgres
CREATE TABLE utente (intestazione_azienda varchar(-5), indirizzo varchar(-5));

if I try to load it using
psql -d mydatabase < file
I have this:

ERROR:  length for 'varchar' type must be at least 1

2) ----CONSTRAINT--------------------------------------------

I created a table like:

CREATE TABLE attivita_a (
	azienda			CHAR(11) NOT NULL,
	attivita		CHAR(03) NOT NULL,
	operatore		CHAR(03),	
	vet_esterno		VARCHAR(45),
	tipo_allevamento1	CHAR(02),		
	tipo_allevamento2	CHAR(02),
	esonerato		CHAR CHECK(esonerato = 'S' OR esonerato = 'N'),
	razza_prevalente1	CHAR(03),	
	razza_prevalente2	CHAR(03),		
	iscrizione_libro	DATE,
	iscritta_funzionali	CHAR CHECK(iscritta_funzionali = 'S' OR iscritta_funzionali = 'N'),
	iscritta_tutela		CHAR CHECK(iscritta_tutela = 'S' OR iscritta_tutela = 'N'),
	sigla_tutela		CHAR(04),
	adesione_altri_piani	VARCHAR(50),
	data_adesione		DATE,
        PRIMARY KEY (azienda,attivita)
	);


using pg_dump I have this:

\connect - postgres
CREATE TABLE attivita_a (
 azienda 		char(11) NOT NULL,
 attivita 		char(3) NOT NULL,
 operatore 		char(3),
 vet_esterno 		varchar(45),
 tipo_allevamento1 	char(2),
 tipo_allevamento2 	char(2),
 esonerato 		char,
 razza_prevalente1 	char(3),
 razza_prevalente2 	char(3),
 iscrizione_libro 	date,
 iscritta_funzionali 	char,
 iscritta_tutela 	char,
 sigla_tutela 		char(4),
 adesione_altri_piani 	varchar(50),
 data_adesione date)
 CONSTRAINT attivita_a_esonerato CHECK esonerato = 'S' OR esonerato = 'N',
 CONSTRAINT attivita_a_iscritta_funzionali CHECK iscritta_funzionali = 'S' OR iscritta_funzionali = 'N',
 CONSTRAINT attivita_a_iscritta_tutela CHECK iscritta_tutela = 'S' OR iscritta_tutela = 'N';
--
Note that CONSTRAINTs are the wrong syntax, they are defined after the close
parenthesis of CREATE TABLE.

3)----VIEWS-------------------------------------------------
I have some views on my database but seems that pg_dump doesn't see those
views.
                                                                   Jose'


Tue, 19 May 1998 12:24:31 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
pg_dump error
> 
> Hi all,
> 
> I think there's an error on pg_dump, 
> my environment is:
>          Lynux 2.0.33
> 	 PostgreSQL 6.3
> 
> 1) ----VARCHAR(-50)------------------------------------------
> 
> I created a table as:
> CREATE TABLE utente (
> 	intestazione_azienda 	varchar,
> 	indirizzo 		varchar
> 	);
> 
> using pg_dump -d mydatabase > file
> 
> file is like:
> \connect - postgres
> CREATE TABLE utente (intestazione_azienda varchar(-5), indirizzo varchar(-5));

Basically, something major is wrong in your installation.  I have never
heard a report like this, and people use pg_dump all the time.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 15 Jun 1998 23:19:07 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_dump error
On Mon, 15 Jun 1998, Bruce Momjian wrote:

> > 
> > Hi all,
> > 
> > I think there's an error on pg_dump, 
> > my environment is:
> >          Lynux 2.0.33
> > 	 PostgreSQL 6.3
> > 
> > 1) ----VARCHAR(-50)------------------------------------------
> > 
> > I created a table as:
> > CREATE TABLE utente (
> > 	intestazione_azienda 	varchar,
> > 	indirizzo 		varchar
> > 	);
> > 
> > using pg_dump -d mydatabase > file
> > 
> > file is like:
> > \connect - postgres
> > CREATE TABLE utente (intestazione_azienda varchar(-5), indirizzo varchar(-5));
> 
> Basically, something major is wrong in your installation.  I have never
> heard a report like this, and people use pg_dump all the time.
> 
I have three bugs Bruce:

1)  VARCHAR(-5)
2)  CONSTRAINTs wrong syntax
3)  no VIEWs ??

hygea=> create table prova (var varchar, bp bpchar check (bp='zero'));
CREATE
hygea=> create view wprova as select var from prova;
CREATE

pg_dump hygea -s prova

\connect - postgres
CREATE TABLE prova (var varchar(-5), bp char(-5)) CONSTRAINT prova_bp CHECK bp
=COPY prova FROM stdin;
\.
                                                Jose'


Tue, 16 Jun 1998 11:18:16 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [HACKERS] pg_dump error

I (thought I) forwarded fixes for the pg_dump constraint syntax
bug to this list a couple of weeks ago.  I added a -c (compatible)
switch to pg_dump to force it to dump constraints in a syntax that
pgsql can understand.

Here's another copy of the diffs (against 6.3.2).

ccb

----------------
*** /usr/local/src/pgsql/6.3.2/src/bin/pg_dump/pg_dump.c	Thu Apr  9 19:02:24 1998
--- ./pg_dump.c	Tue Jun  9 14:27:36 1998
***************
*** 110,115 ****
--- 110,116 ----
  int			attrNames;			/* put attr names into insert strings */
  int			schemaOnly;
  int			dataOnly;
+ int                     compatConstraint;
  
  char		g_opaque_type[10];	/* name for the opaque type */
  
***************
*** 126,131 ****
--- 127,134 ----
  	fprintf(stderr,
  			"\t -a          \t\t dump out only the data, no schema\n");
  	fprintf(stderr,
+ 		        "\t -c          \t\t generate pgsql-compatible CONSTRAINT syntax\n");
+ 	fprintf(stderr,
  			"\t -d          \t\t dump data as proper insert strings\n");
  	fprintf(stderr,
  	  "\t -D          \t\t dump data as inserts with attribute names\n");
***************
*** 551,567 ****
  	g_comment_end[0] = '\0';
  	strcpy(g_opaque_type, "opaque");
  
! 	dataOnly = schemaOnly = dumpData = attrNames = 0;
  
  	progname = *argv;
  
! 	while ((c = getopt(argc, argv, "adDf:h:op:st:vzu")) != EOF)
  	{
  		switch (c)
  		{
  			case 'a':			/* Dump data only */
  				dataOnly = 1;
  				break;
  			case 'd':			/* dump data as proper insert strings */
  				dumpData = 1;
  				break;
--- 554,574 ----
  	g_comment_end[0] = '\0';
  	strcpy(g_opaque_type, "opaque");
  
! 	compatConstraint = dataOnly = schemaOnly = dumpData = attrNames = 0;
  
  	progname = *argv;
  
! 	while ((c = getopt(argc, argv, "acdDf:h:op:st:vzu")) != EOF)
  	{
  		switch (c)
  		{
  			case 'a':			/* Dump data only */
  				dataOnly = 1;
  				break;
+ 		        case 'c':                       /* generate constraint syntax that
+ 							    can be read back into postgreSQL */
+ 			        compatConstraint = 1;
+ 				break;
  			case 'd':			/* dump data as proper insert strings */
  				dumpData = 1;
  				break;
***************
*** 1496,1502 ****
  				query[0] = 0;
  				if (name[0] != '$')
  					sprintf(query, "CONSTRAINT %s ", name);
! 				sprintf(query, "%sCHECK %s", query, expr);
  				tblinfo[i].check_expr[i2] = strdup(query);
  			}
  			PQclear(res2);
--- 1503,1514 ----
  				query[0] = 0;
  				if (name[0] != '$')
  					sprintf(query, "CONSTRAINT %s ", name);
! 				if( compatConstraint ) {
! 				  sprintf(query, "%sCHECK (%s)", query, expr);
! 				}
! 				else {
! 				  sprintf(query, "%sCHECK %s", query, expr);
! 				}
  				tblinfo[i].check_expr[i2] = strdup(query);
  			}
  			PQclear(res2);
***************
*** 2518,2523 ****
--- 2530,2546 ----
  				}
  			}
  
+ 			if( compatConstraint ) {
+ 				/* put the CONSTRAINTS inside the table def */
+ 				for (k = 0; k < tblinfo[i].ncheck; k++)
+ 				{
+ 					sprintf(q, "%s%s %s",
+ 						q,
+ 						(actual_atts + k > 0) ? ", " : "",
+ 						tblinfo[i].check_expr[k]);
+ 				}
+ 			}
+ 
  			strcat(q, ")");
  
  			if (numParents > 0)
***************
*** 2533,2540 ****
  				strcat(q, ")");
  			}
  
! 			if (tblinfo[i].ncheck > 0)
  			{
  				for (k = 0; k < tblinfo[i].ncheck; k++)
  				{
  					sprintf(q, "%s%s %s",
--- 2556,2564 ----
  				strcat(q, ")");
  			}
  
! 			if( !compatConstraint )
  			{
+ 				/* put the CONSTRAINT defs outside the table def */
  				for (k = 0; k < tblinfo[i].ncheck; k++)
  				{
  					sprintf(q, "%s%s %s",
***************
*** 2543,2548 ****
--- 2567,2573 ----
  							tblinfo[i].check_expr[k]);
  				}
  			}
+ 
  			strcat(q, ";\n");
  			fputs(q, fout);
  			if (acls)

Wed, 17 Jun 1998 11:10:34 -0300
Charles Bennett <ccb@pubweb.net>
Re: [BUGS] Re: [HACKERS] pg_dump error 
> 
> 
> I (thought I) forwarded fixes for the pg_dump constraint syntax
> bug to this list a couple of weeks ago.  I added a -c (compatible)
> switch to pg_dump to force it to dump constraints in a syntax that
> pgsql can understand.
> 
> Here's another copy of the diffs (against 6.3.2).
> 

I just applied this patch a few days ago.  I e-mailed you asking why
there is an option for this behavour.  Seems like it should always be
on.

Please let me know.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Wed, 17 Jun 1998 18:54:45 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [BUGS] Re: [HACKERS] pg_dump error
On Wed, 17 Jun 1998, Charles Bennett wrote:

> 
> I (thought I) forwarded fixes for the pg_dump constraint syntax
> bug to this list a couple of weeks ago.  I added a -c (compatible)
> switch to pg_dump to force it to dump constraints in a syntax that
> pgsql can understand.
> 
> Here's another copy of the diffs (against 6.3.2).
> 
> ccb
I applied your patch, Charles and it works, obviouly I remove the -c parameter
because there isn't another syntax for CONSTRAINTs. PostgreSQL has
the SQL92 syntax.
                                       Thanks, Jose'


Thu, 18 Jun 1998 10:27:01 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [BUGS] Re: [HACKERS] pg_dump error 

Bruce Momjian said:

> I just applied this patch a few days ago.  I e-mailed you asking why
> there is an option for this behavour.  Seems like it should always be
> on.
> 
> Please let me know.


Sorry I missed the mail...

I set this up as an option because I though the initial behavior
might have been put in for a reason - one that I didn't understand.
I have no objection if you decide to make PGSQL-compatible dump
syntax the default.

ccb

---
Charles C. Bennett, Jr.			PubWeb, Inc.
Software Engineer			The Publishing <-> Printing Network
Agent of Disintermediation		4A Gill St.
ccb@pubweb.net				Woburn, MA 01801

Thu, 18 Jun 1998 14:35:37 -0300
Charles Bennett <ccb@pubweb.net>
Re: [BUGS] Re: [HACKERS] pg_dump error 
> 
> On Wed, 17 Jun 1998, Charles Bennett wrote:
> 
> > 
> > I (thought I) forwarded fixes for the pg_dump constraint syntax
> > bug to this list a couple of weeks ago.  I added a -c (compatible)
> > switch to pg_dump to force it to dump constraints in a syntax that
> > pgsql can understand.
> > 
> > Here's another copy of the diffs (against 6.3.2).
> > 
> > ccb
> I applied your patch, Charles and it works, obviouly I remove the -c parameter
> because there isn't another syntax for CONSTRAINTs. PostgreSQL has
> the SQL92 syntax.

OK, I have removed the -c syntax for pg_dump, so all dumps now use the
new format.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 19 Jun 1998 22:49:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [BUGS] Re: [HACKERS] pg_dump error
> 
> 
> Bruce Momjian said:
> 
> > I just applied this patch a few days ago.  I e-mailed you asking why
> > there is an option for this behavour.  Seems like it should always be
> > on.
> > 
> > Please let me know.
> 
> 
> Sorry I missed the mail...
> 
> I set this up as an option because I though the initial behavior
> might have been put in for a reason - one that I didn't understand.
> I have no objection if you decide to make PGSQL-compatible dump
> syntax the default.

Done.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 19 Jun 1998 23:01:21 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [BUGS] Re: [HACKERS] pg_dump error