pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg with different sql?
5+ messages / 5 participants
[nested] [flat]

* pg with different sql?
@ 2002-01-13 22:04 Paulo J. Matos <pocm@rnl.ist.utl.pt>
  2002-01-13 22:38 ` Re: pg with different sql? Ian Barwick <barwick@gmx.net>
  2002-01-13 22:38 ` Re: pg with different sql? Jean-Luc Lachance <jllachan@nsd.ca>
  2002-01-13 22:39 ` Re: pg with different sql? Tom Lane <tgl@sss.pgh.pa.us>
  2002-01-13 23:01 ` Re: pg with different sql? George Moga <george@dsn.ro>
  0 siblings, 4 replies; 5+ messages in thread

From: pocm@rnl.ist.utl.pt @ 2002-01-13 22:04 UTC (permalink / raw)
  To: pgsql-sql

Hi all,

I'm new to postgres and I'm doing a database project for a
subject at college in postgres. I've studied databases using
DataBases Management Systems by Ramakrishnan.
I've done some create tables and at psql interactive prompt I've
done:
\i proj.sql
(where proj.sql is the filename with the CREATE TABLEs)

AFAIK, SQL is standard and should be the same for all DBMSs.

For example:
CREATE TABLE socio (
	id integer SERIAL PRIMARY KEY,
	nome varchar,
	alcunha string,
	data_filiacao date)

CREATE TABLE paga_cota (
	ano integer,
	mes integer,
	valor integer,
	id integer,
	PRIMARY KEY(id,ano,mes),
	FOREIGN KEY(id) REFERENCES(socio))


After this I get an error around CREATE (of the 2nd table). I
just can't imagine what it might be. 

I'd appreciate your help.

TIA,
My best regards,
-- 
Paulo J. Matos : pocm(_at_)rnl.ist.utl.pt
Instituto Superior Tecnico - Lisbon    
Software & Computer Engineering - A.I.
 - > http://www.rnl.ist.utl.pt/~pocm 
 ---	
	Yes, God had a deadline...
		So, He wrote it all in Lisp!




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: pg with different sql?
  2002-01-13 22:04 pg with different sql? Paulo J. Matos <pocm@rnl.ist.utl.pt>
@ 2002-01-13 22:38 ` Ian Barwick <barwick@gmx.net>
  3 siblings, 0 replies; 5+ messages in thread

From: Ian Barwick @ 2002-01-13 22:38 UTC (permalink / raw)
  To: pocm@rnl.ist.utl.pt; pgsql-sql

On Sunday 13 January 2002 23:04, Paulo J. Matos wrote:
> Hi all,
>
> I'm new to postgres and I'm doing a database project for a
> subject at college in postgres. I've studied databases using
> DataBases Management Systems by Ramakrishnan.
> I've done some create tables and at psql interactive prompt I've
> done:
> \i proj.sql
> (where proj.sql is the filename with the CREATE TABLEs)
>
> AFAIK, SQL is standard and should be the same for all DBMSs.

Happy April 1st! ;-)

Your statement would be true if you interpret it thus:

"There are SQL standards which in an ideal world would be 
implemented uniformly by all (R)DBMSs".

Unfortunately, in the real world each system has its own
very individual view of SQL, meaning only the most
basic statements are universally executable. Postgres
is pretty close to SQL92 AFAIK.

> For example:
> CREATE TABLE socio (
> 	id integer SERIAL PRIMARY KEY,
> 	nome varchar,
> 	alcunha string,
> 	data_filiacao date)
>
> CREATE TABLE paga_cota (
> 	ano integer,
> 	mes integer,
> 	valor integer,
> 	id integer,
> 	PRIMARY KEY(id,ano,mes),
> 	FOREIGN KEY(id) REFERENCES(socio))
>
>
> After this I get an error around CREATE (of the 2nd table). I
> just can't imagine what it might be.

Nor can anyone else, if you don't provide the error message...

Just a wild guess, but try reading up on the SERIAL and STRING
data types ;-)

The docs are a good starting point:

http://www.postgresql.org/idocs/

particularly the SQL reference:

http://www.postgresql.org/idocs/index.php?reference.html


HTH 

Ian Barwick



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: pg with different sql?
  2002-01-13 22:04 pg with different sql? Paulo J. Matos <pocm@rnl.ist.utl.pt>
@ 2002-01-13 22:38 ` Jean-Luc Lachance <jllachan@nsd.ca>
  3 siblings, 0 replies; 5+ messages in thread

From: Jean-Luc Lachance @ 2002-01-13 22:38 UTC (permalink / raw)
  To: Paulo J. Matos <pocm@rnl.ist.utl.pt>; +Cc: pgsql-sql

You should add a semi-colon at the end of each statement.

"Paulo J. Matos" wrote:
> 
> Hi all,
> 
> I'm new to postgres and I'm doing a database project for a
> subject at college in postgres. I've studied databases using
> DataBases Management Systems by Ramakrishnan.
> I've done some create tables and at psql interactive prompt I've
> done:
> \i proj.sql
> (where proj.sql is the filename with the CREATE TABLEs)
> 
> AFAIK, SQL is standard and should be the same for all DBMSs.
> 
> For example:
> CREATE TABLE socio (
>         id integer SERIAL PRIMARY KEY,
>         nome varchar,
>         alcunha string,
>         data_filiacao date)
> 
> CREATE TABLE paga_cota (
>         ano integer,
>         mes integer,
>         valor integer,
>         id integer,
>         PRIMARY KEY(id,ano,mes),
>         FOREIGN KEY(id) REFERENCES(socio))
> 
> After this I get an error around CREATE (of the 2nd table). I
> just can't imagine what it might be.
> 
> I'd appreciate your help.
> 
> TIA,
> My best regards,
> --
> Paulo J. Matos : pocm(_at_)rnl.ist.utl.pt
> Instituto Superior Tecnico - Lisbon
> Software & Computer Engineering - A.I.
>  - > http://www.rnl.ist.utl.pt/~pocm
>  ---
>         Yes, God had a deadline...
>                 So, He wrote it all in Lisp!
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: pg with different sql?
  2002-01-13 22:04 pg with different sql? Paulo J. Matos <pocm@rnl.ist.utl.pt>
@ 2002-01-13 22:39 ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2002-01-13 22:39 UTC (permalink / raw)
  To: pocm@rnl.ist.utl.pt; +Cc: pgsql-sql

pocm@rnl.ist.utl.pt (Paulo J. Matos) writes:
> AFAIK, SQL is standard and should be the same for all DBMSs.

...rotfl...

The standard is what it is, but almost everybody has their own set of
extensions, variations, etc.  You seem to have acquired a few
nonstandardisms from your previous exposure.

> CREATE TABLE socio (
> 	id integer SERIAL PRIMARY KEY,
> 	nome varchar,
> 	alcunha string,
> 	data_filiacao date)

integer OR serial, please, not both.  (Serial isn't standard, btw.)

"string" is not a datatype known to Postgres.  Try "text",
or if you want to actually conform to the standard, "varchar(N)".
(varchar with no length limit, as in your second field, isn't
standard either.)

Also, if you're entering these commands via psql, you need a semicolon
after each one.

> CREATE TABLE paga_cota (
> 	ano integer,
> 	mes integer,
> 	valor integer,
> 	id integer,
> 	PRIMARY KEY(id,ano,mes),
> 	FOREIGN KEY(id) REFERENCES(socio))

By my reading of the SQL spec the last line should be

	FOREIGN KEY(id) REFERENCES socio)

There's nothing I can see in the SQL92 syntax that allows parentheses
around the name of the referenced table.

If this stuff works as-is on some other RDBMS, then you've learned
some nonstandard habits.  We're not any better; we've got our own
set of nonstandardisms.  Just not those ones.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: pg with different sql?
  2002-01-13 22:04 pg with different sql? Paulo J. Matos <pocm@rnl.ist.utl.pt>
@ 2002-01-13 23:01 ` George Moga <george@dsn.ro>
  3 siblings, 0 replies; 5+ messages in thread

From: George Moga @ 2002-01-13 23:01 UTC (permalink / raw)
  To: pgsql-sql

Paulo J. Matos wrote:

 > Hi all,
 > CREATE TABLE socio (
 > 	id integer SERIAL PRIMARY KEY,
 > 	nome varchar,
 > 	alcunha string,
 > 	data_filiacao date)
 >
 > CREATE TABLE paga_cota (
 > 	ano integer,
 > 	mes integer,
 > 	valor integer,
 > 	id integer,
 > 	PRIMARY KEY(id,ano,mes),
 > 	FOREIGN KEY(id) REFERENCES(socio))
 >
 >
 > After this I get an error around CREATE (of the 2nd table). I
 > just can't imagine what it might be.
 >



First ...
SERIAL means an integer with primary key [tableName]_pkey (socio_pkey),
not null and default set to sequence: [tableName]_[fieldName]_seq
(socio_id_seq).

Probably this is what you want!!
CREATE TABLE socio (
	id SERIAL,
...

Second ...
String ?? ... maybe char(x) or varchar(x) or text or ...

Third ...
FOREIGN KEY(id) REFERENCES socio ...




George Moga,
	george@dsn.ro
	Braila, ROMANIA





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2002-01-13 23:01 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-13 22:04 pg with different sql? Paulo J. Matos <pocm@rnl.ist.utl.pt>
2002-01-13 22:38 ` Ian Barwick <barwick@gmx.net>
2002-01-13 22:38 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-01-13 22:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-13 23:01 ` George Moga <george@dsn.ro>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox