Обсуждение: create table with table constraints
A sample, please, of the syntax for creating a table with unique table constraint. I thought I followed the online manual syntax, but only got a parser error pointing to the opening paren following "CREATE TABLE".
Thanks,
Jim
Thanks,
Jim
On Tue, Dec 13, 2005 at 07:46:10PM -0800, Jim Beckstrom wrote: > A sample, please, of the syntax for creating a table with unique > table constraint. I thought I followed the online manual syntax, > but only got a parser error pointing to the opening paren following > "CREATE TABLE". What part of the manual were you looking at? There are several examples in the "Constraints" section of the "Data Definition" chapter: http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html If that doesn't help then please show the exact command you ran and the exact error message; that way we can point out what's wrong and why it's wrong. It might also be useful to know what version of PostgreSQL you're running. -- Michael Fuhr
I looked at CREATE TABLE for both 8.0 and 7.2, they seem to be the same. I reverted to CREATE INDEX [UNIQUE]..., and that still works.
Jim
Michael Fuhr <mike@fuhr.org> wrote:
Jim
$cr->Query("CREATE TABLE client (Error: Unable to perform query: CREATE TABLE client( lastname CHAR(20) DEFAULT ' ', etc.,CONSTRAINT program (program1, intakedate, ssnbr))ERROR: parser: parse error at or near "("
lastname CHAR(20) DEFAULT ' ', firstname CHAR(15) DEFAULT ' ',
middleinit CHAR(1) DEFAULT ' ', ssnbr CHAR(11) DEFAULT ' ',
gender CHAR(1) DEFAULT ' ', casenbr CHAR(7) DEFAULT ' ',
membno CHAR(7) DEFAULT ' ', rqst4appt1 DATEDEFAULT '01/01/1901' :: DATE,
rqst4appt2 DATE DEFAULT '01/01/1901' :: DATE, rqst4appt3 DATE DEFAULT '01/01/1901' :: DATE,
intakedate DATE DEFAULT '01/01/1901' :: DATE, intaketime CHAR(6) DEFAULT ' ',
sal_code CHAR(4) DEFAULT ' ', assgnther CHAR(30) DEFAULT ' ',
th_ack_dat DATE DEFAULT '01/01/1901' :: DATE, su_ack_dat DATE DEFAULT '01/01/1901' :: DATE,
waiting DATE DEFAULT '01/01/1901' :: DATE, registtype CHAR(8) DEFAULT ' ',
registdate DATE DEFAULT '01/01/1901' :: DATE, registstat CHAR(12) DEFAULT ' ',
stnbr CHAR(8) DEFAULT ' ', stname CHAR(25) DEFAULT ' ',
pobox CHAR(5) DEFAULT ' ', city CHAR(12) DEFAULT ' ',
state CHAR(2) DEFAULT ' ', zip CHAR(10) DEFAULT ' ',
homephone CHAR(14) DEFAULT ' ', workphone CHAR(14) DEFAULT ' ',
home_msgs CHAR(3) DEFAULT ' ', work_msgs CHAR(3) DEFAULT ' ',
admitdate DATE DEFAULT '01/01/1901' :: DATE, ethnicity CHAR(20) DEFAULT ' ',
birthdate DATE DEFAULT '01/01/1901' :: DATE, recipient CHAR(30) DEFAULT ' ',
program1 CHAR(60) DEFAULT ' ', program2 CHAR(60) DEFAULT ' ',
program3 CHAR(60) DEFAULT ' ', program4 CHAR(60) DEFAULT '',
pgm_dir CHAR(30) DEFAULT ' ', county CHAR(10) DEFAULT ' ',
liable_cty CHAR(10) DEFAULT ' ', svc_area CHAR(20) DEFAULT ' ',
censustrct CHAR(4) DEFAULT ' ', highgrade CHAR(22) DEFAULT ' ',
spcedyrs CHAR(2) DEFAULT ' ', lastspced CHAR(30) DEFAULT ' ',
spcedcur CHAR(3) DEFAULT ' ', nbr_dep NUMERIC(2) DEFAULT 0,
maritalst CHAR(16) DEFAULT ' ', veteranst CHAR(7) DEFAULT ' ',
publicinc CHAR(18) DEFAULT ' ', grossinc CHAR(20) DEFAULT ' ',
employst CHAR(20) DEFAULT ' ', livingarrg CHAR(50) DEFAULT ' ',
childlivar CHAR(20) DEFAULT ' ', livarrinfo CHAR(40) DEFAULT ' ',
livarr_len CHAR(20) DEFAULT ' ', medicaidel CHAR(20) DEFAULT ' ',
sda_1991 CHAR(50) DEFAULT ' ', legal_stat CHAR(20) DEFAULT ' ',
correctrel CHAR(25) DEFAULT ' ', family_id CHAR(4) DEFAULT ' ',
primarycsm CHAR(30) DEFAULT ' ', second_csm CHAR(30) DEFAULT ' ',
refersrce CHAR(50) DEFAULT ' ', refer_reas CHAR(50) DEFAULT ' ',
informant CHAR(30) DEFAULT ' ', work_comp CHAR(3) DEFAULT ' ',
refer_tn CHAR(14) DEFAULT ' ', prev_trmnt CHAR(3)DEFAULT ' ',
prev_when CHAR(50) DEFAULT ' ', prev_name CHAR(30) DEFAULT ' ',
prev_casen CHAR(7) DEFAULT ' ', immed_help CHAR(3) DEFAULT ' ',
comingfrom CHAR(3) DEFAULT ' ', come_from CHAR(20) DEFAULT ' ',
from_admit DATE DEFAULT '01/01/1901' :: DATE, from_disch DATE DEFAULT '01/01/1901' :: DATE,
lastdtused DATE DEFAULT '01/01/1901' :: DATE, danger_rel CHAR(3) DEFAULT ' ',
emer1name CHAR(30) DEFAULT ' ', emer1addr CHAR(30) DEFAULT ' ',
emer1csz CHAR(30) DEFAULT ' ', emer1_h_tn CHAR(14) DEFAULT ' ',
emer1_w_tn CHAR(14) DEFAULT ' ', emer2name CHAR(30) DEFAULT ' ',
emer2addr CHAR(30) DEFAULT ' ', emer2csz CHAR(30) DEFAULT ' ',
emer2_h_tn CHAR(14) DEFAULT ' ', emer2_w_tn CHAR(14) DEFAULT ' ',
suicidal CHAR(3) DEFAULT ' ', homicidal CHAR(3) DEFAULT ' ',
hallucinat CHAR(3) DEFAULT ' ', delusions CHAR(3) DEFAULT ' ',
occupation CHAR(20) DEFAULT ' ', employer CHAR(30) DEFAULT ' ',
guardianyn CHAR(3) DEFAULT ' ', guarcustod CHAR(70) DEFAULT ' ',
protectsvc CHAR(70) DEFAULT ' ', convicinfo CHAR(60) DEFAULT ' ',
convicted CHAR(3) DEFAULT ' ', nbrconvict NUMERIC(2) DEFAULT 0,
chemconvic CHAR(3) DEFAULT ' ', timeinjail CHAR(3) DEFAULT ' ',
daysinjail NUMERIC(3) DEFAULT 0, court_pend CHAR(3) DEFAULT ' ',
court_chg CHAR(30) DEFAULT ' ', court_date DATE DEFAULT '01/01/1901' :: DATE,
prob_offic CHAR(30) DEFAULT ' ', p_o_addr CHAR(40) DEFAULT ' ',
p_o_tn CHAR(14) DEFAULT ' ', fm_name1 CHAR(30) DEFAULT ' ',
fm_tn1 CHAR(14) DEFAULT ' ', fm_relat1 CHAR(11) DEFAULT ' ',
fm_relea1 DATE DEFAULT '01/01/1901' :: DATE, fm_ltrdt1 DATE DEFAULT '01/01/1901' :: DATE,fm_name2 CHAR(30) DEFAULT ' ', fm_tn2 CHAR(14) DEFAULT ' ',
fm_relat2 CHAR(11) DEFAULT ' ', fm_relea2 DATE DEFAULT '01/01/1901' :: DATE,
fm_ltrdt2 DATE DEFAULT '01/01/1901' :: DATE, fm_name3 CHAR(30) DEFAULT ' ',
fm_tn3 CHAR(14) DEFAULT ' ', fm_relat3 CHAR(11) DEFAULT ' ',
fm_relea3 DATE DEFAULT '01/01/1901' :: DATE, fm_ltrdt3 DATE DEFAULT '01/01/1901' :: DATE,
fm_comm TEXT NOT NULL, svc_comm TEXT NOT NULL,
gastype CHAR(8) DEFAULT ' ', gasscore CHAR(2) DEFAULT ' ',
presenprob CHAR(40) DEFAULT ' ', seriousnes CHAR(42)DEFAULT ' ',
len_mhcond CHAR(18) DEFAULT ' ', prevmhsvcs CHAR(15) DEFAULT ' ',
othersvcs CHAR(10) DEFAULT ' ', first_appt CHAR(40) DEFAULT ' ',
svc_rcvng CHAR(40) DEFAULT ' ', last_phys DATE DEFAULT '01/01/1901' :: DATE,
casetype CHAR(30) DEFAULT ' ', dx_1 CHAR(6) DEFAULT ' ',
dx_des_1 CHAR(70) DEFAULT ' ', dx_2 CHAR(6) DEFAULT ' ',
dx_des_2 CHAR(70) DEFAULT ' ', appt_hours CHAR(20) DEFAULT ' ',
int_worker CHAR(30) DEFAULT ' ', open_date DATE DEFAULT '01/01/1901' :: DATE,
prev_svc CHAR(3) DEFAULT ' ',last_seen DATE DEFAULT '01/01/1901' :: DATE,
last_sal CHAR(4) DEFAULT ' ', admit_reas CHAR(32) DEFAULT ' ',
location1 CHAR(20) DEFAULT ' ', location2 CHAR(20) DEFAULT ' ',
location3 CHAR(20) DEFAULT ' ', location4 CHAR(20) DEFAULT ' ',
callreason TEXT NOT NULL, med_trtmnt TEXT NOT NULL,
curr_func TEXT NOT NULL, fam_soc_hx TEXT NOT NULL,
mentalstat TEXT NOT NULL,
CONSTRAINT intakedt UNIQUE (intakedate, casenbr),
CONSTRAINT client_case UNIQUE (intakedate, casenbr),
CONSTRAINT clientss UNIQUE (intakedate, casenbr),
CONSTRAINT clientname UNIQUE (intakedate, casenbr),
CONSTRAINT clientru (casenbr),
CONSTRAINT assgnther (assgnther, casenbr, th_ack_dat),
CONSTRAINT firstname (firstname),
CONSTRAINT program (program1, intakedate, ssnbr))");
Michael Fuhr <mike@fuhr.org> wrote:
On Tue, Dec 13, 2005 at 07:46:10PM -0800, Jim Beckstrom wrote:
> A sample, please, of the syntax for creating a table with unique
> table constraint. I thought I followed the online manual syntax,
> but only got a parser error pointing to the opening paren following
> "CREATE TABLE".
What part of the manual were you looking at? There are several
examples in the "Constraints" section of the "Data Definition"
chapter:
http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html
If that doesn't help then please show the exact command you ran and
the exact error message; that way we can point out what's wrong and
why it's wrong. It might also be useful to know what version of
PostgreSQL you're running.
--
Michael Fuhr
---------------------------(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
Jim Beckstrom <jrbeckstrom@sbcglobal.net> writes:
> Error: Unable to perform query: CREATE TABLE client( lastname CHAR(20) DEFAULT ' ', etc.,CONSTRAINT
program (program1, intakedate, ssnbr))ERROR: parser: parse error at or near "("
You should probably try testing on something newer than 7.2 ;-).
In 8.0 and up I get a pretty clear pointer to the problem:
psql:bogus.sql:90: ERROR: syntax error at or near "(" at character 6692
psql:bogus.sql:90: LINE 86: CONSTRAINT clientru (casenbr),
psql:bogus.sql:90: ^
and in fact that constraint and the ones after it are missing the UNIQUE
keyword.
regards, tom lane
On Thu, Dec 15, 2005 at 02:21:29PM -0800, Jim Beckstrom wrote:
> I looked at CREATE TABLE for both 8.0 and 7.2, they seem to be the
> same. I reverted to CREATE INDEX [UNIQUE]..., and that still works.
[snip 89-line example]
It's easier to find problems if you reduce them to the smallest
possible test case; the example you posted was bigger than it needed
to be. Anyway, here's what's wrong:
% psql -f test.sql
psql:test.sql:89: ERROR: syntax error at or near "(" at character 6692
psql:test.sql:89: LINE 86: CONSTRAINT clientru (casenbr),
psql:test.sql:89: ^
The offending lines are:
> CONSTRAINT clientru (casenbr),
> CONSTRAINT assgnther (assgnther, casenbr, th_ack_dat),
> CONSTRAINT firstname (firstname),
> CONSTRAINT program (program1, intakedate, ssnbr))");
You're trying to define several constraints but you've neglected
to declare what kind of constraints they are. If you want them to
be UNIQUE constraints then do what you did on the lines immediately
preceding:
> CONSTRAINT intakedt UNIQUE (intakedate, casenbr),
> CONSTRAINT client_case UNIQUE (intakedate, casenbr),
> CONSTRAINT clientss UNIQUE (intakedate, casenbr),
> CONSTRAINT clientname UNIQUE (intakedate, casenbr),
--
Michael Fuhr