Re: [SQL] nullif('','') on insert

Matt L. wrote: I need to test whether or not a value is null on insert. Example: insert into table (column) values nullif('','')); ERROR: column "column" is of type boolean but expression is of type text. Your problem is that NULL's are typed in PostgreSQL. Try this: SELECT NULL; SELECT

Re: [SQL] booleans and nulls

Matt L. wrote: Out of curiousity, 1. Does a boolean column occupy 1byte of disk whether or not the value is null or not? I believe so. 2. Is matching on IS NULL or = 0 more efficient? Hmm... = 0 is the same as IS FALSE. Not the same as IS NULL. So I guess it is apples v. oranges.

Re: [SQL] returning inserted id

On Sun, Aug 21, 2005 at 12:56:27 -0700, "Matt A." <[EMAIL PROTECTED]> wrote: > this may have been asked a thousand times but i > haven't found a standard answer... > > > MSSQL > set nocount on > insert into (column) values (value) > select identityid = @@identity > set nocount off > > > POSTG

[SQL] returning inserted id

this may have been asked a thousand times but i haven't found a standard answer... MSSQL set nocount on insert into (column) values (value) select identityid = @@identity set nocount off POSTGRESQL *cricket cricket* :) How is this done? By a trigger function? Or is it natively supported? Coul

[SQL] question

I have a rating section on a website. It has radio buttons with values 1-5 according to the rating level. Lastly there is a null option for n/a. We use null for n/a so it's excluded from the AVG() calculations. We used nullif('$value','') on inserts in mssql. We moved to postgres and love it but

[SQL] Unwanted nested dollar-quoted constants

Hello, I recurrently need to migrate a big amount of data from one database type to PostgreSQL 8.0.3. For this, I use INSERT scripts that I run from the pgAdmin III. All the text fields are specified using the dollar-quoted string constant form that. However, sometime, there are records that i

[SQL] nullif('','') on insert

I need to test whether or not a value is null on insert. Example: insert into table (column) values nullif('','')); ERROR: column "column" is of type boolean but expression is of type text. It works in MSSQL (probably against not standards) but nonetheless I need to make it work. I assume it's

[SQL] Numerical variables in pqsql statements

Folks, I'm sure this is dumb, but I'm a little confused about use of numerical variables in pqsql.  It was my impression one had to use EXECUTE on a concatenated string including quote_literal() for the variable containing the value.  This can be quite a bit of trouble.  I just wrote a funct

[SQL] booleans and nulls

Out of curiousity, 1. Does a boolean column occupy 1byte of disk whether or not the value is null or not? 2. Is matching on IS NULL or = 0 more efficient? 3. If I ix'd columns w/ null does postgres know whatevers not indexed is null or would their be no point? I currently utilize null fields

[SQL] Common patterns for 'store' functions

I find store functions fairly useful; eg for any table 'foo', the function store_foo(v1,v2,...,vn) returns int; will perform an insert (if a unique key is not present) or an update (if the unique key is present) v1,...,vn may be values for columns in the table foo, or perhaps also for tables

Re: [SQL] Tidying values on variable instantiation

[EMAIL PROTECTED] ("Bath, David") writes: > Question(s): > * Am I being realistic, or should I grit my teeth and clone code from > trigger to trigger and column to column? > * Is this something I should try and do using domains, types and > cast functions from "text" or some horrible combinatio

Re: [SQL] Tidying values on variable instantiation

[EMAIL PROTECTED] ("Greg Patnude") writes: > Data validation and purification should be performed at the > application layer -- you should format your data appropriately > BEFORE trying any INSERT/UPDATE operations. It seems to me that one might create some stored functions that can do some valida

Re: [SQL] Tidying values on variable instantiation

On Fri, Aug 26, 2005 at 13:04:10 +1000, > Desired Outcome(s): > * I would like to have the convenience of declaring a column that obeys > a constraint (similar to using a domain), but allows a "tidy-up" as the > value is created BEFORE asserting the constraint. This *might* be > termed a "do

Re: [SQL] Tidying values on variable instantiation

IMHO: It's not necessarily the job of the RDBMS to be responsible for formatting and cleaning of your data... This is a job better suited for the application layer and the data model... The RDBMS should only be responsible for enforcing constraints on the data... not validating or purifying the d

Re: [SQL] Tidying values on variable instantiation

Desired Outcome(s): * I would like to have the convenience of declaring a column that obeys a constraint (similar to using a domain), but allows a "tidy-up" as the value is created BEFORE asserting the constraint. This *might* be termed a "domain trigger". (Perhaps even a WORM is possible!).