limit table to one row

On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
<brandon@geronimoalloys.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@geronimoalloys.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.broersma@gmail.com writes:

r> On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
r> <brandon@geronimoalloys.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@geronimoalloys.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.broersma@gmail.com writes:

r> On Thu, Jun 4, 2009 at 1:23 PM, Brandon Metcalf
r> <brandon@geronimoalloys.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@geronimoalloys.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@codecraft.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@geronimoalloys.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@geronimoalloys.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@gmail.com> writes:

On Thu, Jun 4, 2009 at 4:13 PM, Brandon
Metcalf<brandon@geronimoalloys.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@sss.pgh.pa.us> wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

On Thu, Jun 4, 2009 at 4:13 PM, Brandon
Metcalf<brandon@geronimoalloys.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@gmail.com> writes:

On Sat, Jun 6, 2009 at 2:07 PM, Tom Lane<tgl@sss.pgh.pa.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@postnewspapers.com.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@geronimoalloys.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@gmail.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@geronimoalloys.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@gmail.com writes:

g> 2009/6/9 Brandon Metcalf <brandon@geronimoalloys.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