Re: Comments in .sql files

Hi,

Still working on converting our schemas over to postgres from mysql, and trying
to get the hang of the differences between the two products.

I'm trying to figure out how to put comments in our .sql schema files.
For example:

a.sql
-----------------------
drop table A;
create table A (
id SERIAL PRIMARY KEY,
foo int default 5,
bar int default 10
);

# This is a comment
insert into A (foo, bar) values (1, 1);
insert into A (foo, bar) values (2, 2);
-------------------------

Running "psql --username=user -d DB < a.sql" yields the following result:

DROP TABLE
NOTICE: CREATE TABLE will create implicit sequence 'a_id_seq' for SERIAL column 'a.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey' for table 'a'
CREATE TABLE
ERROR: parser: parse error at or near "#" at character 1
INSERT 66820 1

------------------

And the DB only shows the second entry:

Portal=# select * from A;
id | foo | bar
----+-----+-----
1 | 2 | 2
(1 row)

----------------

So, my questions are:

1. It obviously doesn't like the # notation for comments. What is the proper
way to put comments in schema files?

2. Why does postgres ignore the first insert? Shouldn't it just barf on
the "#" line and keep going?

3. Is there a way to turn of the notices about creating implicit sequences
and indices?

Thanks,

Chad

"Chad N. Tindel" <chad(at)tindel(dot)net> writes:
> 1. It obviously doesn't like the # notation for comments. What is the proper
> way to put comments in schema files?

The SQL-standard comment syntaxes are

-- comment to end of line

/* C-like comment, possibly multiple lines */

We do both. There are a lot of not-SQL-standard comment syntaxes
accepted by various other databases ...

> 2. Why does postgres ignore the first insert? Shouldn't it just barf on
> the "#" line and keep going?

Since it doesn't think # is a comment introducer, it sees an invalid
multiline statement running up to the first ';'.

> 3. Is there a way to turn of the notices about creating implicit sequences
> and indices?

Set client_min_messages higher than NOTICE.

regards, tom lane

> 1. It obviously doesn't like the # notation for comments. What is the
proper
> way to put comments in schema files?

Start the comment with two hyphens, like this:

-- This is a comment. It will continue up to the newline.

>
> 2. Why does postgres ignore the first insert? Shouldn't it just barf on
> the "#" line and keep going?

Since PG isn't parsing the '#', it discards all entry up to (what it thinks
is) the end of the statement, i.e. up to the next semicolon. Bear in mind
that newlines are, in general, treated like whitespace, so they don't have
any syntactic value.

hth,
cl.

On Friday 01 August 2003 04:54, Claudio Lapidus wrote:
> > 1. It obviously doesn't like the # notation for comments. What is the
>
> proper
>
> > way to put comments in schema files?
>
> Start the comment with two hyphens, like this:
>
> -- This is a comment. It will continue up to the newline.

Note that you can also do multi-line comments

/*
like this
*/

That's not SQL standard AFAIK, but can be useful for commenting out sections
of a data dump.
--
Richard Huxton
Archonet Ltd

/*
Check to see if a person's code is correct
*/

My Postgres 7.3.2 install chokes on the ' when trying to load a function
from psql \i.

Rory

On 31/07/03, Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> "Chad N. Tindel" <chad(at)tindel(dot)net> writes:
> > 1. It obviously doesn't like the # notation for comments. What is the proper
> > way to put comments in schema files?
>
> The SQL-standard comment syntaxes are
>
> -- comment to end of line
>
> /* C-like comment, possibly multiple lines */

--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>

Richard Huxton <dev(at)archonet(dot)com> writes:
> Note that you can also do multi-line comments

> /*
> like this
> */

> That's not SQL standard AFAIK,

It is standard in SQL99, but I don't see it in SQL92.

regards, tom lane

Rory Campbell-Lange <rory(at)campbell-lange(dot)net> writes:
> /*
> Check to see if a person's code is correct
> */

> My Postgres 7.3.2 install chokes on the ' when trying to load a function
> from psql \i.

Works fine for me. Could we see a complete example?

regards, tom lane