Re: limit table to one row

Is there a way when creating a table to limit it to one row? That is,
without using a stored procedure?

I searched the documentation, but didn't find anything.

--
Brandon

On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
<brandon(at)geronimoalloys(dot)com> wrote:
> Is there a way when creating a table to limit it to one row?  That is,
> without using a stored procedure?

Sure just add a check constraint along the lines of:

CONSTRAINT Only_one_row
CHECK( tableuniqueid = 1 ); --assuming you row has a unique id of 1

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
<brandon(at)geronimoalloys(dot)com> wrote:
> Is there a way when creating a table to limit it to one row?  That is,
> without using a stored procedure?

Sure just add a check constraint along the lines of:

CONSTRAINT Only_one_row
CHECK( tableuniqueid = 1 ); --assuming you row has a unique id of 1

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

r == richard(dot)broersma(at)gmail(dot)com writes:

r> On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
r> <brandon(at)geronimoalloys(dot)com> wrote:
r> > Is there a way when creating a table to limit it to one row? That is,
r> > without using a stored procedure?

r> Sure just add a check constraint along the lines of:

r> CONSTRAINT Only_one_row
r> CHECK( tableuniqueid = 1 ); --assuming you row has a unique id of 1

Got it. Currently, it doesn't have a column for an ID, but I can add
one if this is the only way.

--
Brandon

On Thu, Jun 4, 2009 at 1:23 PM, Brandon Metcalf
<brandon(at)geronimoalloys(dot)com> wrote:

> Got it.  Currently, it doesn't have a column for an ID, but I can add
> one if this is the only way.

Actually any column with a unique index on it will work. It doesn't
have to be primary key or even a serial id.

ANSI-SQL 92 allows for the following syntax:

CONSTRAINT Only_one_row
CHECK( 1 >= (SELECT COUNT(*) FROM _this_table;));

However, I have yet to find a RDBMS (PostgreSQL included) that
supports select statements in a table's check constraint.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

r == richard(dot)broersma(at)gmail(dot)com writes:

r> On Thu, Jun 4, 2009 at 1:23 PM, Brandon Metcalf
r> <brandon(at)geronimoalloys(dot)com> wrote:

r> > Got it. Currently, it doesn't have a column for an ID, but I can add
r> > one if this is the only way.

r> Actually any column with a unique index on it will work. It doesn't
r> have to be primary key or even a serial id.

r> ANSI-SQL 92 allows for the following syntax:

r> CONSTRAINT Only_one_row
r> CHECK( 1 >= (SELECT COUNT(*) FROM _this_table;));

r> However, I have yet to find a RDBMS (PostgreSQL included) that
r> supports select statements in a table's check constraint.

I had thought about a subquery in the constraint, but as you say, that
didn't work.

Thanks for the help.

--
Brandon

On 4 jun 2009, at 22.17, Richard Broersma wrote:

> On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
> <brandon(at)geronimoalloys(dot)com> wrote:
>> Is there a way when creating a table to limit it to one row? That
>> is,
>> without using a stored procedure?
>
>
> Sure just add a check constraint along the lines of:
>
> CONSTRAINT Only_one_row
> CHECK( tableuniqueid = 1 ); --assuming you row has a unique id
> of 1

Another way, which I've used a couple of times, is to use the rule
system:

CREATE TABLE single_row (value text);
INSERT INTO single_row VALUES ('value');
CREATE RULE no_insert AS ON INSERT TO single_row DO INSTEAD NOTHING;
CREATE RULE no_delete AS ON DELETE TO single_row DO INSTEAD NOTHING;

This way, the table must have exactly one row. I believe the
constraint check would still allow the row to be deleted, which you
may or may not want.

If you want an error to be raised when inserting or deleting, you'd
have to call a function raising the error in the rule. A minor
drawback is that the table still isn't safe from TRUNCATE though.

Sincerely,

Niklas Johansson

p == pgmailings(at)codecraft(dot)se writes:

p> On 4 jun 2009, at 22.17, Richard Broersma wrote:

p> > On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
p> > <brandon(at)geronimoalloys(dot)com> wrote:
p> >> Is there a way when creating a table to limit it to one row? That
p> >> is,
p> >> without using a stored procedure?
p> >
p> >
p> > Sure just add a check constraint along the lines of:
p> >
p> > CONSTRAINT Only_one_row
p> > CHECK( tableuniqueid = 1 ); --assuming you row has a unique id
p> > of 1

p> Another way, which I've used a couple of times, is to use the rule
p> system:

p> CREATE TABLE single_row (value text);
p> INSERT INTO single_row VALUES ('value');
p> CREATE RULE no_insert AS ON INSERT TO single_row DO INSTEAD NOTHING;
p> CREATE RULE no_delete AS ON DELETE TO single_row DO INSTEAD NOTHING;

p> This way, the table must have exactly one row. I believe the
p> constraint check would still allow the row to be deleted, which you
p> may or may not want.

p> If you want an error to be raised when inserting or deleting, you'd
p> have to call a function raising the error in the rule. A minor
p> drawback is that the table still isn't safe from TRUNCATE though.

Thanks for the info. The data in the table in question is easy to
recreate if it gets deleted. The main thing is to prevent more than
one row, so using CHECK( tableuniqueid = 1 ) works fine.

--
Brandon

just change whatever you are storing to be in vertical structure,
instead of horizontal. so instead of create table foo(a int, b int, c
int, etc), try:

create table foo(name varchar, val int);

common mistake I've seen committed by people..

On Thu, Jun 4, 2009 at 4:13 PM, Brandon
Metcalf<brandon(at)geronimoalloys(dot)com> wrote:
> Is there a way when creating a table to limit it to one row?  That is,
> without using a stored procedure?
>
> I searched the documentation, but didn't find anything.
>
> --

you can also do it with a trigger.

create or replace function one_row () returns trigger as
$$
declare
_rows bigint;
begin
execute 'select count(*) from ' || tg_relname into _rows;

if _rows > 1 then
raise exception 'one row only please';
end if;

return new;
end;
$$ language plpgsql;

create table one_row(id int);
create trigger on_one_row after insert on one_row execute procedure one_row();

as written, you only need one trigger function, and can attach it to
multiple tables. this has a couple of (small) advantages over the
unique constraint method...its more general and can satisfy a broader
range of conditions, is checked at the end of statement, not at each
row, and does not requires arbitrary annotation in the table.

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Thu, Jun 4, 2009 at 4:13 PM, Brandon
> Metcalf<brandon(at)geronimoalloys(dot)com> wrote:
>> Is there a way when creating a table to limit it to one row? That is,
>> without using a stored procedure?

> you can also do it with a trigger.

If you're willing to use a trigger, just insert the one allowed row
and then install a trigger that throws error for any insert or delete
attempt ...

regards, tom lane

On Sat, Jun 6, 2009 at 2:07 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Thu, Jun 4, 2009 at 4:13 PM, Brandon
>> Metcalf<brandon(at)geronimoalloys(dot)com> wrote:
>>> Is there a way when creating a table to limit it to one row?  That is,
>>> without using a stored procedure?
>
>> you can also do it with a trigger.
>
> If you're willing to use a trigger, just insert the one allowed row
> and then install a trigger that throws error for any insert or delete
> attempt ...

what if you need to delete then insert? :-)

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Sat, Jun 6, 2009 at 2:07 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> If you're willing to use a trigger, just insert the one allowed row
>> and then install a trigger that throws error for any insert or delete
>> attempt ...

> what if you need to delete then insert? :-)

Hmm ... I supposed the requirement was "always exactly one row".
If it's "always zero or one row", then you're right.

regards, tom lane

On Saturday 6. June 2009, Grzegorz Jaśkiewicz wrote:
>just change whatever you are storing to be in vertical structure,
>instead of horizontal. so instead of create table foo(a int, b int, c
>int, etc), try:
>
>create table foo(name varchar, val int);
>
>common mistake I've seen committed by people..

That sounds a lot like EAV.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

Brandon Metcalf wrote:
> Is there a way when creating a table to limit it to one row? That is,
> without using a stored procedure?
>
> I searched the documentation, but didn't find anything.
>

CREATE TABLE x (...);

CREATE UNIQUE INDEX x_only_one_row ON ((1));

--
Craig Ringer

On Sun, Jun 7, 2009 at 1:38 AM, Craig Ringer<craig(at)postnewspapers(dot)com(dot)au> wrote:
> Brandon Metcalf wrote:
>> Is there a way when creating a table to limit it to one row?  That is,
>> without using a stored procedure?
>>
>> I searched the documentation, but didn't find anything.
>>
>
>
> CREATE TABLE x (...);
>
> CREATE UNIQUE INDEX x_only_one_row ON ((1));

very clever :D

merlin

On 2009-06-04, Brandon Metcalf <brandon(at)geronimoalloys(dot)com> wrote:
> Is there a way when creating a table to limit it to one row? That is,
> without using a stored procedure?
>
> I searched the documentation, but didn't find anything.

create a unique index, and a constraint to a single value on one of
the columns :)

g == gryzman(at)gmail(dot)com writes:

g> just change whatever you are storing to be in vertical structure,
g> instead of horizontal. so instead of create table foo(a int, b int, c
g> int, etc), try:

g> create table foo(name varchar, val int);

g> common mistake I've seen committed by people..

I'm not sure I follow how this solves the problem.

--
Brandon

2009/6/9 Brandon Metcalf <brandon(at)geronimoalloys(dot)com>:

> I'm not sure I follow how this solves the problem.

Well, surely if you just need one row, you need single value per key.
And that's the, imo , better solution to that problem, than limiting
number of rows.

--
GJ

g == gryzman(at)gmail(dot)com writes:

g> 2009/6/9 Brandon Metcalf <brandon(at)geronimoalloys(dot)com>:

g> > I'm not sure I follow how this solves the problem.

g> Well, surely if you just need one row, you need single value per key.
g> And that's the, imo , better solution to that problem, than limiting
g> number of rows.

Hm. Maybe I'm looking at the problem incorrectly. I have a situation
where I need a table like

CREATE TABLE foo (
start DATE,
length VARCHAR(10),
);

I need to be able to get one value for each column at any given time.
Specifically, I need to get a value for start and add the value for
length to get a time period. Since your approach would allow more
than one row, how does it help me?

--
Brandon

2009/6/9 Brandon Metcalf <brandon(at)geronimoalloys(dot)com>:

>
>  CREATE TABLE foo (
>    start  DATE,
>    length VARCHAR(10),
>  );
>
> I need to be able to get one value for each column at any given time.
> Specifically, I need to get a value for start and add the value for
> length to get a time period.  Since your approach would allow more
> than one row, how does it help me?

If you want to store period of time, why store it as varchar ?

just store two rows
create table foo(
n varchar,
val date
);

and store two rows:
"start", now(),
"end", now()+'something '::interval

Wouldn't that do, or is there something I misunderstand ?

The approach with vertical structure, is used when people want to
store some particular state, say - kind of like structure in C, but in
SQL, and they create table for it with all the columns, etc, and have
to limit it to one row.

On the other hand, you could also use unique index on (1) approach
(say if you need more variety of types).

--
GJ

g == gryzman(at)gmail(dot)com writes:

g> If you want to store period of time, why store it as varchar ?

g> just store two rows
g> create table foo(
g> n varchar,
g> val date
g> );

g> and store two rows:
g> "start", now(),
g> "end", now()+'something '::interval

g> Wouldn't that do, or is there something I misunderstand ?

Sure, that would work, but I'm having to retrofit this table to an
existing system. Much of the system I'm redesigning, but this piece
isn't too terribly important right now.

g> The approach with vertical structure, is used when people want to
g> store some particular state, say - kind of like structure in C, but in
g> SQL, and they create table for it with all the columns, etc, and have
g> to limit it to one row.

g> On the other hand, you could also use unique index on (1) approach
g> (say if you need more variety of types).

Got it.

--
Brandon

On Tue, 09 Jun 2009 08:24:01 -0700, Brandon Metcalf
<brandon(at)geronimoalloys(dot)com> wrote:
>
> CREATE TABLE foo (
> start DATE,
> length VARCHAR(10),
> );
>
> I need to be able to get one value for each column at any given time.

CREATE UNIQUE INDEX u ON foo ((start IS NOT NULL));

You would just make sure you SELECT FROM foo WHERE start IS NOT NULL;

However, it doesn't prevent you from having 0 rows on the table.

On 2009-06-09, Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> wrote:
> 2009/6/9 Brandon Metcalf <brandon(at)geronimoalloys(dot)com>:
>
>
>> I'm not sure I follow how this solves the problem.
>
> Well, surely if you just need one row, you need single value per key.
> And that's the, imo , better solution to that problem, than limiting
> number of rows.

not really, if different keys have different types of values
associated.

one application I work with has a config table with atleast text, integer,
interval, and boolean values.

>
>
> --
> GJ
>