Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inserting data in composite types!

Inserting data in composite types!

From
"Rodrigo Sakai"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size:
10.0pt;font-family:Arial">  Hi, I have a question about how to insert data in composite types!</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">  Imagine the exemple:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">CREATETYPE t_time AS (</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> a date,</span></font></b><p class="MsoNormal"><b><font
face="Arial"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:Arial;font-weight:bold">  b
date</span></font></b><pclass="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">);</span></font></b><pclass="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">CREATETABLE salary (</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">  salary numeric(10,2),</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">  t_date t_time</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">);</span></font></b><pclass="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">I know that if I want to insert data in the table SALARY I just have to do
like:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> INSERT INTO salary VALUES (1000.00, ‘(2006/10/10,
2006/12/10)’);</span></font></b><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">But if I have another table:</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">CREATETABLE employee (</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> employee_id int,</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> name varchar(30),</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> emp_salary salary</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">)</span></font></b><pclass="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">How can I insert a single row in this table???</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Thanks in advamce!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size: 
10.0pt;font-family:Arial"> </span></font></div>

Re: Inserting data in composite types!

From
Markus Schiltknecht
Date:
Hi,

Rodrigo Sakai wrote:
> How can I insert a single row in this table???

INSERT INTO employee (emp_salary)  VALUES ((1000.00, '(2006/10/10, 2006/12/10)'));


BTW: are you sure you don't want to use foreign keys instead? Something 
like:

CREATE TABLE salaries (  id SERIAL PRIMARY KEY,  salary numeric(10,2) NOT NULL,  t_date t_time NOT NULL
);

CREATE TABLE employee (  employee_id SERIAL PRIMARY KEY,  name TEXT NOT NULL,  salary INT NOT NULL REFERENCES
salaries(id)
);

Regards

Markus

Re: Inserting data in composite types!

From
Shane Ambler
Date:
Rodrigo Sakai wrote:
>   Hi, I have a question about how to insert data in composite types!
> 
>  
> 
>   Imagine the exemple:
> 
>  
> 
> CREATE TYPE t_time AS (
> 
>   a date,
> 
>   b date
> 
> );
> 
>  
> 
> CREATE TABLE salary (
> 
>    salary numeric(10,2),
> 
>    t_date t_time
> 
> );
> 
>  
> 
> I know that if I want to insert data in the table SALARY I just have to do
> like:
> 
>  
> 
>   INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)');
> 
>  
> 
> But if I have another table:
> 
>  
> 
> CREATE TABLE employee (
> 
>   employee_id int,
> 
>   name varchar(30),
> 
>   emp_salary salary
> 
> )

I am thinking that with the salary type here you are thinking of your 
salary table defined above?
If so and you want them in a separate table to record salary histories 
then you want to create a foreign key to link them.

You would end up with -

CREATE TABLE employee (
  employee_id int PRIMARY KEY,
  name varchar(30)

);

CREATE TABLE salary (
   emp_id int REFERENCES employee(employee_id) ON DELETE CASCADE,
   salary numeric(10,2),
   t_date t_time

);

then -
INSERT INTO salary VALUES (1, 1000.00, '(2006/10/10, 2006/12/10)');


Otherwise you will want to change the CREATE TABLE salary... to CREATE 
TYPE salary...

Probably as
CREATE TYPE salary AS(   salary numeric(10,2),   a date,   b date
);

You can then
INSERT INTO employee VALUES
(1,'Hard Worker','(1000.00, 2006/10/10, 2006/12/10)');


-- 

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

Re: Inserting data in composite types!

From
imad
Date:
I am able to do this thing with the following query:

insert into employee (a, name, s) values(1, 'emp name', ((1,
'(01/01/2000, 01/01/2000)')));


--Imad
www.EnterpriseDB.com




On 11/13/06, Rodrigo Sakai <rodrigo.sakai@zanthus.com.br> wrote:
>
>
>
>
>   Hi, I have a question about how to insert data in composite types!
>
>
>
>   Imagine the exemple:
>
>
>
> CREATE TYPE t_time AS (
>
>   a date,
>
>   b date
>
> );
>
>
>
> CREATE TABLE salary (
>
>    salary numeric(10,2),
>
>    t_date t_time
>
> );
>
>
>
> I know that if I want to insert data in the table SALARY I just have to do
> like:
>
>
>
>   INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)');
>
>
>
> But if I have another table:
>
>
>
> CREATE TABLE employee (
>
>   employee_id int,
>
>   name varchar(30),
>
>   emp_salary salary
>
> )
>
>
>
> How can I insert a single row in this table???
>
>
>
> Thanks in advamce!
>
>

RES: Inserting data in composite types!

From
"Rodrigo Sakai"
Date:
  Thanks, It works! I have tried:

insert into employee (c, nome, sal) values(1, 'emp name', '(1, (01/01/2000,
01/01/2000))' );
 And it doesn't work!

-----Mensagem original-----
De: imad [mailto:immaad@gmail.com] 
Enviada em: segunda-feira, 13 de novembro de 2006 11:10
Para: rodrigo.sakai@zanthus.com.br
Cc: pgsql-sql@postgresql.org
Assunto: Re: [SQL] Inserting data in composite types!

I am able to do this thing with the following query:

insert into employee (a, name, s) values(1, 'emp name', ((1,
'(01/01/2000, 01/01/2000)')));


--Imad
www.EnterpriseDB.com




On 11/13/06, Rodrigo Sakai <rodrigo.sakai@zanthus.com.br> wrote:
>
>
>
>
>   Hi, I have a question about how to insert data in composite types!
>
>
>
>   Imagine the exemple:
>
>
>
> CREATE TYPE t_time AS (
>
>   a date,
>
>   b date
>
> );
>
>
>
> CREATE TABLE salary (
>
>    salary numeric(10,2),
>
>    t_date t_time
>
> );
>
>
>
> I know that if I want to insert data in the table SALARY I just have to do
> like:
>
>
>
>   INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)');
>
>
>
> But if I have another table:
>
>
>
> CREATE TABLE employee (
>
>   employee_id int,
>
>   name varchar(30),
>
>   emp_salary salary
>
> )
>
>
>
> How can I insert a single row in this table???
>
>
>
> Thanks in advamce!
>
>

Re: RES: Inserting data in composite types!

From
Richard Broersma Jr
Date:
>   Thanks, It works! I have tried:
> 
> insert into employee (c, nome, sal) values(1, 'emp name', '(1, (01/01/2000,
> 01/01/2000))' );
> 
>   And it doesn't work!

What is the error message?

Regards,

Richard Broersma Jr.