Can't get element from array in second column PostgreSQL

Question

I made two dimensional array as timestamp type. Then i added an insert like this

'{{2017-03-01 14:00:00},{2017-06-01 21:00:00}}'

How can I get only second element?

Creating of table:

create table client(id SERIAL PRIMARY KEY, full_name VARCHAR(40) NOT NULL, gender VARCHAR(15) NOT NULL, phone_number VARCHAR(20) NOT NULL, card_type VARCHAR(30) NOT NULL, card_period Timestamp[][] NOT NULL );

Then I made some inserts as next:

insert into client (full_name, phone_number, card_type, card_period, gender) values('Mr James Abrams','3805832940003073','Bronze','{{2017-03-01 14:00:00},{2017-06-01 21:00:00}}','man');

When for example I select something

select card_period from client where id = 1;

Its shows like: 2017-03-01T14:00:00,2017-06-01T21:00:00 I tried to select card_period[0:1] - shows only first, if [0:2] then it shows them both

How you get the first one? Show db schema? How you insert that string? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 22, 2017 at 20:19

Kristo Mägi · Accepted Answer · 2017-03-22 20:40:31Z

1

The answer already given before me is correct in format, just number of array order are wrong :)

This is how you get 2nd element value:

SELECT card_period[2][1] FROM client WHERE id = 1;
     card_period
---------------------
 2017-06-01 21:00:00
(1 row)

answered Mar 22, 2017 at 20:40

Kristo Mägi

1,70413 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AndrewMontana Over a year ago

oh, it's crazy xD. Thank you

Juan Carlos Oropeza Over a year ago

That is crazy, what sintaxis you use if want add two rows? like here ?

Kristo Mägi Over a year ago

The syntax is not IMHO crazy if you create 2-dimensional array, then this is common syntax in any programming language to read 1 dimension element and then 2nd dimension element :)

mike.k · Accepted Answer · 2017-03-22 20:19:58Z

0

SELECT column_name[1][2] FROM the_table

answered Mar 22, 2017 at 20:19

mike.k

3,4771 gold badge14 silver badges20 bronze badges

2 Comments

Juan Carlos Oropeza Over a year ago

Well then the problem is someplace else, because that is how you access one array. We need more info.

AndrewMontana Over a year ago

Updated my question

Collectives™ on Stack Overflow

Can't get element from array in second column PostgreSQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related