On Wed, Dec 28, 2005 at 00:52:18 +0700,
David Garamond <[EMAIL PROTECTED]> wrote:
> Is it possible to use only CHECK constraint (and not triggers) to
> completely enforce ordered value of a column (colx) in a table? By that
> I mean:
>
> 1. Rows must be inserted in the order of colx=1, then colx
"George Pavlov" <[EMAIL PROTECTED]> writes:
> the interesting thing here is that 4::int gets into a text
> field whereas 4::text does not get into an integer field. seems to me
> like there is an implicit int-to-text cast (without a symmetrical
> text-to-int one)
Yeah, there is. You can easily se
On Wed, Dec 28, 2005 at 04:37:21PM -0300, Don Croata wrote:
> Please, if someone recalls a link, book, piece of code or anything with info
> about this technique for PL/PgSQL (8.1), please let us know. We've been
> searching into google, groups.google, http://archives.postgresql.org and
> http://ww
> > test=# insert into foo values (4::int,4::int);
> > INSERT 0 1
> > test=# insert into foo values (4::text,4::text);
> > ERROR: column "b" is of type integer but expression is of type text
> > HINT: You will need to rewrite or cast the expression.
> > test=# insert into foo values (cast(4 as
or, from the "stupid tricks" category:
SELECT
n.user_id,
max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note)
FROM notes n
GROUP by n.user_id
i am not *really* suggesting this!
---(end of broadcast)---
TIP 5: don't forget
If you want the latest by user, you can cheat a bit and use the fact that
the id's are incrementing, thus ordering by the id
is about the same as ordering by the date field. I know it can be inexact
in some corner cases, but it's a good approximation, and
very useful in practice :
SELECT user
At 06:58 PM 12/28/05, Collin Peters wrote:
The following query will return me all the latest dates, but I can't
return the note_id or subject with it.
SELECT n.user_id, max(n.modified_date)
FROM notes n
GROUP by n.user_id
ORDER BY n.user_id
Is this simpler than I am making it?
No, it's not "s
Collin Peters <[EMAIL PROTECTED]> writes:
> Is there a nice simple query I can run that will return me a list of
> all the *latest* notes for all users (users can have many notes in the
> table)?
You can use SELECT DISTINCT ON for that, if you don't mind using a
Postgres-only feature. See the "we
I have a simple table called notes which contains notes for users.
The table has 4 columns: note_id (auto-incrementing primary key),
user_id (foreign key to a users table), note (varchar), and
modified_date (timestamp).
Is there a nice simple query I can run that will return me a list of
all the
George Pavlov wrote:
> test=# insert into foo values (4::int,4::int);
> INSERT 0 1
> test=# insert into foo values (4::text,4::text);
> ERROR: column "b" is of type integer but expression is of type text
> HINT: You will need to rewrite or cast the expression.
> test=# insert into foo values (ca
> Sure, but in this example the required type of the value is clear from
> immediate context (ie, the INSERT). This is one of the cases where
> the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4'
> in this example are *not* values of type text; they are
> untyped literals which
"George Pavlov" <[EMAIL PROTECTED]> writes:
> indeed! but, wait, doesn't our favorite dbms do some implicit casting
> too? continuing with my table foo (a varchar, b int):
> test=# delete from foo;
> DELETE 2
> test=# insert into foo values (4,4);
> INSERT 0 1
> test=# insert into foo values ('4',
> "Better" is in the eye of the beholder.
sorry for the value-laden term. "laxer" is more appropriate, of course!
the funny thing is that had they cast the NULLs to TEXT it would have
failed there too (they do not do implicit TEXT to INT).
> It surprises me not at all that
> Microsoft would be
"George Pavlov" <[EMAIL PROTECTED]> writes:
> What does The SQL Standard say about this one?
> insert into foo (a, b) select distinct null, null from bar;
> -- ERROR: column "b" is of type integer but expression is of type text
According to the SQL spec that query is illegal on its face --- the
What does The SQL Standard say about this one?
create table foo (a varchar, b int);
insert into foo (a, b) select null, null from bar;
-- no problem
insert into foo (a, b) select distinct null, null from bar;
-- ERROR: column "b" is of type integer but expression is of type text
-- HINT: You w
Please, if someone recalls a link, book, piece of code or anything with info about this technique for PL/PgSQL (8.1), please let us know. We've been searching into google, groups.google,
http://archives.postgresql.org and http://www.postgresql.org/docs/8.1/interactive with no results. Most of the
On Wed, Dec 28, 2005 at 10:48:25AM -0500, Tom Lane wrote:
> Don Croata <[EMAIL PROTECTED]> writes:
> > It's a function who has a cursor and calls itself, but the problem raises
> > after the first recursion, when PgSQL complains:
>
> > ERROR: cursor "cur" already in use
>
> > Are the cursors kep
On 12/28/05, J Crypter <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I would like to implement a 1:n relation between two
> tables.
> An auto-generated number should be used as primary key
> which connects both tables.
> Example:
>
> table 1:
> name | number (prim_key)
>
> table 2:
> country | number_table1
Don Croata <[EMAIL PROTECTED]> writes:
> It's a function who has a cursor and calls itself, but the problem raises
> after the first recursion, when PgSQL complains:
> ERROR: cursor "cur" already in use
> Are the cursors kept globally? or cached like TEMP TABLE?
Cursor names are global within a
elein <[EMAIL PROTECTED]> writes:
> > Note that the above are not inverses because you changed the lefthand
> > input. You do get consistent results when you just add or omit NOT:
> Yes, you are right. I skipped the permutations to get down to the point.
Remember that NULL means "unknown". So "1
Hi,
I would like to implement a 1:n relation between two
tables.
An auto-generated number should be used as primary key
which connects both tables.
Example:
table 1:
name | number (prim_key)
table 2:
country | number_table1 (foreign key)
View:
number_table1 | name | country
I would like to
Hi,
I've been trying to do recursion and cursors in PL/PgSQL (PostgreSQL 8.1). It's a function who has a cursor and calls itself, but the problem raises after the first recursion, when PgSQL complains:
ERROR: cursor "cur" already in use
Are the cursors kept globally? or cached like TEMP TABL
22 matches
Mail list logo
