Thread: pg with different 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 wroteit all in Lisp!
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
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
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
Paulo J. Matos wrote: > Hi all,> CREATE TABLE socio (> id integer SERIAL PRIMARY KEY,> nome varchar,> alcunha string,> data_filiacaodate)>> 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 2ndtable). 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.roBraila, ROMANIA