Thread: INSERT and SELECT

INSERT and SELECT

From
Eric BARROCA
Date:
Hi,

I've tried this query with PostgreSQL, but it doesn't work. Could anyone
explain me why and what must I do to get the same result ?
Query example : "INSERT INTO table VALUES (        (SELECT oid FROM membre WHERE email="toto@toto.com"),
"test");"
I get the error : "parse error near select".

I've been searched in the manual and with deja.com but I haven't found
something about this.
This query works with mysql (and it's the only one query that works with
this RDBMS ;-)


Thanks,

Éric.

-- 
Éric BARROCA               | Logiciels Libres, Conception Intranet/Extranet,
E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce électronique,
Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.), Réseaux... 
GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr>


Re: [SQL] INSERT and SELECT

From
Stuart Rison
Date:
If you go into psql and type \h INSERT or \h SELECT you'll get the syntax
for these:

test_capture=> \h INSERT
Command: insert
Description: insert tuples
Syntax:       INSERT INTO class_name [(attr1, ...attrN)]       VALUES (expr1,..exprN) |       SELECT [DISTINCT [ON
attrN]]      expr1, ...exprN       [FROM from_clause]       [WHERE qual]       [GROUP BY group_list]       [HAVING
having_clause]      [UNION [ALL] SELECT ...];   
 

So what you want is:

INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
email="toto@toto.com";

Assuming table 'table' already exists.

Or 

test_capture=> \h SELECT
Command: select
Description: retrieve tuples
Syntax:       SELECT [DISTINCT [ON attrN]] expr1 [AS attr1], ...exprN       [INTO [TABLE] class_name]       [FROM
from_list]      [WHERE qual]       [GROUP BY group_list]       [HAVING having_clause]       [ORDER BY attr1 [ASC|DESC]
[USINGop1], ...attrN ]       [UNION [ALL] SELECT ...];      
 

SELECT oid, 'test' INTO other_table FROM membre WHERE
email="toto@toto.com");

which will create the appropriate funtion for you.

HTH,

S. 


On 10 Dec 1999, Eric BARROCA wrote:

> Hi,
> 
> I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> explain me why and what must I do to get the same result ?
> Query example : "INSERT INTO table VALUES (
>             (SELECT oid FROM membre WHERE email="toto@toto.com"),
>             "test");"
> I get the error : "parse error near select".
> 
> I've been searched in the manual and with deja.com but I haven't found
> something about this.
> This query works with mysql (and it's the only one query that works with
> this RDBMS ;-)
> 
> 
> Thanks,
> 
> �ric.
> 
> -- 
> �ric BARROCA               | Logiciels Libres, Conception Intranet/Extranet,
> E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce �lectronique,
> Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.), R�seaux... 
> GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr>
> 
> ************
> 

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison@biochem.ucl.ac.uk



Re: [SQL] INSERT and SELECT

From
Date:
change the double quotation mark into sigle ones:
email='toto@toto.com',
'test'

this is sql92 also, so, it's not pgsql's fault ;-).

On 10 Dec 1999, Eric BARROCA wrote:

> Hi,
> 
> I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> explain me why and what must I do to get the same result ?
> Query example : "INSERT INTO table VALUES (
>             (SELECT oid FROM membre WHERE email="toto@toto.com"),
>             "test");"
> I get the error : "parse error near select".
> 
> I've been searched in the manual and with deja.com but I haven't found
> something about this.
> This query works with mysql (and it's the only one query that works with
> this RDBMS ;-)
> 
> 
> Thanks,
> 
> �ric.
> 
> -- 
> �ric BARROCA               | Logiciels Libres, Conception Intranet/Extranet,
> E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce �lectronique,
> Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.), R�seaux... 
> GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr>
> 
> ************
> 



Re: [SQL] INSERT and SELECT

From
Vladimir Terziev
Date:
   Type
  database=>\h select 

    Vladimir




Re: [SQL] INSERT and SELECT

From
Eric BARROCA
Date:
Stuart Rison <rison@biochemistry.ucl.ac.uk> writes:
> INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
> email="toto@toto.com";
I've tried it, but it doesn't work too ("paser error near or at select"),
even if I replace " by '.

The sample table "table" :- ID int4- login varchar()- password varchar()

I'd like to put into the ID culumn the result of (SELECT ...), into the
login column "test" and into the password one "testpass".

I've tried many queries like :

INSERT INTO table (id, login, password) VALUES SELECT oid, 'test',
'testpass' FROM membre WHERE email='toto@toto.com';

or

INSERT INTO table (id, login, password) VALUES ((SELECT oid FROM membre
WHERE email='toto@toto.com'), 'test', 'testpass'); 

or 

INSERT INTO table (id, login, password) VALUES (SELECT oid, 'test',
'testpass' FROM membre WHERE email='toto@toto.com');

...

But nothing give me a result. I get always parse error near select, and I
can't figure why.


Best Regards,

Éric.


Re: [SQL] INSERT and SELECT

From
"Nikolay Mijaylov"
Date:
As i know PgSQL support subqueryes only in Where part
--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>

----- Original Message -----
From: Eric BARROCA <eric@netsystems.fr>
To: <pgsql-sql@postgreSQL.org>
Sent: ïåòúê, Äåêåìâðè 10, 1999 05:46
Subject: [SQL] INSERT and SELECT


> Hi,
>
> I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> explain me why and what must I do to get the same result ?
> Query example : "INSERT INTO table VALUES (
> (SELECT oid FROM membre WHERE email="toto@toto.com"),
> "test");"
> I get the error : "parse error near select".
>
> I've been searched in the manual and with deja.com but I haven't found
> something about this.
> This query works with mysql (and it's the only one query that works with
> this RDBMS ;-)
>
>
> Thanks,
>
> Éric.
>
> --
> Éric BARROCA               | Logiciels Libres, Conception
Intranet/Extranet,
> E-Mail: eric@netsystems.fr | Sites Web dynamiques, Commerce électronique,
> Tel: +33 (0)2 48 21 54 67  | Formations (Unix/Linux, Perl, etc.),
Réseaux...
> GSM: +33 (0)6 16 35 33 25  | NetSystems : <http://www.netsystems.fr>
>
> ************



Re: [SQL] INSERT and SELECT

From
"Ross J. Reedstrom"
Date:
I wasn't going to respond to this, but there are two, almost correct, replies,
so I thought I should edit a bit:

On Fri, Dec 10, 1999 at 04:31:32PM +0000, Stuart Rison wrote:
> If you go into psql and type \h INSERT or \h SELECT you'll get the syntax
> for these:

Good idea ;-)

> 
> test_capture=> \h INSERT
> Command: insert
> Description: insert tuples
> Syntax:
>         INSERT INTO class_name [(attr1, ...attrN)]
>         VALUES (expr1,..exprN) |
>         SELECT [DISTINCT [ON attrN]]
>         expr1, ...exprN
>         [FROM from_clause]
>         [WHERE qual]
>         [GROUP BY group_list]
>         [HAVING having_clause]
>         [UNION [ALL] SELECT ...];   
> 
> So what you want is:
> 
> INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
> email="toto@toto.com";

Oops, the | is between the VALUES and SELECT clauses, so that should be:

INSERT INTO table SELECT oid, 'test' FROM membre WHERE
email='toto@toto.com';

Note that the other followup, about quote marks, is also correct.

> 
> 
> Assuming table 'table' already exists.
> 
> Or 
> 
> test_capture=> \h SELECT
> Command: select
> Description: retrieve tuples
> Syntax:
>         SELECT [DISTINCT [ON attrN]] expr1 [AS attr1], ...exprN
>         [INTO [TABLE] class_name]
>         [FROM from_list]
>         [WHERE qual]
>         [GROUP BY group_list]
>         [HAVING having_clause]
>         [ORDER BY attr1 [ASC|DESC] [USING op1], ...attrN ]
>         [UNION [ALL] SELECT ...];      
> 
> SELECT oid, 'test' INTO other_table FROM membre WHERE
> email="toto@toto.com");

SELECT oid, 'test' INTO other_table FROM membre WHERE
email='toto@toto.com');

Just quote marks, here.


Re: [SQL] INSERT and SELECT

From
wieck@debis.com (Jan Wieck)
Date:
>
> Stuart Rison <rison@biochemistry.ucl.ac.uk> writes:
> > INSERT INTO table VALUES SELECT oid, 'test' FROM membre WHERE
> > email=3D"toto@toto.com";
> I've tried it, but it doesn't work too ("paser error near or at select"),
> even if I replace " by '.

    Because  VALUES  and  SELECT  are  mutually exclusive at that
    point.

    Just do INSERT INTO table SELECT ...

    or INSERT INTO TABLE (attrib, ...) SELECT ...

    and the parser should be happy.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [SQL] INSERT and SELECT

From
neko@kredit.sth.szif.hu
Date:
On Fri, 10 Dec 1999, Nikolay Mijaylov wrote:

> As i know PgSQL support subqueryes only in Where part
subquery? ;)
No.
> > I've tried this query with PostgreSQL, but it doesn't work. Could anyone
> > explain me why and what must I do to get the same result ?
> > Query example : "INSERT INTO table VALUES (
> > (SELECT oid FROM membre WHERE email="toto@toto.com"),
> > "test");"
Try:
=> INSERT INTO table -- NO values, but
-> SELECT oid FROM membre WHERE  ... ;
it may works.

--nek;(



Dilema.

From
"Mitch Vincent"
Date:
  I have a problem that I'm hoping someone can help me with. I've built a
web-pased application that is back-ended by PGsql.. It works great, I
couldn't ask for better preformance.

However, I just recently coded another module for it and this module needed
to accept text files (or just large text blocks lets say). I ran into the 8k
query limit barrier. I need these blocks of text to be searchable and am
having to do this with a flat-file based solution right now, it's slow and
cumbersome. I would much rather have the text stored in a PG field so I
don't have to stray from the design of the search engine (which is designed
to search only the pg database, but has been hacked to search flat files
too).. I could break the texts up but they really all need to be in a single
field, not broken up. I can't think up a way to do it with the 8k query
limit.

Another problem I see is that well, there are a lot of text files (4000 now,
with about 500 added a week), many over 8k in size. Would this be too much
for PG to handle saying that I could get all the text inserted?

Also feel free to suggest some kind of indexing system for the flat files,
the only problem with that will be integration into my existing application
but I've left some hooks open so I can deal with it..

Thanks guys!

-Mitch



Re: [SQL] Dilema.

From
Tom Lane
Date:
"Mitch Vincent" <mitch@venux.net> writes:
> Also feel free to suggest some kind of indexing system for the flat files,

Glimpse, http://glimpse.cs.arizona.edu/

If you are trying for a full-text index over a large amount of text,
Postgres is really not the right tool --- we don't have the right kind
of index support for searching for individual words/phrases within text
fields.  You can fake it with LIKE or regexp matches but performance
will be awful, because the searches will have to scan the whole database.
(There is code that partially addresses this in contrib/fulltextindex,
but performance will still be nothing to write home about.)

Much better to use something that's actually designed for the purpose.
Glimpse is a really nice tool: speedy, and its index files are quite
small.  I've used it for years to keep full-text indexes of all my
files.
        regards, tom lane


Re: [SQL] Dilema.

From
wieck@debis.com (Jan Wieck)
Date:
>   I have a problem that I'm hoping someone can help me with. I've built a
> web-pased application that is back-ended by PGsql.. It works great, I
> couldn't ask for better preformance.

    And  Bruce  just  said  (on the hackers list) that big tuples
    aren't an item of big pressure.  Reading this  less  than  24
    hours past tells another story.

> However, I just recently coded another module for it and this module needed
> to accept text files (or just large text blocks lets say). I ran into the 8k
> query limit barrier. I need these blocks of text to be searchable and am
> having to do this with a flat-file based solution right now, it's slow and
> cumbersome. I would much rather have the text stored in a PG field so I
> don't have to stray from the design of the search engine (which is designed
> to search only the pg database, but has been hacked to search flat files
> too).. I could break the texts up but they really all need to be in a single
> field, not broken up. I can't think up a way to do it with the 8k query
> limit.

    If  you  really  need  to  store  big  texts  into a table, a
    combination of a view, update  rules  and  PL  functions  can
    definitely  do  it. It might look a little complex first, but
    after understanding the trick, it's pretty neat.

    Look at this:

        pgsql=> \d bttest
              View "bttest"
         Attribute | Type | Extra
        -----------+------+-------
         key       | int4 |
         content   | text |
        View definition: SELECT bttest_shadow."key",
        bigtext_fetch(bttest_shadow.btid) AS content FROM
        bttest_shadow;

        pgsql=> select key, length(content) from bttest;
         key | length
        -----+--------
           1 |      3
           2 |      9
           4 | 135840
        (3 rows)

    Yes, the row with key=4 really contains a text of that  size.
    And I can get it back exactly as it was inserted.  And yes, I
    can do INSERT/UPDATE/DELETE on bttest. Anything behaves as if
    it where a regular table.

    But  it  needs  the  unlimited  querysize,  supported only in
    CURRENT development tree, so you'll  have  to  wait  for  7.0
    anyway.

> Also feel free to suggest some kind of indexing system for the flat files,
> the only problem with that will be integration into my existing application
> but I've left some hooks open so I can deal with it..

    That's another story, and Tom's comment on it says it all.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [SQL] Dilema.

From
wieck@debis.com (Jan Wieck)
Date:
I wrote:

>     Look at this:
>
>         pgsql=> select key, length(content) from bttest;
>          key | length
>         -----+--------
>            1 |      3
>            2 |      9
>            4 | 135840
>         (3 rows)

    Rereading  my  own  comment, I just realized that it would be
    possible to  add  a  real  BIG  text  type  with  a  slightly
    different   technique.    This  would  require  another  (not
    syscached) pg_ relation plus support in heap access and  some
    other  parts.   And  it  would  be a DEFINITELY NOT indexable
    type.  Also it would be subject to get removed someday  after
    we implemented large tuples.

    But  it  would  drop  the  pressure from the large tuple item
    close to zero IMHO. So if I can implement it in a  way,  that
    ensures it is easily removable again, why not?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [SQL] Dilema.

From
"Gene Selkov Jr."
Date:
"Mitch Vincent" wrote:
>   I have a problem that I'm hoping someone can help me with. I've built a
> web-based application that is back-ended by PGsql.. It works great, I
> couldn't ask for better preformance.
> 
> However, I just recently coded another module for it and this module needed
> to accept text files (or just large text blocks lets say). I ran into the 8k
> query limit barrier. I need these blocks of text to be searchable and am
> having to do this with a flat-file based solution right now, it's slow and
> cumbersome.

I realize my stuff does not qualify as anything for Tom Lane to write
home about. However, there's a chance that the method I am using fits
the purpose you describe (especially considering the quantities you
mentioned).

The following query is fairly complex, but it takes just a few seconds
to complete on our grossly overloaded server:


http://wit.mcs.anl.gov/EMP/select_emp_advanced.cgi?E1.ec_code=ec&E1.ec_code.op=%7E%09is+in+range&E1.ec_code.patt=3.4&ec_code.count=1&T1.text=or&T1.text.op=%7E*%09matches+regex.%2C+case-insensitive&T1.text.patt=rattus&T2.text=ocn&T2.text.op=%7E*%09matches+regex.%2C+case-insensitive&T2.text.patt=rat&T3.text=en&T3.text.op=%7E*%09matches+regex.%2C+case-insensitive&T3.text.patt=protease&text.count=1&N1.seg=km&N1.seg.op=%7E%09contained+in&N1.seg.patt=0+..+10&seg.count=1&constraint=E1+%26+%28T1+%7C+T2%29+%26+T3+%26+N1&do=Run+the+query

It would have taken at least as much to query any individual term with
Glimpse, and it would not be feasible to query things like E1 and N1
with anything but postgres because these queries are based on special
data types. Also, I can imagine some difficulty in doing all but
trivial relational operations using your average full-text engine.

The idea is simple:

1. Do not store the original files in postgres, or, if you are
desperate, store them as large objects. The only winning in large
objects is that they can be passed to the server and back through a
protocol supported by all postgres clients, but you loose to the
headache of maintaining the large objects which do need some
care. Note that there is no winning at all if your application is
already web-based by design.

A feasible alternative to both large objects and external file storage
is to use compression and uu or base64 encoding which allows one to
split their files into nice-looking numbered 61- or 76-character
lines. Postgres can handle an awesome lot of those.

Check out MIME::Base64 and Compress::Bzip2 modules if you are building
your clients in perl.

2. Having decided how to store and transfer the files, split them up
into words, load those into postgres and build indices on them. Word
indices can be quite efficient because you can use the ^-anchored
expressions with them. Also, parts of the data in your text can be
numeric, and it would be natural to index those as
numbers. Furthermore, if there are portions of your text that
represent distinct classes of data it would be equally natural to
store those in separate index tables. The are about 300 classes of
data in the database I am talking about, which is derived from the
published research articles. The following page roughly outlines how I
did it:

http://wit.mcs.anl.gov/EMP/indexing.html

This is not yet a full-text index but it would be trivial to make one
based on such paradigm. It won't allow you to search for arbitrary
substrings, but if you record the sequence in addition to the
absolute location of words, you will be able to search for "phrases"
of the AltaVista sorts, or even use proximity criteria like they do.

--Gene


manually set serial type

From
rob caSSon
Date:
i know this is in the list archives (i've done this before, and cant
remember now), but they seem to be down right now...

how can i manually set the currval of a serial column?  it's something
like:
select nextval('column_name')....

isn't it....

tia,
rob



Re: [SQL] manually set serial type

From
Date:
the search does not work right, but the mbox is still there. if you click
that, you can search that even in your IE, I assume you are using that ;-)


On Thu, 16 Dec 1999, rob caSSon wrote:

> i know this is in the list archives (i've done this before, and cant
> remember now), but they seem to be down right now...
> 
> how can i manually set the currval of a serial column?  it's something
> like:
> 
>     select nextval('column_name')....
> 
> isn't it....
> 
> tia,
> rob
> 
> 
> ************
> 



Re:[OT]-[SQL] manually set serial type

From
rob caSSon
Date:
yeah...i'll scour the mboxes, although i'm actually using netscrape 4.7 on
a debian box....;-)

rob

On Thu, 16 Dec 1999 kaiq@realtyideas.com wrote:

> the search does not work right, but the mbox is still there. if you click
> that, you can search that even in your IE, I assume you are using that ;-)
> 
> 
> > i know this is in the list archives (i've done this before, and cant
> > remember now), but they seem to be down right now...
> > 
> > how can i manually set the currval of a serial column?  it's something
> > like:
> > 
> >     select nextval('column_name')....
> > 
> > isn't it....
> > 
> > tia,
> > rob



Re:[OT]-[SQL] manually set serial type

From
Howie
Date:
On Thu, 16 Dec 1999, rob caSSon wrote:

> yeah...i'll scour the mboxes, although i'm actually using netscrape 4.7 on
> a debian box....;-)

select setval(sequence_name,value);

ie:

select setval( 'groups_seq', 89231 );

\df via psql shows:

result|function    |arguments       |description
------+------------+----------------+-------------------------
...
int4  |setval      |text int4       |sequence set value
...

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org    
"I've learned that you cannot make someone love you.  All you can do is stalk them and hope they panic and give in."