[SQL] default value syntax - pg compared to?
Hello,
do you happen to know, if there is any sqlserver that WON'T handle
inserting default value if it is not specified in the INSERT field list? I
am particularly interested in ORACLE, INFORMIX and MYSQL .
Eg.
CREATE TABLE test
(
id integer default 5,
fld1 text
);
INSERT INTO test (fld1) VALUES ('blahblah');
So, is there any sqlserver that under these conditions will NOT
make id = 5 for the inserted row?
Background:
There is a package called phprojekt where the developer has
created some workarounds for different handlings of auto-
incrementing fields (sequences) for the above databases.
The only place he uses them, however, is in the INSERT
stataments (and he does not need to know the new id value for the
inserted row).
So I suggested he rewrite INSERTs explicitly specifying field
names and excluding the id field, rather than putting a server-
specific "default value" token (which postgres does not support
anyway). Will it work for the above other dbs?
TIA for answers!
Emils
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Outer joins?
Hello! I am a longtime postgres user (started around 10 years ago), however, as for some years I've been using it mostly as administrator. Now that I have started a project and doing some SQL, I've come up something I don't believe is right. Maybe I am too rusty on my SQL - if so, please forgive me, but I checked it and my reasoning seemed ok to me. I am trying to do simple self-joins. The table structure is: object_values == obj_id att_id value namely, each object can have arbitrary number of attributes each of them with a value. What I want, is a simple table of objects with some of their specific attributes, the result should be in form: obj_id1 o1att1_value o1att2_value o1att3_value obj_id2 o2att1_value o2att2_value o2att3_value ... Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in that grid point. So, I thought some nested outer joins should be OK? SELECT OV.obj_id AS obj_id, OV.value AS NAME, ov1.value AS DESCRIPTION, ov2.value AS ICON FROM object_values OV LEFT JOIN object_values ov1 USING(obj_id) LEFT JOIN object_values ov2 USING(obj_id) WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16; So, I figured this should get me all objects that have atttribute 7 defined, regardless of whether the other attributes exist for them? However, for some reason PG8.1 is giving me something like an INNER join on this query - namely ONLY rows where ALL the attributes ARE present. Am I doing something wrong? As I said my SQL is rusty, but this looked pretty straightforward to me... Thanks in advance, Emils ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Outer joins
(damn gmail, sorry about priv mail earlier) Stephan Szabo <[EMAIL PROTECTED]>: I think putting a test in an ON clause associated with the join (using something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id and ov1.att_id=8) rather than where will consider both as part of the join and null extend even if there are obj_id matches if none of those have att_id=8. Thanks, that worked! 2006/4/28, Tom Lane <[EMAIL PROTECTED]>: Emils <[EMAIL PROTECTED]> writes: > The table structure is: > object_values > == This isn't an outer-join problem, it's a crosstab problem. Try the crosstab functions in contrib/tablefunc. Thanks, I looked into it, unfortunately I might have to have also a solution for where installing contrib stuff is not possible. Emils ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SELECT substring with regex
2006/7/7, T E Schmitz <[EMAIL PROTECTED]>: valid entries would be: "28mm F2.8" (prime lens) "30-70mm F4" (zoom lens) "30-70mm F2.8" (zoom lens) "30-100mm F4.5-5.6" (zoom lens with variable speed) If these are the cases, wouldn't the regex be simply: "^[\d\-]+mm" for BASE "^[\d\-]+mm (.+)$" for SUFFIX Or are you having to deal with malformatted data too (like "30 -70mm" or "28 mm ")? Emils ---(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
[SQL]
[SQL] Q: spinlock on Alpha? (PG7.0.2)
RedHat Linux 6.2 on Alphaserver DS10 (egcs-1.1.2, glibc-2.1.3, libstdc++-2.9.0). Postgresql-7.0.2 source Compiles and installs just fine. However, both the regular initdb and parallel regression testing's initdb stage fails with a core file and message: FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. I fished around in the mailing lists and as I understand there has been some problems with spinlock on Alpha, which have been resolved? Any ideas what could I check/do about that greatly appreciated ( especially as I need to move the production server to alpha ASAP :(( )! TIA, Emils
Re: [SQL] Q: spinlock on Alpha? (PG7.0.2) - solved
From: Fabrice Scemama <[EMAIL PROTECTED]> > Try this: > http://www.rkirkpat.net/software/ > > parallel regression testing's initdb stage fails with a core file and > > message: > > > > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. > > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. > > Thanks, it worked! Now everything works fine and the only regression tests that fail are tinterval abstime geometry I wonder if I might have any problems because of those?
[SQL] Q: performance on some selects (7.0.2)?
I have the following tables: ** Table "articles" Attribute | Type | Modifier -+-+-- id | integer | title | text| authorid| integer | sourceid| integer | createddate | date| createdtime | time| publishdate | date| publishtime | time| categoryid | integer | groupid | integer | lead| text| body| text| status | integer | typeid | integer | hot | integer | parentid| integer | Indices: articles_categoryid, articles_createddate, articles_createdtime, articles_groupid, articles_hot, articles_publish_datetime, articles_sourceid, articles_status, articles_typeid -- Table "articles_groups" Attribute | Type | Modifier ---+-+-- articleid | integer | not null groupid | integer | not null --- Table "newscategories" Attribute | Type | Modifier ---+--+-- id| integer | not null parentid | integer | not null default '0' name | varchar(255) | not null default '' directoryname | varchar(255) | not null default '' metakeywords | text | status| integer | not null default 1 sortnr| integer | not null default 0 level | integer | not null default 1 fullpath | text | section | integer | Index: newscategories_pkey * A typical query runs like this: SELECT a.id,a.title,c.fullpath,c.section FROM articles a,articles_groups x,newscategories c WHERE x.articleid=a.id AND a.categoryid=c.id AND x.groupid='9590' AND a.status=1 AND timestamp(a.publishdate,a.publishtime)<'now'::datetime ORDER BY a.createddate desc,a.createdtime desc LIMIT 3 Explain says: NOTICE: QUERY PLAN: Sort (cost=171.93..171.93 rows=1 width=56) -> Nested Loop (cost=0.00..171.92 rows=1 width=56) -> Nested Loop (cost=0.00..169.95 rows=1 width=36) -> Seq Scan on articles_groups x (cost=0.00..12.10 rows=1 width=4) -> Seq Scan on articles a (cost=0.00..135.55 rows=636 width=32) -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 width=20) EXPLAIN Now, as I understand the thing that slows everything is the Seq scan on articles. I wonder why should it be that the query can't use index? TIA! Emils
Re: [SQL] renaming columns... danger?
Subject:[SQL] renaming columns... danger? > I just discovered that doing an alter table ... alter > column (to rename a column) does not do a complete > rename throughout the database. > shouldn't rename update any index and key definitions? > I'm very frightened right now, because I'm rather > dependent upon my database right now. I don't like > the thought that my database is corrupt at the schema > level. > Yes, I believe the same is true about trigger definitions and suchlike. In short - to do a rename on column I do a pg_dumpall and change all references of the name by hand :*((( Btw, is there a way to see what triggers are defined for particular field? Or how to drop triggers, which (by default) are unnamed?
Re: [SQL] renaming columns... danger?
Subject:[SQL] renaming columns... danger? > I just discovered that doing an alter table ... alter > column (to rename a column) does not do a complete > rename throughout the database. > shouldn't rename update any index and key definitions? > I'm very frightened right now, because I'm rather > dependent upon my database right now. I don't like > the thought that my database is corrupt at the schema > level. > Yes, I believe the same is true about trigger definitions and suchlike. In short - to do a rename on column I do a pg_dumpall and change all references of the name by hand :*((( Btw, is there a way to see what triggers are defined for particular field? Or how to drop triggers, which (by default) are unnamed?
[SQL] group by: properly?
Hello,
I must confess I have always been a bit baffled by the GROUP BY,
therefore I would appreciate if you could tell me if there's a better way:
I have the table "items":
id | integer | not null default nextval('items_id_seq'::text)
seller_id| integer | not null
material | integer | not null
item_kind| integer |
finishing| integer |
amount | integer | not null
What I need is to SELECT the list of items that have the same values in
fields in between the marks.
The material, finishing and item_kind are references to the appropriate id
fields in tables material, finishing and item_kinds:
materials:
id SERIAL,
name_en text
and I would like to get the name of the material rather then the id from the
query.
So I wrote:
SELECT M.name_en AS material, P.name_en AS itemtype, F.name_en
AS finishing, amount FROM items I, item_kinds P, materials M, finish F
WHERE F.id=I.finishing AND M.id=I.material AND P.id=I.item_kind
This gives me the list of items with the names rather than id's already
prepared, the list could be something like:
material itemtype finishing amount
'birch' 'SCAFFOLD BOARDS' 'levelled' 5
'birch' 'SCAFFOLD BOARDS' 'levelled' 33
'birch' 'SCAFFOLD BOARDS' 'levelled' 4
'oak' 'SCAFFOLD BOARDS' '' 7
'oak' 'C.L.S.' '' 66
Now, I want this query to give me only the one value for the items that differ
only with the amount. so that i have:
'birch' 'SCAFFOLD BOARDS' 'levelled' 42
'oak' 'SCAFFOLD BOARDS' '' 7
'oak' 'C.L.S.' '' 66
The following GROUP BY accomplishes it:
SELECT M.name_en AS material, P.name_en AS itemtype, F.name_en
AS finishing, sum(amount) FROM items I, item_kinds P, materials M, finish
F WHERE F.id=I.finishing AND M.id=I.material AND P.id=I.item_kind
GROUP BY M.name_en,P.name_en,F.name_en;
The question is:
assuming I will have quite a lot of rows in the result, will the performance be
OK to GROUP BY the resulting text fields rather then by I.item_kind,
I.material indexed numeric fields?
Is it possible to rewrite the query so that GROUP BY (or any alternative
construct) deals with the numeric fields and yet I can receive the textual
output (that I need) via a single query?
Any comments appreciated,
Emils
Re: [SQL] #DELETED error when using Access 2000 as frontend
> > I want to use pg 7.x as a backend for a MS Access application. I linked a > > table via ODBC, using the newest ODBC driver. I can open and view tables. But > > after I insert a new record, all fields will contain "#deleted". When I > > reopen the table, the inserted data is displayed correctly. > I recall seeing an MS Access 2000 bug that will cause this. I don't remember I can confirm the problem exists at least in access'97 -- applying both of the office'97 service packs helped. I have noticed all kinds of weird errors in Access which makes it operate badly with Postgres and I really can't recommend it as a frontend application, although I know there are people who use it quite successfully. On a side note - pls reply privately - what Win frontends (apart from web) have people used with Postgres, which work OK for them? Emils -- Emils Klotins IT Manager, Baltic states Grafton Entertainment Ltd. / TVNET 40-43 Brivibas Str., Riga LV1050, Latvia +371-7-242-001
Re: [SQL] DATE
> PLEASE ADVISE HOW I SHOULD ALTER THE COMMAND: > > <$NOW;DD;> > > TO GIVE ME A DATE THAT IS X DAYS FORWARD > > ie: I WISH TO REPRESENT A DATE IN FORM WHICH IS A 7 DAYS FORWARD > OF THE DATE NOW. ( now() + '7 days'::interval )::date (or ::datetime, whichever you need) Emils
[SQL] default value syntax - pg compared to?
Hello,
do you happen to know, if there is any sqlserver that WON'T handle
inserting default value if it is not specified in the INSERT field list?
I am particularly interested in ORACLE, INFORMIX and MYSQL .
Eg.
CREATE TABLE test
(
id integer default 5,
fld1 text
);
INSERT INTO test (fld1) VALUES ('blahblah');
So, is there any sqlserver that under these conditions will NOT
make id = 5 for the inserted row?
Background:
There is a package called phprojekt where the developer has
created some workarounds for different handlings of auto-
incrementing fields (sequences) for the above databases.
The only place he uses them, however, is in the INSERT
stataments (and he does not need to know the new id value for the
inserted row).
So I suggested he rewrite INSERTs explicitly specifying field
names and excluding the id field, rather than putting a server-
specific "default value" token (which postgres does not support
anyway). Will it work for the above other dbs?
TIA for answers!
Emils
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Regexps -- too complex?
Running 7.0.2 on Alpha/RedHat 6.2 256MB RAM In order to implement a fulltext search, I have a func that parses list of words and creates a regexp query with things like [[:<:]]( word | word | ... )[[:>:]] That query then is passed to backend... Now the strange thing: gamenet=# SELECT id, title,publishdate,categoryid FROM articles WHERE translate(title,'abcdefghijklmnopqrstuvwxyzâèçìîíïòðûþõäöü','ABCDEFGHIJKLMNOPQRSTUVWXYZÂÈÇÌÎÍÏÒÐÛÞÕÄÖÜ') ~ '(BLACK|SERIOUS|SAM)[[:>:]]'::text ; id | title | publishdate | categoryid --+-+-+ 600 | Serious Sam ceïâ pie pircçjiem | 2001-03-22 |149 523 | Black & White gaidîðanas svçtki | 2001-03-19 |155 241 | Lorgaine: The Black Standard - íeltu varoòeposs | 2001-02-27 |155 707 | Lorgaine: The Black Standard beta versija | 2001-03-23 |156 1484 | Black&White tomçr neesot spiegu programma | 2001-04-18 |155 1490 | Black & White FAQ | 2001-04-18 |160 1496 | Black & White "ïaunais" FAQ | 2001-04-18 |160 1732 | Black & White - pârdotâkâ spçle ASV | 2001-04-24 |155 (8 rows) gamenet=# SELECT id, title,publishdate,categoryid FROM articles WHERE translate(title,'abcdefghijklmnopqrstuvwxyzâèçìîíïòðûþõäöü','ABCDEFGHIJKLMNOPQRSTUVWXYZÂÈÇÌÎÍÏÒÐÛÞÕÄÖÜ') ~ '(BLACK|SERIOUS|WHITE|SAM)[[:>:]]'::text ; id | title | publishdate | categoryid +---+-+ (0 rows) It seems that if the regexp is too complex (more than 3 |-ed elements) it doesnt return. Any ideas? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Regexps -- too complex?
> SELECT id, title,publishdate,categoryid FROM articles WHERE > upper(title) ~ '(BLACK|SERIOUS|SAM)[[:>:]]'::text ; > > I think the proiblem is in trnsalte, not in regexp > > If you have installed apprporiate character encoding in Postgres, > 'upper' will work! > > Vladimir Thanks for the advice, unfortunately, it does not seem to work that way. CREATE TABLE "test" ( "title" text ); COPY "test" FROM stdin; Serious Sam ceïâ pie pircçjiem Black & White gaidîðanas svçtki Lorgaine: The Black Standard - íeltu varoòeposs Lorgaine: The Black Standard beta versija Black&White tomçr neesot spiegu programma Black & White FAQ Black & White "ïaunais" FAQ Black & White - pârdotâkâ spçle ASV \. SELECT title FROM test WHERE title ~ '(BLACK|WHITE|SAM)'; yields 8 rows. SELECT title FROM test WHERE title ~ '(BLACK|WHITE|blahblah|SAM)'; yields 0 rows! SELECT title FROM test WHERE title ~ '(BLACK|WHITE|SAM) *'; also yields 0 rows! I dont think this is right no matter what the characters I am using there. At least it shouldn't, should it? Emils ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Q:Postgres 7.0 & Access'97?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, I am using Postgres 7.0 on SuSE Linux 6.2 and Postdrv.exe 6.50. (as downloaded from /latest/ dir of PG FTP site yesterday). Now, I have experienced 4 issues immediately with Postgres via ODBC from Access and I was wondering whether you could have any idea what could possibly be done about them: 1. Every time a query runs from Access, I get an error on the server coneole: pq_recvbuf: EOF the client closed the connection unexpectedly The query seems to run fine though. 2. The tables on the SQL server were exported from Access tables, using Access' File | Export -> ODBC connection. Now I can only access them from psql monitor if I enclose both the table and field names in double quotes, like "Field1". The tables/fields do have alphanumeric chars in them only (A-z,0-9). If I try to access table "Test" like: \d Test, I get: can't find relation 'test'. (Note the caps both in table name & error msg) \d "Test" works. 3. The connection seems to be QUITE slow (approximately 1-2 seconds to show a form in Access), considering that the total of 19 tables in the dbase contain a maximum of a couple of thousand of rows (I think even less). The server is a HP Netserver PIII650 and does not at the moment run anything else. The client computer is not very up-to-date, yet the same Access db with local tables runs practically instantly. 4. This is almost definitely an Access problem, but even if so: are there any workarounds available? A WHERE clause that compares a boolean value with a boolean constant gives error: can't compare bool and int, use explicit cast. Ie. in Access trying to use a SELECT WHERE a=True , yields the above error, considering that a is a boolean field. Same query works from psql monitor. Thanks a LOT in advance for any comments. Emils, trying to make Access frontend work with Postgres backend. -BEGIN PGP SIGNATURE- Version: PGP 6.5.2 -- QDPGP 2.61a Comment: http://community.wow.net/grt/qdpgp.html iQA/AwUBOUYsHd0sxa1MAPWHEQKd5gCgyNG2ZXSYrfTC8w6nS3Dm4zdp7RAAn2wH UveKFCmxHqDeGmJT1BpfyvhQ =ZVIv -END PGP SIGNATURE-
[SQL] Backup?
Hello, I had a look for 'backup' on the mailing list archives and strangely enough didn't find a message containing the word in admin, general and sql mailing lists... I am sure this must have been asked before, but still: Would it be possible to backup a db by just copying the dir struct under the PG_DATA or must I absolutely use pg_dump? I am using Postgres 7.0. The purpose of the backup is of course to have a copy handy if eg the harddrive breaks. As I understand pg_dump could be comparatively slower, plus it produces a text format dumpfile, which could be kind of large in my db in future. Thanks in advance, Emils
[SQL] trigger or something else?
Hello, I have a table that has to have several fields with different names, but equal content. Sounds stupid, but it is because I have 2 different programs querying the same table for user information and each of them uses differently named fields. Eg. I have fields passwd and password. When passwd field changes, password must automatically change to be the same as passwd. I was wondering whether I need a trigger for that, or could I somehow manage to specify that in the "create table" stmt. If I need to do it via trigger, then I apparently need the plpgsql, right? Could you tell which configure option enables that? --enable- plpgsql? Thanks in advamce for any comments. Emils
[SQL] test
This is a test to see if I can post. Seems my last post didn't go through...
Re: [SQL] referencing serials
On 21 Jul 2000, at 9:41, Markus Wagner wrote: > which data type should be used to hold references to SERIALs in external > tables? integer I believe. Actually if you \d a table with a serial you'll see that it's an integer with DEFAULT clause specified.
